Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
@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? –  Sung May 2 '09 at 23:49

8 Answers 8

up vote 7 down vote accepted

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.

share|improve this answer
    
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. –  David Basarab May 1 '09 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. –  Sung May 2 '09 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). –  K. Brian Kelley May 2 '09 at 23:51
    
@K. Brian: thank you for clearing my issues. –  Sung May 2 '09 at 23:54

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
    
Down-vote w/out comment? Love to know why. –  WaldenL May 3 '09 at 18:55

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.