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?
SnapOverflow
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.
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.
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.