14

I need to copy an entire database from a SQL Server 2005 on my server over to my local SQL Express in order to run my application for a presentation. What is the fastest/easiest way to get this done?

EDIT: I have very limited access to my server so I don't think I can access the backup file that I could create so that is out.

Collin Estes
  • 5,577
  • 7
  • 51
  • 71

7 Answers7

13

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.

Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • NOTE: I have had very bas experience with this....as it has "issues" at times with data relationships. – Mitchel Sellers Oct 28 '08 at 15:05
  • Works great for medium complexity/sized databases! Just wish there was an easier way for the huge, complex ones. – Druid Sep 03 '09 at 12:16
  • @Druid You should check out the BCP Utility. It is very powerful; I've used it to move large amounts of data with complex relationships. – harsimranb Apr 10 '14 at 19:19
  • This is a really old answer, I know, but the link doesn't exist anymore. – bgmCoder Dec 28 '19 at 21:52
  • @bgmCoder: it's probably because the same functionality is now available in vanilla SSMS, see my edited answer. – Christian Specht Dec 30 '19 at 20:39
  • Thanks for the update. For myself, I exported the db and then "restored" it onto the other server. Worked like a charm, too. – bgmCoder Dec 31 '19 at 03:53
9

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!

harsimranb
  • 2,231
  • 1
  • 35
  • 55
5

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

Galwegian
  • 41,475
  • 16
  • 112
  • 158
3

If you can login to both servers (the Express and the 05 Server) using SQL Server Management Studio then you can do a DB Restore from one database to the other. No need for backup files at all.

Stephen Wrighton
  • 36,783
  • 6
  • 67
  • 86
  • how do you do a restore a database from one server to the other without a backup inbetween? (SQL Server 2008). – Petrus Theron Apr 08 '10 at 10:14
  • In instances where neither server is SQL Express, you use sp_detach_db and sp_attach_db or the Import/Export Data Wizard -- http://support.microsoft.com/kb/314546 – Stephen Wrighton Apr 08 '10 at 13:07
2

You can use SSIS's database copy wizard, but it's not quick at all. Is there a DBA there that you can ask for the backup file? That will probably turn out to be the fastest way.

GilaMonster
  • 1,728
  • 12
  • 13
2

Depending which versions of SQL Server you are using, you might get some mileage out of the SQL Server Database Publishing Wizard from Microsoft. I've had mixed results with this tool in complex environments, but for most simple database scenarios it is a great tool.

An example of a scenario where I ran into difficulties was a DB with multiple distinct schemas, each with their own owner and extreme separation between the tables (don't ask...). As I said, though, most other scenarios have been fine.

ZombieSheep
  • 29,603
  • 12
  • 67
  • 114
2

Save your database as a sql script

EMS SQL Manager (for example) allows you to backup your database as a script in a .sql file. It is then possible to run this file against any other SQL server. Just update the first line of the script (CREATE DATABASE ....).

You can even fully parameter the script in order to include data from complete or filtered tables.

Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72