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?

  • @Longhorn213: Did you mean between SQL Server 2005 only? What did you mean by "one server to another"? Are they of the same versions? Or different database RDBMS? Different editions?
    – dance2die
    May 2, 2009 at 23:49

8 Answers 8


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.

  • This method worked for me. That is why I am marking it as the accepted answer. The about the fileshare did not work in my case because I do have not have access to it. However if I did I am sure that method would work. May 1, 2009 at 19:23
  • @berberich Will that work between different versions of SQL Servers? Like between SQL Server versions between 2005 and 2008? The link you listed mentioned only SQL Server 2000 and 2005.
    – dance2die
    May 2, 2009 at 23:48
  • 1
    Copy Database Wizard uses SSIS so if there's not a version difference with regards to the features used (for instance, trying to go from 2008 -> 2005 when you've one of the newer data types), there shouldn't be an issue. When it comes to moving databases via copy the database files or restoring a backup, you can only go forward (2005 to 2008). May 2, 2009 at 23:51

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.


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 ;-)


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.


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


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


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.

  • Down-vote w/out comment? Love to know why.
    – WaldenL
    May 3, 2009 at 18:55

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.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .