Skip to main content

Create a local copy of a database fast

warning

This tutorial assumes you are on a Windows machine

Use Cases​

  • You want to get a database setup locally without having to manually build out all the schemas and manually seed the data.
  • You have a database that you want to copy as a local database.

Description​

This tutorial shows you how to use a .bak file to quickly deploy a SQL Server database to a local SQL Server

info

As an example, I will use the AdventureWorksLT2022 database from Microsoft

Prerequisites​

  • Have PowerShell installed.
info

You can probably use differnt CLIs, but I haven't tested that.

  • Have docker installed on your machine
  • Have SSMS installed
  • Have SQL Server running as a Docker container. See this guide for how to do that.

Instructions​

  1. Obtain the .bak file that has all the database info in it.

  2. Copy the .bak file to the docker container by running docker cp <your_file> <your_sql_server_container_name>:<target_file_name> in PowerShell.

info

Replace <your_file> with the file path to the .bak file

Replace <your_sql_server_container_name> with the name of the docker container

Replace <target_file_name> with the name you want to give the file on the docker container filesystem.

  1. Go into SSMS and connect to your SQL Server

  2. Right click Databases and click Restore Database.... Click Device and then the three dots .... Click Add. Scroll to the top and click the root folder /. Click on the .bak file that you just added to the container's file system. Click OK -> OK. Click Verify Backup Media. Click OK.

Now you should be able to see a new database added! Done.

Example​

info

For the example used in this tutorial, we'll assume we're using a SQL Server Docker container with the name my_sql_server_container and the password as password.

  1. Download the AdventureWorks bak file from here
info

Assume for the example's sake that this downloads a file called AdventureWorksLT2022.bak to the .\ directory.

  1. Run docker cp .\AdventureWorksLT2022.bak my_sql_server_container:mybakfile.bak

  2. Connect to SQL Server with SSMS with server name as .,1433, SQL Server Authentication, login as sa, and password as password.

  3. Follow the instructions for step 4.

Last updated on by Zach Sedlack