This post was most recently updated on September 7th, 2021.
3 min read.Every now and then, you will run into a situation where you need to overwrite a database’s contents with those of another database. Maybe you’ll need to update a staging environment with a partial data import from production, or perhaps you have a new developer coming in who needs some data – I don’t know. But this should be a run-of-the-mill action. Something you can do in a minute.
The bottom line is – this shouldn’t be complicated. And it should be documented really well. And just easily accessible in all manners. And it isn’t
Background
Backups are the obvious answer. I’m not disputing that. But getting to the point where you actually have a database backup file (such as a BACPAC) that you can restore from can actually take quite a few steps when done properly.
I mean, the data import/export tool is the clumsiest piece of technology I’ve used in years, generating and downloading the backup file(s) will take a while and involve a lot of clicking around an ever-changing UI, and once you finally get around to restoring a backup to a database, it’ll create a new database for you, so now you need to point your connection strings to a database with a new name…
That’s just a major hassle for something you can do in a minute in SQL Server Management Studio.
So here’s just a quick way to export the data into a SQL script, that you can then apply to a new database of your choosing :)
Solution
Below, I’ll show you how to copy data (and just data) from a SQL database to another one. Note, that I’m ignoring things like Views or Stored Procedures, which you might want – but you’re the best expert with your database!
I’m using Microsoft’s fantastic sample database, AdventureWorks.
Time needed: 20 minutes
How to import data from one MS SQL database to another?
- Fire up SSMS and sign into the source DB
Suppose this is obvious, but you need to log in to your server with credentials that can access the source database.
And just in case you’re trying to log in to your local Microsoft SQL Server, but are running into issues – see this article for some guidance:
How to access local MSSQL server using SQL Server Management Studio? - Start the export
This happens by clicking on the source database with the right mouse button and selecting “Generate Scripts…”
- Select your tables
So this step might require some domain knowledge – you’ll probably not want to copy environment-specific data, or if you’re replicating data from production to staging, please PLEASE ignore webhooks or other stuff that might point to production.
Now, hit “Next”. - Configure the export
There’s a few things of note in the scripting options – my go-to for simply exporting a lot of data would be saving it as a script file, but in case you have a really small amount of data, you could also simply store it simply on your clipboard.
Make note of the file location!
Then, select “Advanced”.
- Set the data to be exported
By default, only the schema is exported. But if you already have a database with proper schema and you’re just missing the data (like is the case after running code-first database migrations with EF Core, for example), you’ll need to change “Types of data to script” to “Data only“.
Like shown below:
- Hit “OK” -> Next -> and Next
This should start the export, after which you can finish the export by clicking “Finish”.
- Copy the .sql file contents
Open up the SQL file you exported earlier and copy its contents.
Note: If you need to modify the data in any way, now’s the time. - Connect to the target database
If you’re careful, close the connection to the source SQL Server – just in case.
- Open a new Query window to the target database
- Paste the SQL to the “New Query” window and Execute
- Make note of any errors
If you encounter any errors, evaluate and make note of them. Make the judgment whether they are something that you can fix by hand, or if you need to redo the import completely.
And you should be good!
Was it useful? Did it fail miserably? Let me know!
References
- Azure SQL Database: Import BACPAC into a new database.
- SQL Server: Import a BACPAC File to Create a New User Database.
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.
This is his personal professional (e.g. professional, but definitely personal) blog.
- CSOM suddenly throwing exceptions when trying to access list contents in SharePoint? A weird fix. - April 2, 2024
- “Predefined type ‘System.Object’ is not defined or imported” and other System namespace stuff missing in your solution? - March 26, 2024
- How to import GraphQL schema to Postman in Windows? - March 19, 2024
