数据库异地备份还原-HQY 一个和谐有爱的空间

15

2023
11
15:54:32

数据库异地备份还原

---备份
USE MASTER
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC MASTER ..xp_cmdshell 'net use \\sharef.cn\Share\DataBackup\SQLBackupData01 "password" /user:sharef\username'
GO
---\\sharef.cn\Share\DataBackup\SQLBackupData01 为中间存储备份文件的存储服务器共享路径
CREATE LOGIN [backup_link_user] WITH PASSWORD=N'admindba@0001221', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBA_Maintenance]
GO
CREATE USER [backup_link_user] FOR LOGIN [backup_link_user]
GO
USE [DBA_Maintenance]
GO
ALTER ROLE [db_datareader] ADD MEMBER [backup_link_user]
GO
USE [DBA_Maintenance]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [backup_link_user]
GO
USE [msdb]
GO
CREATE USER [backup_link_user] FOR LOGIN [backup_link_user]
GO
USE [msdb]
GO
ALTER ROLE [db_datareader] ADD MEMBER [backup_link_user]
GO
USE DBA_Maintenance
GO
CREATE PROCEDURE PRO_Archive_BackupData
AS
BEGIN
SET NOCOUNT ON
DECLARE @NOW DATETIME=GETDATE()
DECLARE @condition DATETIME
SET @condition=dateadd(day,-3,@NOW)
DELETE db_backup_record
OUTPUT DELETED.*,@NOW
INTO db_backup_archive
WHERE backup_end<=@condition and is_restore=1
SET NOCOUNT OFF
END
GO
CREATE PROCEDURE [dbo].[PRO_BACKUP_DB]
(
@Instance nvarchar(128)=NULL,
@DB_NAME SYSNAME,
@TYPE VARCHAR(12), --FULL DIFF LOG
@BACK_PATH VARCHAR(512)
)
WITH ENCRYPTION
AS
--version1.1
BEGIN
SET NOCOUNT ON
DECLARE @BACKUP_FULL_SQL NVARCHAR(1280)
DECLARE @BACKUP_DIFF_SQL NVARCHAR(1280)
DECLARE @BACKUP_LOG_SQL NVARCHAR(1280)
DECLARE @DATE_SERIAL_NUMBER NVARCHAR(16)
IF @Instance IS NULL
BEGIN
SET @Instance=@@SERVICENAME
END
SET @DATE_SERIAL_NUMBER='_'+''+CONVERT(varchar(8), GETDATE(), 112)+ replace(convert(char(5), getdate(), 108), ':', '')+''
IF @DB_NAME IS NULL
BEGIN
PRINT 'Database Does not Exist'
RETURN 1;
END
IF NOT EXISTS(SELECT NAME FROM MASTER.SYS.DATABASES WHERE NAME=@DB_NAME)
BEGIN
PRINT '[' + @DB_NAME + '] is not a valid database name!'
RETURN 1;
END
IF @TYPE NOT IN ('FULL','DIFF','LOG')
BEGIN
PRINT 'Database backup type must be [full], [log] or [diff]!'
RETURN 1;
END
DECLARE @EXISTS_INSTANCE_SQL NVARCHAR(512)
DECLARE @EXISTS_INSTANCE_FOLDER TABLE(COL NVARCHAR(512))
DECLARE @EXISTS_FOLDER TABLE(COL NVARCHAR(512))
DECLARE @EXISTS_SQL NVARCHAR(512)
DECLARE @EXISTS_SHAREFLODER_SQL NVARCHAR(512)
DECLARE @MKDIR_SQL NVARCHAR(512)
DECLARE @MKDIR_INSTANCE_SQL NVARCHAR(512)
SET @EXISTS_SHAREFLODER_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@BACK_PATH+''''
SET @EXISTS_INSTANCE_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+''''
SET @EXISTS_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+''''
SET @MKDIR_INSTANCE_SQL=N'EXEC MASTER..XP_CMDSHELL ''MKDIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+''', NO_OUTPUT'
SET @MKDIR_SQL=N'EXEC MASTER..XP_CMDSHELL ''MKDIR'+CHAR(9)+''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+''', NO_OUTPUT'
INSERT INTO @EXISTS_FOLDER EXEC SP_EXECUTESQL @EXISTS_SHAREFLODER_SQL,N'@BACK_PATH NVARCHAR(128)',@BACK_PATH
IF EXISTS (SELECT 1 FROM @EXISTS_FOLDER WHERE COL LIKE '%File Not Found%')
BEGIN
PRINT '['+@BACK_PATH+'] is not a valid !'
RETURN 2;
END
INSERT INTO @EXISTS_INSTANCE_FOLDER EXEC SP_EXECUTESQL @EXISTS_INSTANCE_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128)',@BACK_PATH,@Instance
IF EXISTS (SELECT 1 FROM @EXISTS_INSTANCE_FOLDER WHERE COL LIKE '%File Not Found%')
BEGIN
EXEC SP_EXECUTESQL @MKDIR_INSTANCE_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128)',@BACK_PATH,@Instance
END
INSERT INTO @EXISTS_FOLDER EXEC SP_EXECUTESQL @EXISTS_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DB_NAME NVARCHAR(128)',@BACK_PATH,@Instance,@DB_NAME
IF EXISTS(SELECT 1 FROM @EXISTS_FOLDER WHERE COL LIKE '%File Not Found%')
BEGIN
EXEC SP_EXECUTESQL @MKDIR_SQL,N'@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DB_NAME NVARCHAR(128)',@BACK_PATH,@Instance,@DB_NAME
END
IF RIGHT(@BACK_PATH,1)='\'
BEGIN
SET @BACK_PATH=LEFT(@BACK_PATH,LEN(@BACK_PATH)-1)
END
DECLARE @ERROR INT
DECLARE @BACKUP_FILENAME VARCHAR(512)
DECLARE @BACKUP_BEGIN DATETIME
IF @TYPE='FULL'
BEGIN
SET @BACKUP_FULL_SQL=N'BACKUP DATABASE'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
+'TO DISK= '''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@DB_NAME+@DATE_SERIAL_NUMBER+'.BAK'''+CHAR(9)+'WITH NOFORMAT, NOINIT,'
+'NAME=N'''+@DB_NAME+'-FULL Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
SET @BACKUP_FILENAME=@DB_NAME+@DATE_SERIAL_NUMBER+'.BAK'
--EXEC (@BACKUP_FULL_SQL)
SET @BACKUP_BEGIN=GETDATE()
EXEC SP_EXECUTESQL @BACKUP_FULL_SQL,N'@DB_NAME NVARCHAR(128),@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DATE_SERIAL_NUMBER NVARCHAR(16)',@DB_NAME,@BACK_PATH,@Instance,@DATE_SERIAL_NUMBER
SET @ERROR=@@ERROR
END
IF @TYPE='DIFF'
BEGIN
SET @BACKUP_DIFF_SQL=N'BACKUP DATABASE'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
+'TO DISK= '''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@DB_NAME+@DATE_SERIAL_NUMBER+'.DIFF'''+CHAR(9)+'WITH DIFFERENTIAL , NOFORMAT, NOINIT,'
+'NAME=N'''+@DB_NAME+'-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
SET @BACKUP_FILENAME=@DB_NAME+@DATE_SERIAL_NUMBER+'.DIFF'
--EXEC (@BACKUP_DIFF_SQL)
SET @BACKUP_BEGIN=GETDATE()
EXEC SP_EXECUTESQL @BACKUP_DIFF_SQL,N'@DB_NAME NVARCHAR(128),@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DATE_SERIAL_NUMBER NVARCHAR(16)',@DB_NAME,@BACK_PATH,@Instance,@DATE_SERIAL_NUMBER
SET @ERROR=@@ERROR
END
IF @TYPE='LOG'
BEGIN
SET @BACKUP_LOG_SQL=N'BACKUP LOG'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
+'TO DISK= '''+@BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@DB_NAME+@DATE_SERIAL_NUMBER+'.TRN'''+CHAR(9)+'WITH NOFORMAT, NOINIT,'
+'NAME=N'''+@DB_NAME+'-Transaction Log Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
SET @BACKUP_FILENAME=@DB_NAME+@DATE_SERIAL_NUMBER+'.TRN'
--EXEC (@BACKUP_LOG_SQL)
SET @BACKUP_BEGIN=GETDATE()
EXEC SP_EXECUTESQL @BACKUP_LOG_SQL,N'@DB_NAME NVARCHAR(128),@BACK_PATH NVARCHAR(128),@Instance NVARCHAR(128),@DATE_SERIAL_NUMBER NVARCHAR(16)',@DB_NAME,@BACK_PATH,@Instance,@DATE_SERIAL_NUMBER
SET @ERROR=@@ERROR
END
IF @ERROR=0
BEGIN
--INSERT INTO DBA_Maintenance.dbo.db_backup_record select @DB_NAME,@BACKUP_BEGIN,getdate(), @BACK_PATH+'\'+@Instance+'\'+@DB_NAME,@TYPE,@BACKUP_FILENAME,0 --version 1.0
INSERT INTO DBA_Maintenance.dbo.db_backup_record select @DB_NAME,@BACKUP_BEGIN,getdate(), @BACK_PATH+'\'+@Instance+'\'+@DB_NAME+'\'+@BACKUP_FILENAME,@TYPE,0 --version 1.1
END
SET NOCOUNT OFF
END
---还原
USE MASTER
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC MASTER ..xp_cmdshell 'net use \\sharef.cn\Share\DataBackup\SQLBackupData01 "password" /user:sharef\username'
GO
GO
CREATE LOGIN [backup_link_user] WITH PASSWORD=N'admindba@0001221', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBA_Maintenance]
GO
CREATE USER [backup_link_user] FOR LOGIN [backup_link_user]
GO
USE [DBA_Maintenance]
GO
ALTER ROLE [db_datareader] ADD MEMBER [backup_link_user]
GO
USE [DBA_Maintenance]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [backup_link_user]
GO
USE [msdb]
GO
CREATE USER [backup_link_user] FOR LOGIN [backup_link_user]
GO
USE [msdb]
GO
ALTER ROLE [db_datareader] ADD MEMBER [backup_link_user]
GO
EXEC sp_addlinkedserver
@server='VCST004003IS01' ,--被访问的服务器别名
@srvproduct='' ,
@provider='SQLOLEDB' ,
@datasrc='vcst004003sl01.sharef.cn\vcst004003is01' --要访问的服务器
EXEC sp_addlinkedsrvlogin
'VCST004003IS01', --被访问的服务器别名
'false',
NULL,
'backup_link_user', --帐号
'admindba@0001221' --密码
GO
CREATE PROCEDURE [dbo].[PRO_AccordingTo_Parameters_Restore_DB]
(
@Instance nvarchar(128)=NULL,--暂时无用
@DB_NAME NVARCHAR(128)=NULL,
@TYPE NVARCHAR(16), --FULL DIFF LOG
@RESTORE_PATH NVARCHAR(128)=N'\\sharef.cn\Share\DataBackup\SQLBackupData01\SQLBackupData01\VCST004005IS01',
@LOCAL_DBFILES_PATH NVARCHAR(512)=N'T:\MSSQL11.VWSR027002IS02\MSSQL\DATA\',
@DATE_TIME DATETIME=NULL
)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @RESTORE_PATH_FILENAME NVARCHAR(512)
DECLARE @RESTORE_FULL_SQL NVARCHAR(4000)
DECLARE @RESTORE_DIFF_SQL NVARCHAR(4000)
DECLARE @RESTORE_LOG_SQL NVARCHAR(4000)
DECLARE @ERROR INT=0
DECLARE @i int=1
DECLARE @count int=1
DECLARE @RESTORE_BEGIN DATETIME
DECLARE @EXISTS_FOLDER TABLE(COL NVARCHAR(512))
DECLARE @EXISTS_RESTOREDB_FOLDER_SQL NVARCHAR(512)
IF @DATE_TIME IS NULL AND @TYPE='FULL'
BEGIN
SET @DATE_TIME=DATEADD(day,-3,getdate())
END
IF @DATE_TIME IS NULL AND @TYPE='DIFF'
BEGIN
SET @DATE_TIME=DATEADD(hour,-3,getdate())
END
IF @TYPE NOT IN ('FULL','DIFF','LOG')
BEGIN
PRINT 'Please Specify The Type Of The Restore Database ([full], [log] or [diff])!'
RETURN;
END
IF @RESTORE_PATH IS NULL
BEGIN
PRINT 'The Path To Restore Database Cannot Be Empty!'
RETURN;
END
ELSE
BEGIN
SET @EXISTS_RESTOREDB_FOLDER_SQL=N'EXEC MASTER..XP_CMDSHELL ''DIR'+CHAR(9)+''+@RESTORE_PATH+'\'+@DB_NAME+''''
INSERT INTO @EXISTS_FOLDER EXEC SP_EXECUTESQL @EXISTS_RESTOREDB_FOLDER_SQL,N'@RESTORE_PATH NVARCHAR(128),@DB_NAME NVARCHAR(128)',@RESTORE_PATH,@DB_NAME
IF EXISTS (SELECT 1 FROM @EXISTS_FOLDER WHERE COL LIKE '%File Not Found%')
BEGIN
PRINT '['+@RESTORE_PATH+'] is not a valid !'
RETURN;
END
END
IF RIGHT(@RESTORE_PATH,1)='\'
BEGIN
SET @RESTORE_PATH=LEFT(@RESTORE_PATH,LEN(@RESTORE_PATH)-1)
END
DECLARE @tmp TABLE (rn int,dbname sysname,backup_type varchar(16),backup_path_filename nvarchar(512))
DECLARE @RESTORE_TYPE VARCHAR(6)
IF @TYPE='FULL'
BEGIN
SET @RESTORE_TYPE='D'
END
IF @TYPE='DIFF'
BEGIN
SET @RESTORE_TYPE='I'
END
IF @TYPE='LOG'
BEGIN
SET @RESTORE_TYPE='L'
END
INSERT INTO @tmp SELECT ROW_NUMBER() over(order by database_name)rn,BACKUP_DATA.database_name, BACKUP_DATA.backup_type,BACKUP_DATA.physical_device_name
FROM
(
SELECT
bjs.database_name,
CASE bjs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'DIFF'
END AS backup_type,
bkmf.physical_device_name,
bjs.first_lsn,bjs.last_lsn,
bjs.database_backup_lsn,
bjs.backup_finish_date
FROM [VCST004005sl01].[msdb].[dbo].[backupmediafamily] AS bkmf WITH(NOLOCK)
INNER JOIN [VCST004005sl01].[msdb].[dbo].[backupset] AS bjs WITH(NOLOCK)
ON bkmf. media_set_id=bjs.media_set_id
WHERE bjs.RECOVERY_MODEL='FULL'
AND bjs.type=@RESTORE_TYPE
AND bjs.backup_finish_date>=@DATE_TIME
AND bjs.database_name not in ('master','msdb','model','tempdb','DBA_Maintenance')
)AS BACKUP_DATA
WHERE NOT EXISTS( SELECT 1
FROM msdb .dbo. backupset AS bus WITH(NOLOCK)
WHERE BACKUP_DATA.database_name=bus.database_name
AND BACKUP_DATA.first_lsn= bus.first_lsn
AND BACKUP_DATA.last_lsn=bus.last_lsn
AND bus.backup_finish_date>=@DATE_TIME
AND bus.type=@RESTORE_TYPE
)
AND NOT EXISTS (SELECT 1 FROM (SELECT database_name,MAX(backup_finish_date)backup_finish_date
FROM msdb .dbo. backupset AS bus WITH(NOLOCK)
WHERE bus.backup_finish_date>=@DATE_TIME
AND type=@RESTORE_TYPE
GROUP BY database_name
)tmp
WHERE BACKUP_DATA.database_name=tmp.database_name
AND BACKUP_DATA.backup_finish_date<=tmp.backup_finish_date
)
SELECT @count=count(*) from @tmp
WHILE @i<=@count
BEGIN
SET @DB_NAME=NULL
SET @RESTORE_PATH_FILENAME=NULL
SET @RESTORE_PATH=NULL
SET @ERROR=0
SELECT @DB_NAME=dbname,
@RESTORE_PATH_FILENAME=backup_path_filename
FROM @tmp WHERE rn=@i
DECLARE @FileListInfo TABLE
(
LogicalName nvarchar(512) null,
PhysicalName nvarchar(512) null,
Type varchar(16) null,
FileGroupName nvarchar(128) null,
FileSize bigint null ,
FileMaxSize Bigint null,
FileId bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint nvarchar(128)
)
DECLARE @Get_FilelistSQL nvarchar(max)
SET @Get_FilelistSQL = N'RESTORE FILELISTONLY FROM DISK = N'''+@RESTORE_PATH_FILENAME+''''+CHAR(9)
INSERT INTO @FileListInfo EXEC SP_EXECUTESQL @Get_FilelistSQL
DECLARE @LogicalName_D NVARCHAR(512)
DECLARE @LogicalName_L NVARCHAR(512)
SELECT @LogicalName_D=LogicalName FROM @FileListInfo WHERE Type='D'
SELECT @LogicalName_L=LogicalName FROM @FileListInfo WHERE Type='L'
IF @TYPE='FULL'
BEGIN
SET @RESTORE_FULL_SQL=N'RESTORE DATABASE'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
+'FROM DISK= '''+@RESTORE_PATH_FILENAME+''''+CHAR(9)
+'WITH FILE = 1,MOVE N'''+@DB_NAME+''' TO N'''+@LOCAL_DBFILES_PATH+@LogicalName_D+'.MDF'',
MOVE N'''+@DB_NAME+'_LOG '' TO N'''+@LOCAL_DBFILES_PATH+@LogicalName_L+'.LDF'',NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
SET @RESTORE_BEGIN =GETDATE()
EXEC SP_EXECUTESQL @RESTORE_FULL_SQL
,N'@DB_NAME NVARCHAR(128),@RESTORE_PATH_FILENAME NVARCHAR(512),@LOCAL_DBFILES_PATH NVARCHAR(512),@LogicalName_D NVARCHAR(128),@LogicalName_L NVARCHAR(128)'
,@DB_NAME,@RESTORE_PATH_FILENAME,@LOCAL_DBFILES_PATH,@LogicalName_D,@LogicalName_L
SET @ERROR=@ERROR+@@ERROR
END
IF @TYPE='DIFF'
BEGIN
SET @RESTORE_DIFF_SQL=N'RESTORE DATABASE'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
+'FROM DISK= '''+@RESTORE_PATH_FILENAME+''''+CHAR(9)
+'WITH FILE = 1,MOVE N'''+@DB_NAME+''' TO N'''+@LOCAL_DBFILES_PATH+@LogicalName_D+'.MDF'',
MOVE N'''+@DB_NAME+'_LOG '' TO N'''+@LOCAL_DBFILES_PATH+@LogicalName_L+'.LDF'',NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'
SET @RESTORE_BEGIN =GETDATE()
EXEC SP_EXECUTESQL @RESTORE_DIFF_SQL
,N'@DB_NAME NVARCHAR(128),@RESTORE_PATH_FILENAME NVARCHAR(512),@LOCAL_DBFILES_PATH NVARCHAR(512),@LogicalName_D NVARCHAR(128),@LogicalName_L NVARCHAR(128)'
,@DB_NAME,@RESTORE_PATH_FILENAME,@LOCAL_DBFILES_PATH,@LogicalName_D,@LogicalName_L
SET @ERROR=@ERROR+@@ERROR
END
IF @TYPE='LOG'
BEGIN
SET @RESTORE_LOG_SQL=N'RESTORE LOG'+CHAR(9)+'['+@DB_NAME+']'+CHAR(9)
+'FROM DISK= '''+@RESTORE_PATH_FILENAME+''''+CHAR(9)
+'WITH FILE = 1,NORECOVERY, NOUNLOAD, STATS = 10'
SET @RESTORE_BEGIN =GETDATE()
EXEC SP_EXECUTESQL @RESTORE_LOG_SQL
,N'@DB_NAME NVARCHAR(128),@RESTORE_PATH_FILENAME NVARCHAR(512)'
,@DB_NAME,@RESTORE_PATH_FILENAME
SET @ERROR=@ERROR+@@ERROR
END
IF @ERROR=0
BEGIN
INSERT INTO DBA_Maintenance.dbo.db_restory_record (dbname,restore_begin,restore_end,restore_type,restore_filename,flag)
VALUES (@DB_NAME,@RESTORE_BEGIN,GETDATE(),@TYPE,@RESTORE_PATH_FILENAME,1)
END
SET @i=@i+1
END
SET NOCOUNT OFF
END
GO