I've got an old 2005 server where 8/12 of the databases are in Standby/Read-Only. This server has 81 jobs, many of them with unique id's for names. One of these jobs is called Replicate, and it calls a .cmd file every 30 minutes. The .cmd calls a .pl that looks like some sort of home-brewed log shipping.
I'd like to bump the databases out of standby so I can back them up properly, but I'm worried it will break everything.
To make matters worse, while the server is a VM, there is (according to the VM admins) a problem with the drives where it sits, making it impossible to back up. They are worried that the server could die at any time, so we have to do something.
The databases are relatively small – I may be able to get in and out in the 30 minutes between the Replicate job. There's no test environment, though, so I can't find out easily.
We do have vendor support, but they don't seem to be very good. Am I better off just handing them the keys?
Question: How would you go about backing up this server without knocking it down in the process?
Best Answer
I think there are at least the following methods here for your consideration.
BCP (Bulk Copy) out each table and then script out the database and then you can recreate a new db using these data and scripts.
You can temporarily take the db offline (by detaching the db) and then copy the MDF file and/or LDF file. You can use the file copy to reattach it to another db.
If you want doing online, you can simply create an SSIS package to dump the data (of course, your destination db should be created the same way as the source db, which you can script it out)