Quickest way of creating a copy of existing database without data - SQL Server Brain Basher of the Week #059

Sometime we need to take a copy of the database without data and have it as a new database. This either requires to generate the entire script and run the script with a new database name or take a backup of the database, restore it and delete data. Both require a reasonable time base on various conditions.

Here is the Brain Basher of the week on it.

What is the quickest way of creating a copy of existing SQL Server 2016 database without data?

Both techniques mentioned above work fine but add complexities. If you have installed SQL Server 2016 Service Pack 1, this can be esaily achieved using DBCC CLONEDATABASE command.

Here is the way of creating a copy with a new name.

DBCC CLONEDATABASE(AdventureWorks2014, CloneAdventureWorks2014)
WITH NO_STATISTICS,NO_QUERYSTORE ;

Note that database is created as a read-only database;

However, changing the Database read-only property to false makes the database as a read-write database.

For more info on this command, read: DBCC CLONEDATABASE is now working with SQL Server 2016.

** Note that this does not take all objects exist in the database when copying the schema. Therefore, if your database contains many different types of objects (such as Naively Compiled Stored Procedures), this is not the best way of getting a copy of your database.

** Although we can get the schema from this, this functionlity is primarily given for getting a copy of datatbase for troubleshooting, NOT FOR CREATING A DATABASE SCRIPT OR COPY OF DATABASE.

For more info, read: https://support.microsoft.com/en-us/kb/3177838