Script to mirror MS SQL Server databases between 2 servers
- by David W
Hi
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 :)
