Easiest way to copy an entire SQL server Database from a server to local SQL Express

If the database is not too big, you could use the Database Publishing Wizard.

This is a free tool from Microsoft which creates a complete SQL script of a database for you (not only the tables and stuff, but all data as well).

You can install the tool on your machine, connect to a remote server and let the tool create the script directly on your machine.

You can download the Database Publishing Wizard here.


Apparently the link above doesn't work anymore in 2019.
That's probably because in newer versions of SQL Server Management Studio, the functionality of the Database Publishing Wizard is included out-of-the-box, so there's no need to install it separately.

It's now called the Generate and Publish Scripts Wizard, but it does exactly the same.


You can right click the database -> Tasks -> Generate scripts. Here you can select one, multiple, or all objects. Then in the 'Set Scripting Options' step of the wizard, click Advanced. In here set the property 'Types of Data to script; to Schema and Data.

Having done these steps, make sure you publish to a file. Because only file can handle large amounts of data.

Now you should have all your objects, tables, and data scripted. Now start running the scripts and viola!


Back up the database on the server and then restore it locally in SQL Express.

EDIT: If this is part of your work, surely you can get someone in networks to get you a backup..?

Tags:

Sql Server