|
CHAR(13)
PRINT @stat
SET @Restorepath1=''
SET @Restorepath2=@Restorepath+@DBName+'_FullBackup_1.bak'
SET @SQL = 'RESTORE FILELISTONLY FROM DISK = '''+@Restorepath2+''''
INSERT INTO #BackupFileList EXEC (@SQL);
DECLARE @LNAME NVARCHAR(2000)
DECLARE @PNAME NVARCHAR(2000)
DECLARE CurTBName CURSOR
FOR
SELECT LogicalName,PhysicalName
FROM #BackupFileList
OPEN CurTBName
FETCH NEXT FROM CurTBName INTO @LNAME,@PNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Restorepath1=' MOVE N'''+@LNAME+''' TO N'''+@PNAME+''', '+CHAR(13)+@Restorepath1
FETCH NEXT FROM CurTBName INTO @LNAME,@PNAME
END
CLOSE CurTBName
DEALLOCATE CurTBName
SET @stat='USE [master]
RESTORE DATABASE '+@DBName+' FROM DISK = N'''+@Restorepath+@DBName+'_FullBackup_1.bak'' WITH FILE = 1,'+CHAR(13)
+@Restorepath1
+'NOUNLOAD,NORECOVERY, REPLACE, STATS = 5
GO'
SET @stat='USE [master]
RESTORE DATABASE '+@DBName+' FROM DISK = N'''+@Restorepath+@DBName+'_logBackup_2.bak'' WITH FILE = 1,'+CHAR(13)
+'NOUNLOAD,NORECOVERY, REPLACE, STATS = 5
GO'
PRINT @stat+CHAR(13)+CHAR(13)
DROP TABLE #BackupFileList
--------------------------------------------------------------------------------
PRINT '-- ============================================='
SET @stat='--8、增加镜像伙伴，需要先在备机上执行，再执行主机，镜像弄好之后，默认为事务安全等级为FULL'+CHAR(13)
PRINT @stat
SET @stat='--备机上执行'+CHAR(13)
+'USE [master]
GO
ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@masterip+':5022''; --主机服务器的ip'+CHAR(13)+CHAR(13)
PRINT @stat
SET @stat='--主机上执行'+CHAR(13)
+'USE [master]
GO
ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@mirrorip+':5022''; --镜像服务器的ip'+CHAR(13)+CHAR(13)
PRINT @stat
SET @stat='ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@witness+':5022''; --见证服务器的ip'+CHAR(13)+CHAR(13)
PRINT @stat
希望对大家有帮助
最后附上镜像相关脚本
--=================================
--拆除镜像
SELECT DB_NAME([database_id]) as 'dbname',* FROM sys.[database_mirroring]
GO
ALTER DATABASE [test] SET PARTNER OFF
ALTER DATABASE [test] SET WITNESS OFF
--=================================
--恢复镜像
USE [master]
GO
ALTER DATABASE [Demo1] SET PARTNER RESUME
GO
--=================================
--挂起镜像
USE [master]
GO
ALTER DATABASE [Demo1] SET PARTNER SUSPEND
GO
--===================================================
--未发送的日志和未重做的日志情况
WITH tmp AS(
SELECT
DB_NAME(Database_id) AS DatabaseName,
ROW_NUMBER()OVER(PARTITION BY Database_id ORDER BY local_time DESC) AS RID,
*
FROM msdb.dbo.dbm_monitor_data
)
SELECT * FROM tmp
WHERE RID=1
--看一下redo_queue 和send_queue
--=================================
--删除镜像
USE [master]
GO
ALTER DATABASE [Demo1] SET PARTNER OFF
GO
--=================================
--移除见证服务器
USE [master]
GO
ALTER DATABASE [Demo1] SET WITNESS OFF
GO
--=================================
--修改为高性能模式
USE [master]
GO
ALTER DATABASE [Demo1] SET PARTNER SAFETY OFF
GO
--=================================
--修改为高安全模式
USE [master]
GO
ALTER DATABASE [Demo1] SET PARTNER SAFETY FULL
GO
--=================================
--在高安全下手动转移镜像（在主服务器上）
USE [master]
GO
ALT |