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

How to create a large database in seconds - SQL Server 2016 - Perform Volume Maintenance Task

In most cases, we do not set the database file large, initial size is always less than 1GB, unless a decision is taken to make it suitable for next few months. If the database has to be created with a larger file, then of course, we need to set the file size with database creation, and it takes long time for creation. Why it takes long time? We dont have data, and it is just an allocation from the space in the disk. But it takes time if the size is in GBs.

It takes long time for initializing the space required, by filling the file with zeros. This makes sure that previously deleted files cannot be accessed and no security violation. This Zeroing Process applies to;

• Create a database
• Add files to an existing database
• Increase the size of the file
• Restore a database

If required, this process can be stopped for SQL Server and reduce the time it takes. SQL Server 2014 and before, it has to be done by adding the SQL Server service account to a policy group called Perform Volume Maintenance Task. However SQL Server 2016 allows us to set this at installation itself.

If you select this checkbox during the installation, it will not take long time for peforming above mentioned operations. Here is a comparison I just did.

This code is based on SQL Server 2014 with default setting. Its service account has not been added to policy task and as you see, it has taken more than 2 minutes for creating a database with 10GB size file.

This shows the same code execution with SQL Server 2016. Note that I have enabled the option during the installation. As you see, it has taken only 14 seconds.

Note that this does not applicable for log files. You should consider this option if you frequently perform these operations against your SQL Server.