Tags

,

Run the

first script @Primary The output gives an easy way to mirror the databases quickly.


DECLARE

@Database sysname, @Command varchar(8000),@PATH_LOCAL varchar(100),@PATH_NETWORK varchar(100),@Temp varchar(1000),@i int

DECLARE

@SECONDARY_SERVER varchar(100),@PRIMARY_SERVER varchar(100)

set

@Database=‘DB_NAME’

set

@PATH_NETWORK=‘M:\NETWORK_PATH_LOCAL\’

set

@PATH_LOCAL=‘\NETWORK_PATH\’

set

@PRIMARY_SERVER=‘PRIMARY’

set

@SECONDARY_SERVER=‘SECONDARY’

set

@i=0

set

@Command =‘BACKUP DATABASE ‘+@Database+‘ to disk=”’+@PATH_LOCAL+@Database+‘.bak”’

print

‘PRIMARY’

print

  @Command

DECLARE

cur CURSORfor

select‘ MOVE N”’+name+”’ TO N”M:\MSSQL10.INSTANCE01\MSSQL\DATA\’+name+case groupid when 0 then‘.ldf”’else‘.mdf”’end

fromsysaltfileswheredbid=db_id(@Database)

OPEN

cur;

FETCH

NEXTFROM cur INTO @Temp ;

set

@Command =‘RESTORE DATABASE ‘+@Database+‘ from disk=”’+@PATH_NETWORK+@Database+‘.bak” with norecovery, ‘

WHILE

(@@FETCH_STATUS<>1)

BEGIN

if ( @i <>0 )set @Command=@Command+‘,’

set @Command=@Command+@Temp

set @i=@i+1

FETCHNEXTFROM cur INTO @Temp ;

END

CLOSE

cur;DEALLOCATE cur;

print

print

‘SECONDARY’

print

@Command


print

print

‘PRIMARY’

set

@Command=‘backup log ‘+@Database+‘ to disk=”’+@PATH_LOCAL+@Database+‘.trn”’

print

@Command


print

print

‘SECONDARY’

set

@Command=‘restore log ‘+@Database+‘ from disk=”’+@PATH_NETWORK+@Database+‘.trn” with norecovery’

print

@Command

set

@Command=‘alter database ‘+@Database+‘ set partner=”TCP://’+@PRIMARY_SERVER+‘:7001”’

print

@Command


print

print

‘PRIMARY’

set

@Command=‘alter database ‘+@Database+‘ set partner=”TCP://’+@SECONDARY_SERVER+‘:7001”’

print

@Command

set

@Command=‘alter database ‘+@Database+‘ set safety OFF’

print

@Command



Script

OutputSecond Script


PRIMARY

BACKUP DATABASE DB_NAME to disk=‘\NETWORK_PATH\DB_NAME.bak’

SECONDARY

RESTORE DATABASE DB_NAME from disk=‘M:\NETWORK_PATH_LOCAL\DB_NAME.bak’ with norecovery,  MOVE N’DATA_FILE’ TO N’M:\MSSQL10.INSTANCE01\MSSQL\DATA\DATA_FILE.mdf’, MOVE N’LOG_FILE’ TO N’M:\MSSQL10.INSTANCE01\MSSQL\DATA\LOG_FILE.ldf’

PRIMARY

backup log DB_NAME to disk=‘\NETWORK_PATH\DB_NAME.trn’

SECONDARY

restore logDB_NAME from disk=‘M:\NETWORK_PATH_LOCAL\DB_NAME.trn’ with norecovery

alter database DB_NAME set partner=‘TCP://PRIMARY:7001’

PRIMARY

alter database DB_NAME set partner=‘TCP://SECONDARY:7001’

alter database DB_NAME set safety OFF

Advertisements