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?
Add a comment
|
3 Answers
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.
answered Jul 18, 2011 at 15:26
-
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) Jul 18, 2011 at 15:28
-
You can manually backup the databases to disk, copy the files to the other server, and restore them. Jul 18, 2011 at 15:33
-
-
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.
