Many projects use central database for development. All developers working on a project connect to a single database server. It really is efficient to get started on the project and especially when the modifications being done by a developer are only code and not in database.

But there are certain situations, when we want to have a local copy of the database, to be present on our own machine. This might be because we want to test some functional scenarios with some specific data, or it might be because we want to troubleshoot some issues or it may also be required because a developer is making schema modifications which needs to be tested before merging the modifications to GIT repository.

The bottom line – we may sometimes want to have local copy of database on our machine.

This article explains various ways which can be used for this purpose – without using any third party tools. The aim of every approach is to restore both schema and data on the local machine.

Please note that this article’s focus is on SQL Server database. The scripts given below are not production-ready. They are just for demonstrating different approaches.

Approach 1: Backup and Restore

This is the approach that we have been using traditionally. The idea is to take a full (or differential) backup from the source server. This backup file would contain all objects and all the data in the database. This file then can be used to restore the database on the destination server (in our case local machine).

Using SSMS

The SQL Server Management Studio (SSMS) is a client that can be used to connect to a SQL server instance. This client can be used to create backup or restore backup from file.

If you right click on a database, the context menu would show Tasks option. Inside this Task menu option, you can find “Back up…” and “Restore” options. These options open wizard which will guide you step by step to create a backup file or restore database from backup file.

Please refer the documentation for step by step guide.

SQL Script

T-SQL provides “BACKUP DATABASE” and “RESTORE DATABASE” commands. The code snippet given below demonstrates how those can be used. Of course, you will need to change the database name and the backup file name in the below code snippet.

PowerShell Script

The PowerShell also has a SqlServer module, which has Backup-SqlDatabase and Restore-SqlDatabase commands. Below is a code snippet demonstrating its usage.

You can also refer to this article for more explanation.

There are two more alternatives.

• The SQL script can be invoked from PowerShell to backup and restore the databases
• Another option is to use SMO to write PowerShell script to backup and restore databases

C# using SMO

As stated earlier, Sql Management Objects (SMO) can be used to backup and restore the databases. The SMO is the collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

You can refer to the documentation for a code example.

Sometimes due to setup of SQL server and permissions assigned to the service, it might be sometimes difficult to save backup from remote SQL server instance to your local machine. You may need to use shared drive for accessing the backup file.

There is some interesting discussion about this issue on StackOverflow, another on DBA StackExchange community.

Approach 2: Generate Scripts

If generating backup file is not an easy option for you because of file system access rights (or no network share to the server), you may want to use this option.

In SQL Server Management Studio (SSMS), you can right click on the database and select Tasks -> Generate Scripts. This would open a new wizard, it would ask you which objects (table, views, stored procedures, functions, triggers, etc.) you want to generate script for. If you want to copy the database, just keep the default option selected which is to script all objects.

Also, on the screen when it asks for where to store the generated script file, you can either provide a path where a “.sql” file would be saved OR you can also select option to o pen the script in new query editor window in SSMS. On the same screen, it shows an “Advanced” button, click it to open a new popup. This new popup would show a lot of options. Important option is “Types of data to script” – it should be set to Schema and Data to ensure that data insert scripts are generated.

Then click Ok to close this advanced options popup and click on Next -> Next to start the script generation. This new SQL file then can be used to create the database with all the data as in original source database.

There is also a nice article explaining how C# SMO can be used to generate the scripts. I have not tried it (yet) but if you want you can refer that article to automate the script generation.

Possible Issues

It is hard to take differential data / schema using this approach. At least I am not aware of how it can be done.

Sometimes when you try to run generated scripts, it may show errors that some scripts could not run. It may be generally for applying permissions to the logins which were available on source SQL server, but are not available on target SQL server, causing GRANT scripts to fail. Although this is not a limitation, it may take some of your time to ensure that all the “intended ” data is inserted correctly and that if errors from script execution can be safely ignored.

Also, you may have to (sometimes) make sure to apply appropriate advanced options like scripting collation options, or scripting based on target server edition (Express or professional or developer), etc.

Approach 3: The “.bacpac” File

I personally prefer this approach now a days.

Using SSMS

In SSMS, you can right click on the source database and select Tasks -> Export Data-tier Application to start the wizard. This wizard will ask you where do you want to save the bacpac file. You can also select tables to include in bacpac. You can connect to remote SQL Server and the exported file can be saved on your local machine.

Once you have the bacpac file, you can connect to target SQL server instance. Then right click on Databases folder, and select Import Data-tier Application option. This new wizard will ask to select the bacpac file. It will also ask you name of new database and file paths for data file / log file. On completion, source SQL instance will show the new database with all the intended data in it.

C# Code

Create a .NET project and add a reference to Microsoft.SqlServer.DacFx NuGet package. Once this is done, you can use the below code. It is pretty much self explanatory. It is calling APIs provided by NuGet package to export / import bacpac file.

Refer documentation for more details about this nuget package.

Alternatively, you can also write PowerShell script for exporting / importing “.bacpac”. You can also take a look at SqlPackage which can be used as a command line tool to generate bacpac and dacpac files.

Issues with this approach

If you try to copy a SQL Server database using this approach, the bacpac file creation may fail due to some features which are not supported on Azure SQL.

One of the example – if there are windows authentication users, bacpac export may fail. Apart from this issue, I did not face any other issues personally.

Exporting Azure SQL Database

Azure SQL database can be restored on local machine using either “.dacpac” or “.bacpac” file. Below are high level steps for exporting / importing the Azure SQL database:

• Export “bacpac” or “dacpac” file. The export operation can store the file on Azure Storage.
• Use the exported file to import the database either on other Azure environment or on local machine.

Please refer this article for detailed steps.

Wrapping Up

This article has covered most of the important ways to copy the SQL Server databases. It did not go in details to discuss about different input parameters, error handling settings, etc. I would suggest to pick the approach which is suitable for your project.

In my opinion, a programmatic approach may be useful to create your own scripts to increase the developer productivity. If you want to choose programmatic approach using PowerShell or C#, then go to respective documentation to further enhance the scripts provided here.

I hope you found this information helpful. Let me know your thoughts.