Copy a SQL Server database with just the objects and no data

By:   |   Comments (9)   |   Related: More > Database Administration


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

  1. In the SSMS Object Explorer Window, right click on the "AdventureWorks2012" database and choose "Tasks" > "Extract Data-tier Application...", as shown below
  2. .

    Extract Data-tier Application... in SQL Server Management Studio

  3. 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:


  4. Extract Data-tier Application wizard

  5. Continue to click "Next" until you see the dacpac file generated successfully as shown below
  6. .

    Building the DAC package

  7. Click the "Next" button to close the window.

Now I have an AdventureWorks2012.dacpac file generated.

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:

  1. 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
  2. .

    Deploy Data-tier Application... in SQL Server Management Studio

  3. 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
  4. .

    Select the DAC package to deploy


  5. Click Next, and in the [Update Configuration] screen, input the required destination database name or leave it as (defaults to the source database name)
  6. .

    Configure the database deployment properties

  7. Click Next until the Wizard starts to deploy to the destination database as shown below
  8. .

    Deploying the DAC

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.

another benefit of using a DAC package file, we can un-package it by right-clicking on it and choose Unpack

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.

The final result is that the DAC package is extracted to a folder

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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, November 30, 2017 - 12:51:02 AM - MAHARAJAN Back To Top (73449)

Thanks for nice article.

But when the database has FileStream, this failed..

Any idea for that ?

Raj

 


Wednesday, November 29, 2017 - 11:54:55 PM - MAHARAJAN Back To Top (73448)

 Hi, Relly nice article.

 

But I got problem when the database have FILEGROUP / FileSteam.

 

 


Friday, October 27, 2017 - 11:37:40 PM - jeff_yao Back To Top (68902)

 @siva kumar, I am glad it worked for you and really appreciate your feedback.


Friday, October 27, 2017 - 10:34:37 AM - siva kumar Back To Top (68874)

 

 

Thank you Jeffrey,It's working and really great about automation of all database objects with powershell.


Friday, September 22, 2017 - 1:04:10 AM - jeff_yao Back To Top (66515)

@Mike Christie, I do not know whether your issue comes from SQL Server Express edition (I do not have express edition in my environment) or it is a problem of your SSMS (which i recommend you to download the latest version https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms and then try it.


Wednesday, September 20, 2017 - 7:45:29 PM - Mike Christie Back To Top (66470)

 

Hi, I am using the follwoing: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012 16:41:53   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) 

I did the first part (creating the DAC package) but when I try to restore, I cannot see any occurrence of "Deploy Data-tier Application..." on any menu  I right-click, not on Databases or on the new database I created for the DAC package to restore to.... could this be because I am using the Express version?  If so, I wonder why it allows me to create the DAC package but not to deploy it?

I'll google as well but I need to get this done ASAP so I might just have to do it the old fashioned way with a script :-(

 


Monday, February 6, 2017 - 11:25:53 AM - jeff_yao Back To Top (46054)

 @Thomas, good mentioning. DBCC clonedatabase is really a very nice feature which I like a lot.


Monday, February 6, 2017 - 6:07:07 AM - Thomas Franz Back To Top (46048)

Nice to know.

I prefer DBCC CLONEDATABASE ('source_db', 'target_db'), which is available since SQL 2014 SP2 / SQL 2016. If I remind correct it is a enterprise only feature before SQL 2016 SP 1.

It will create an empty clone of your database (including all files / filegroups using the file size from the model system database).

Remarks:

  • as far I know it can't copy In-Memory tables and CLR functions.
  • database is read-only after cloning it (but can be changed easily either in the database properties or with ALTER DATABASE)
  • It also copies the index statistics (even if there are no data in the new table), which means that you can use it to check execution plans (since the generated execution plans depends on the statistics and not the real data). When removing the READ_ONLY and inserting new data (or altering / creating indexes ...) it will create new statistics of course

Wednesday, February 1, 2017 - 10:33:17 AM - Jimbo99 Back To Top (45783)

One can always just script each object individually and then create the database elsewhere and then run each script to create tables, views, stored procedures, etc without any of the data in that newly created database. The shell of the database is recreated. Nice to have imdividual scripts to be able to share, modify and run in other database applications.















get free sql tips
agree to terms