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?

  • Just do a native sql server full backup - backup database dbname to disk = 'your disk\dbname.bak' with init .. since you are on 2005, backup compression (was an EE feature - need to check .. do not recall). – Kin Shah Jan 29 '18 at 15:32
  • can't back up a DB in standby though, right? – James Jan 29 '18 at 15:33
  • No .. you cant backup a standby db .. but readonly - yes. when you said Standby/Read-Only I assumed you have a mix of standby and readonly dbs :-) – Kin Shah Jan 29 '18 at 15:35

I think there are at least the following methods here for your consideration.

  1. 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.

  2. 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.

  3. 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)

  • Oh man. I wrote a script that will back up & restore a whole database using .txt files, giggling to myself the whole time, because I thought it was really stupid. Time to go dust it off... – James Jan 29 '18 at 18:03

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.