We currently have a single SQL server (2005, on a Windows server 2003 machine) with 3 DBs on it that I would like to just straight copy so that I can run some test on them for an upgrade. I don't think I can do this with the copy DB wizard as I am running my test servers on VMs and do not have them properly networked. I also cannot take this DB down as it needs to be up 24/7 for the company. So what I'm asking is, is there any way to just straight copy the DBs and then import them directly into a different installation of SQL server (2005 or 2008, running tests on both) without taking the DB down or corrupting anything by accident. and having them work as if nothing had happened on a different machine?


You can backup the databases and restore them to another server (or the same server with different database names and paths) without impacting the production use of the databases.

  • That, unfortunately, is not an option either as all the back ups are on tapes (another thing I'm looking to improve for a similar reason) – Tablemaker Jul 18 '11 at 15:28
  • You can manually backup the databases to disk, copy the files to the other server, and restore them. – joeqwerty Jul 18 '11 at 15:33
  • derp moment on my part -.- – Tablemaker Jul 18 '11 at 15:34
  • LOL......... :) – joeqwerty Jul 18 '11 at 15:37

Source server: BACKUP DATABASE dbname to disk = 'sourcepathname\dbname.bak'

Target server: RESTORE DATABASE dbname from disk = '\sourceservername\sourcepathname\dbname.bak' WITH MOVE 'logicalname_data' to 'mdf location', MOVE 'logicalname_log' to 'ldf location'

Assume SQL 2005 installed on your target server and your source server logins are there, etc.


Pretty easy place to start is a backup/restore. Use the copy-only option to ensure you're not going to interfere with any existing backup schedules when you take your backups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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