SQL Server 2012 copy database without data

Question

We have a SQL Server 2012 database with test data in it that we used to develop a website. We will shortly need to empty the database and import the real data, but we have changed the database design many times during the web development, so we cannot use the empty copy we created at the start!

Is there a way to copy the database, with no data but with everything else (including views, SPs functions, security objects etc), and resetting all the indexes to the design seed value? In other words, we would like to keep the database exactly as it is but delete all the data, reset all the table indexes to their original design seed values and then import the data.

Any help gratefully received

Answer 1

1. Right click your database in your server, and select Tasks > Generate Scripts.
2. At Introduction step, click Next.
3. At Choose Objects, you can either select either the entire database (including functions, and etc) or specify what objects you want to generate. In your case, you should select Script entire databases and all database objects then hit Next.
4. Choose your file name and destination then click next
5. Review and then click next
6. Done!

You will see a .sql file generated in your specified folder. You can open that file in other servers and hit execute, it will then generate a exact same database.

Answer 2

You are missing the most important answer here: Introduced with MSSQL 2014 SP2 (2016-Jul-11), but later added to MSSQL 2012 with SP4 (2017-Oct-05) - the fastest and easiest way of preparing empty clone of your database is achievable via a DBCC CLONEDATABASE. Then according to the books online:

• Creates a new destination database that uses the same file layout as the source but with default file sizes from the model database.
• Creates an internal snapshot of the source database.
• Copies the system metadata from the source to the destination database.
• Copies all schema for all objects from the source to the destination database.
• Copies statistics for all indexes from the source to the destination database.

SYNTAX:

DBCC CLONEDATABASE   
(  
     [Source_DB_Name]
    ,[Target_DB_Name]
)
WITH NO_STATISTICS, NO_QUERYSTORE, VERIFY_CLONEDB, BACKUP_CLONEDB

REMARKS:

• The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.)
  isn't allowed.

• The source database must be online or readable.

• A database that uses the same name as the clone database must not already exist.

• The command isn't in a user transaction.

• Requires SA server role

Here is a nice MS article about how to use it.

Second option:

Is to use a PowerShell module called dbatools.io which can be sourced directly form the project website or from the official PowerShell Gallery.

Once you have it, you can use this command:

Invoke-DbaDbClone 
    [-SqlInstance] <DbaInstanceParameter[]>] 
    [-SqlCredential] <PSCredential>] 
    [-Database] <String[]>] 
    [-InputObject] <Database[]>] 
    [-CloneDatabase] <String[]>] 
    [-ExcludeStatistics] 
    [-ExcludeQueryStore] 
    [-UpdateStatistics] 
    [-EnableException] 
    [-WhatIf] 
    [-Confirm] [<CommonParameters>]

As the dbatools.io is an open source project you can see what exactly happens in the background thanks to their publicly available GitHub repo.

And what you will find there is that they use DBCC CLONEDATABASE() to perform the core of this operation (but giving you ability to do much more).

Answer 3

You could generate the script for the database objects by right clicking it in Management Studio, then going to Tasks, Generate Scripts, choosing the objects you want to script, such as Tables, Views, etc. and there you have it.

Answer 4

There is one more way, which takes a little longer, but has its benefits. When using the "Generate Scripts" option as others have suggested, we get the entire database and tables script in a ".sql" text file.

A different approach is to restore the existing database into a new db. You can then delete all data in the tables using a custom script to loop through the tables. Now you have a copy of the database without any data. Take a backup of this to get a familiar ".bak" file.

Answer 5

Yes! there are a couple of ways to do it. You can try to copy the database, drops FKs, truncate tables and recreate the FKs (there are SPs to automate the drop/recreate FK truncate process if you can search for it.)

You can simply right click in MS and try to generate the entire DB creation script (check if it did the work to 100%)

and the most reliable way: using VS create a new empty DB and using the schema comparation tool to generate the diff script.