Problem
I sometimes get requests to copy a database from one server to another without any data. I just need an empty database with just the schema information and no data, how can I do it?
Solution
There are a few potential solutions to this problem, for example any of the following methods will work:
- Script out the source database and then run the script against an empty target database to create all database objects that are in the source database
- Backup the source database and restore to the destination database and then delete all table data.
- Backup the source database, restore it to an intermediate database, truncate all tables in this intermediate database, and backup this intermediate database, then restore it to the final destination database.
All these can work very well, but there is another simpler way that is not well-known to DBAs, that is to use a DAC package which is applicable to SQL Server 2008 R2 and later versions.
To use a DAC package, we first need to download and install the Data-tier Application Framework, aka DacFx, from here.
Backup a SQL Server Database without Data
Here are the detailed steps to do this using SQL Server Management Studio (SSMS).
- In the SSMS Object Explorer Window, right click on the “AdventureWorks2012” database and choose “Tasks” > “Extract Data-tier Application…”, as shown below
- The [Extract Data-tier Application] wizard will start. Click next in the first [Introduction] screen, and in the next [Set Properties] screen, input the DAC package file location or leave it as it is, as shown below:
- Continue to click “Next” until you see the dacpac file generated successfully as shown below
- Click the “Next” button to close the window.
.
.
Now I have an AdventureWorks2012.dacpac file generated.
With the DAC package file ready, we can copy it to anywhere or put it in a shared folder and then we can restore it to a new destination database.
Restore a SQL Server Database from a DAC package
The DAC package can be restored to a target SQL Server instance whose version is equal to or higher than that of the source SQL Server instance.
Note: I actually successfully restored a SQL Server 2014 DAC package (from AdventureWorks2014) to SQL Server 2012 instance without any errors.
Here are the restore steps:
- In SSMS Object Explorer Window, connect to the target SQL Server instance, in the [Object Explorer] window, right click [Databases] , and choose “Deploy Data-tier Application…”, as shown below
- The [Deploy Data-tier Application] wizard will start, Click next in the first [Introduction] screen, and in the [Select Package] screen, click the Browse button to find the DAC package file location
- Click Next, and in the [Update Configuration] screen, input the required destination database name or leave it as (defaults to the source database name)
- Click Next until the Wizard starts to deploy to the destination database as shown below
.
.
.
.
Backup and Restore Automation with PowerShell
Now we know how to do this backup and restore via SSMS, but if we want to do this for multiple databases on multiple SQL instances, we need to rely on an automated script. I will demonstrate this via PowerShell instead of CMD.exe, because Microsoft is replacing CMD.exe with PowerShell as mentioned here.
The key here is to use SQLPackage.exe, a command line utility included in DacFx.
Let’s start a PowerShell 3.0+ ISE window and run the following script, you can select one line and press F8 to run the selected line.
#Add sqlpackage.exe location folder to environment, this is done once only
[string]$path='C:\Program Files\Microsoft SQL Server\130\DAC\bin' ## the folder where sqlpackage.exe resides
if (-not ($env:path).Contains('C:\Program Files\Microsoft SQL Server\130\DAC\bin'))
{ $env:path = $env:Path + ';C:\Program Files\Microsoft SQL Server\130\DAC\bin'; }
# do a backup, i.e. generate the DAC package file, assuming the source sql server instance is my local server
sqlpackage /a:extract /of:true /scs:"server=localhost;database=AdventureWorks2012;trusted_connection=true" /tf:"C:\SQL Server Management Studio\DAC Packages\AdventureWorks2012.dacpac";
<#
# if you have multiple databases to backup, you can generate their dacpac files easily using PowerShell as follows:
# you may need to change server=localhost to your desired sql instance name (or put this instance name in a variable)
[string[]]$dbs = "db1", "db2";
$dbs | % {sqlpackage /a:extract /of:true /scs:"server=localhost;database=$_;trusted_connection=true" /tf:"C:\SQL Server Management Studio\DAC Packages\$_.dacpac";}
#>
#do a restore to my local named instance
sqlpackage /a:publish /sf:"C:\SQL Server Management Studio\DAC Packages\AdventureWorks2012.dacpac" /tcs:"server=localhost\sql2014;database=AdventureWorks2012;trusted_connection=true"
<#
# if you have multiple databases to restore, you can publish these dacpac files easily using PowerShell as follows:
# you may need to change server=localhost to your desired sql instance name (or put this instance name in a variable)
[string[]]$dbs = "db1", "db2";
$dbs | % {sqlpackage /a:publish /sf:"C:\SQL Server Management Studio\DAC Packages\$_.dacpac" /tcs:"server=localhost\sql2014;database=$_;trusted_connection=true";}
#>
There is another benefit of using a DAC package file, we can un-package it by right-clicking on it and choose Unpack as shown below.
The final result is that the DAC package is extracted to a folder, as shown below. The most interesting one is the model.sql, which contains all the database objects, such as tables, views, functions, stored procedures, etc.
You may want to run the model.sql directly to create all the target database objects, but this may fail because this model.sql file does not guarantee to generate the object creation script in the correct order. For example if you have two tables, one table (child table) refers to another table (parent table), in model.sql you may see that the child table is created first before the parent table, and thus the model.sql execution fails.
Summary
Backing up a database without data is not difficult and can be implemented in multiple ways, however some methods may be easier or more efficient than others.
To me, the DAC package file is probably the best way. I like its brevity and quickness, especially from an automation perspective (for multiple databases). If you are a developer, there is an additional benefit that this DAC package file can be immediately consumed by a database project in Visual Studio 2012+.
According to MSDN, DAC supports many database objects as listed here, this implies some objects are not supported, but I find the list is not true. For example, the SEQUENCE object is not listed here as supported, but I can find it created in the model.sql after I unpack the dacpac file and I also see it after I restore the dacpac file to a new database. Same for some other objects like CERTIFICATE.
Since Microsoft does not say DAC will support all database objects, you need to be cautious and test your solution to see whether there are any objects that you need that are not included in the DAC package file.
Next Steps
SQLPackage.Exe can do some other interesting work, such as backing up a database with data, creating an incremental update script that updates the schema of a target to match the schema of a source.
You may refer the following articles to better understand the DAC framework:
