T-SQL, yes please!

How to (easily) move data between SQL databases?

3 min read.

Every now and then, you 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 partial data import from production, or perhaps you have a new developer coming in and they need 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.

Background

Table of Contents

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 even stuff 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?

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

  2. Start the export

    This happens by clicking on the source database with the right mouse button and selecting “Generate Scripts…”
    This image has an empty alt attribute; its file name is image-38.png

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

    This image has an empty alt attribute; its file name is image-39.png

    Now, hit “Next”.

  4. 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”.
    This image has an empty alt attribute; its file name is image-40.png

  5. Set the data to be exported

    By default, only 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:
    This image has an empty alt attribute; its file name is image-37.png

  6. Hit “OK” -> Next -> and Next

    This should start the export, after which you can finish the export by clicking “Finish”.

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

  8. Connect to the target database

    If you’re careful, close the connection to the source SQL Server – just in case.

  9. Open a new Query window to the target database

    This image has an empty alt attribute; its file name is image-41.png

  10. Paste the SQL to the “New Query” window and Execute

  11. Make note of any errors

    If you encounter any errors, evaluate and make note of them. Make the judgement whether they’re something 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

mm
0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments