How do I copy a SQL Server 2005 Database?

Question

In my environment I need to copy a database from one server to another. Usually a QA machine to my local box.

I do not have access to QA machine. I can connect to the database, but not RDP into the box.

What is the best way to copy the database from the QA server to my machine?

Answer 1

I would try the Copy Database Wizard command available through SQL Management Studio. This can be done without RDP access.

Microsoft also has an MSDN page on copying data between servers that may be helpful.

Answer 2

If you have access to a fileshare or administrative fileshare (i.e. c$) on the SQL Server box you can run the backup using SQL Server Management Studio to the server drive and then copy it over to your local machine.

Sql Server Management Studio (Example is from SSMS 2008, should be similar to 2005):

• Login to the database server
• Right click on the database you wish to backup -> Tasks -> Backup
• In the 'Destination' group, select 'Backup to: Disc'
• Click 'Add...'
• Specify where you want the backup and what the filename will be (.bak file) under the 'File name:'
• Click 'Ok'
• Click 'Ok'

When that completes, copy the file to your local machine.

There is also an Import/Export wizard that comes with SSMS 2008 that may be able to do a direct copy from one database to another (i.e. QA to local), but I haven't used it much because I have always had access to the DB servers.

Answer 3

The easiest way, and maybe safest in this situation, is to backup the database and restore the .bak file to your machine.

This is what I usually do specially when I am writing Stored Procs for, say, SSRS report or simply testing or plainly dabbling on something. And, of course, I always make sure that I am ONLY connected to my dev instance. I don't want to mess around with the live db. So, I usually do a backup to a particular db at midnight, SFTP it to my machine, and do a quick restore. When everything is tested and everything is working fine in the dev, then that's the time I run the script/s on the live instance and db ;-)

Answer 4

If you can connect to the source database, but not RDP to the machine it is running on, you could use the Microsoft Database Publishing Wizard.
This is a free tool from MS which creates an SQL script of the complete database for you.
You can then run the script on your machine.

Answer 5

I've used powershell before to create the backup and automatically copy it over to my local drive.

You can probably google powershell sql backups but here are some links to get you started:

Database Journal

Powershell Database Scripts

Answer 6

Can you accomplish this by just copying the files in your installation's /Data directory?

Answer 7

I second the "backup" idea, however I would not take a new backup for this. Get last night's backup. Not only will you save the extra backup time, but it's a test of last night's backup. If this is a regular requirement it's reasonable to ask the DBAs to leave a copy of the backup somewhere that you can get at it.

Answer 8

If your database is relatively small you can generate create scripts from management studio to execute on the target SQL server. These can get quite large when you are scripting data, but it does work.

Right click on the database and select Tasks and Generate scripts...

Select your database and click the box toward the bottom labeled 'Script all objects in the selected database'

This is the most important part, read through the options carefully, Make sure you include data (false by default), triggers etc. If you have a complicated database, you may find the script will error when executed. For most common database schemas, this isn't the case.

I use this method often with web hosts which don't open SQL ports to the outside world. It only text, and easy to deal with

Happy copying.

Brett