How do I copy a SQL Server 2005 Database?
Solution 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.
Solution 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.
Solution 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 ;-)
Solution 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.