I have about 200 sites each of which have 2 servers running MSSQL (2k5 at some sites, 2k8 at others)

One server is production and the other is primarily there as a backup. We're rebuilding all of these servers this year and as part of that we will have to set up mirroring for ... a lot ... of databases.

Some of these sites have 45 databases so mirroring them manually is going to be a huge pain.

I was going to write a batch script which uses SQLCMD to backup the database and log, copies to the secondary server, restores the backup and log with norecovery, creates the endpoints and sets the partner.

This in itself isn't too complicated, but i'd love to see what other people have done as i'm not very confident in catching errors using the process i've outlined above.

I've seen Tools to manage sql 2008 database mirroring? Which looks really good, but the formatting is jumbled and I can't get it to work. If anyone has any other scripts they've written and are willing to share I'd be eternally grateful.

Ideally I'd love to be able to use a script to ensure there are matching endpoints (same ports) on both servers, backup the database, backup the log, copy the backups to second server, restore database and log with norecovery, set the partners on both servers, and somehow confirm that the databases are linked and synchronized.

Well, thanks for reading :)

  • Wow, good luck! Anyhow I fixed up the formatting on the other question, so it might help you more now. – Mark Henderson Feb 8 '11 at 23:06
  • Thanks Mark. Very helpful, I might be able to get this done! – David W Feb 9 '11 at 23:28

I ended up building a set of scripts to manage and complete the mirroring of databases, based off the snips provided by Jason Cumberland in link I provided in the question.

I'll share them once tested properly. I haven't added any logging into the process as yet.

  • I'd be interested to see those. – user3914 Feb 16 '11 at 21:25

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.