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).