Import data from SQL database on server to SQL database on localhost

Question

We have a database with a very large amount of data in it (around 400k records per table). I'd like to be able to debug a stored procedure on this database, however I do not have the permissions to do that on the server it's on.

As such, I need to create a replica of the database on localhost and debug from there.

However.. due to the large size the script that gets created is too large for SQL Server Management Studio to open.

Is there a way to directly import the data from one database to another if one is located on localhost and the other is not? The security issues shouldn't be a problem for importing/exporting data, I'm told.

Answer 1

Just create a binary backup of the source database (e.g. using SQL Server Management Studio, right click on the database, then select Tasks -> Back Up), then import that .bak file into your local installation - again using Management Studio, right click on the "Databases" node and choose Restore Database.

The only thing that might prevent you from doing that is if you are running SQL Server Express locally and the total size of the source database exceeds the size limit of the Express edition.

Answer 2

If you can connect to the server database from your computer, you can use SQL Server Management Studio, right click the Database, menu option Tasks and then Copy Database. You'll see an easy wizard asking for source and destination databases.

You can also create a backup through SQL Server Management Studio and restore on your computer.