How to Copy Database Schemas without Data in SQL Server

Learn how to backup and restore database objects for a SQL Server databases, using DAC Package.

When operating on a SQL Server database, there can be situations where you wish to copy a database in a given server to another server, with schema information only, and not the data. This can be done in multiple ways, a few of the basic ones include:

1. Separate the script from the source database, and run it for a fresh, empty database, this can also be the target to copy the objects in source database.
2. Backup source database retrieve backup to target database, and then delete the data in the table.
3. Backup source database retrieve data in intermediate database, and make sure you truncate tables before you backup this database, finally retrieve this backup to your target database.

These solutions are reliable but a little complicated, a more user friendly, but probably lengthy option that not many DBAs opt for is using a DAC Package for the same. The only restriction that comes with this is that you should have access to the 2008 R2edition or later editions of SQL Server

Note: To be able to make use of a DAC Package for backing up only the objects of a SQL Server database, you should have access to Data tier Application Framework, which is also referred to as DacFx.

Backing Up SQL Server Database Objects Only

Here this will be explained using SSMS (SQL Server Management Studio)

1. Right click the ‘AdventureWorks2012’ database option in the ‘SSMS ‘Object Explorer Window’. Now click on ‘Tasks’, and choose the ‘Extract Data tier Application’ option.
2. This will start the wizard for extraction the Data tier Application. Now choose the ‘next’ option from the first screen, followed by ‘next’ in the ‘Set Properties’ screen, enter location for DAC Package file.
3. Keep on clicking the ‘next’ option till a DAC Package file is not successfully generated.
4. When the dacpac file is generated, you can close the window by clicking on ‘next’. Your AdventureWorks2012.dacpac file is now ready to be copied anywhere.

You can copy this file, store it in shared folder, or even restore to new target database. To backup the same file to target database, continue reading.

Given Below Are The Steps For Restoring SQL Server Database From DAC Package.

Note: One of the key things to keep in mind while restoring from DAC backup is to ensure that you are restoring to the version of SQL Server which is equal or advanced to the one we have taken backup from.

1. From the ‘SSMS’ Object Explorer Window, establish connection to the destination database, now right click on databases, and select ‘Deploy Data tier Application’.
2. This will start the wizard, from here click ‘next’, and then ‘Browse’ to get the location of DAC Package file.
3. In Update Configuration screen, click on ‘next’, enter the name of the target database, you can also let the default source database name remain.
4. Keep on clicking next till the deployment to the destination database does not begin by the wizard.

It is important to note that during a data copy process, incidents of SQL corruption can occur. In case you encounter such a scenario, it is best to call in a specialized SQL Server recovery tool.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Access and sql recovery software products. For more information visit https://www.datanumen.com/