Whilst Database mirroring is now deprecated with the release of SQL Server 2012 (http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.110%29.aspx) it’s still something I’m going to be using heavily with 2008 and 2008R2 as it’s a simple way of implementing failover between discrete clusters or instances. They’ll move to Availability groups eventually, but not in the short term as there’s no direct way to do this without building a new Windows cluster that contains the Fallover Clusters
However, with a database with frequent transaction log backups (and if it’s important enough to mirror, it’s almost certainly important enough to deserver frequent log backups) it can take a bit of work to get everything synced up again. Or catching back up if you’ve had to break the mirror for any reason.
The first script is to sync up from scratch. It connects back to the primary server to get the back information. It assumes that:
- You’re running it as an account that exists on both instances with sysadmin role granted
- You don’t already have a linked server with the name of the primary instance on the secondary
- The file structure is identical on the 2 instance
- All backup files (log and db) are available in their main location (ie; not taken to tape and deleted)
declare @last_lsn numeric(25,0), @first_lsn numeric(25,0), @@tmp_lsn numeric(25,0) declare @dbname varchar(50), @restorefile varchar(255), @oserver nvarchar(50), @lserver nvarchar(50)
--Set server name twice. First is used for building db links, 2nd is used in the actual query to hide special characters set @oserver='server1\instance' set @lserver='[server1\instance]' set @dbname='bigdb1'
EXEC master.dbo.sp_addlinkedserver @oserver, @srvproduct=@oserver, @provider=N'SQLNCLI', @datasrc=@oserver EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@oserver,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
exec(N'select c.physical_device_name, a.last_lsn, a.first_lsn into ##mirror from '+@lserver+'.msdb.dbo.backupset a, '+@lserver+'.msdb.dbo.backupmediaset b, '+@lserver+'.msdb.dbo.backupmediafamily c where a.database_name='''+@dbname+''' and a.media_set_id=b.media_set_id and b.media_set_id=c.media_set_id and a.last_lsn > (select max(first_lsn) from '+@lserver+'.msdb.dbo.backupset where database_name='''+@dbname+''' and type=''D'') order by last_lsn asc') declare restore_cursor cursor for select physical_device_name, last_lsn, first_lsn from ##mirror order by last_lsn asc open restore_cursor fetch next from restore_cursor into @restorefile, @last_lsn ,@first_lsn while @@FETCH_STATUS = 0 begin --comment out the exec line and uncomment the print line if you want to check the restore commands first