---备份USE MASTERGO 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 MASTERGOEXEC sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE WITH OVERRIDE;GOEXEC MASTER ..xp_cmdshell 'net use \\sharef.cn\Share\DataBackup\SQLBackupData01 "password" /user:sharef\username' GOGOCREATE LOGIN [backup_link_user] WITH PASSWORD=N'admindba@0001221', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE [DBA_Maintenance]GOCREATE USER [backup_link_user] FOR LOGIN [backup_link_user]GOUSE [DBA_Maintenance]GOALTER ROLE [db_datareader] ADD MEMBER [backup_link_user]GOUSE [DBA_Maintenance]GOALTER ROLE [db_datawriter] ADD MEMBER [backup_link_user]GOUSE [msdb]GOCREATE USER [backup_link_user] FOR LOGIN [backup_link_user]GOUSE [msdb]GOALTER ROLE [db_datareader] ADD MEMBER [backup_link_user]GOEXEC sp_addlinkedserver @server='VCST004003IS01' ,--被访问的服务器别名 @srvproduct='' , @provider='SQLOLEDB' , @datasrc='vcst004003sl01.sharef.cn\vcst004003is01' --要访问的服务器 EXEC sp_addlinkedsrvlogin 'VCST004003IS01', --被访问的服务器别名 'false', NULL, 'backup_link_user', --帐号 'admindba@0001221' --密码 GOCREATE 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 ENCRYPTIONASBEGINSET NOCOUNT ONDECLARE @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=0DECLARE @i int=1DECLARE @count int=1DECLARE @RESTORE_BEGIN DATETIMEDECLARE @EXISTS_FOLDER TABLE(COL NVARCHAR(512))DECLARE @EXISTS_RESTOREDB_FOLDER_SQL NVARCHAR(512)IF @DATE_TIME IS NULL AND @TYPE='FULL'BEGINSET @DATE_TIME=DATEADD(day,-3,getdate())ENDIF @DATE_TIME IS NULL AND @TYPE='DIFF'BEGINSET @DATE_TIME=DATEADD(hour,-3,getdate())ENDIF @TYPE NOT IN ('FULL','DIFF','LOG')BEGINPRINT 'Please Specify The Type Of The Restore Database ([full], [log] or [diff])!'RETURN;ENDIF @RESTORE_PATH IS NULLBEGINPRINT 'The Path To Restore Database Cannot Be Empty!'RETURN;ENDELSEBEGINSET @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_NAMEIF EXISTS (SELECT 1 FROM @EXISTS_FOLDER WHERE COL LIKE '%File Not Found%')BEGINPRINT '['+@RESTORE_PATH+'] is not a valid !'RETURN;ENDENDIF RIGHT(@RESTORE_PATH,1)='\'BEGINSET @RESTORE_PATH=LEFT(@RESTORE_PATH,LEN(@RESTORE_PATH)-1)ENDDECLARE @tmp TABLE (rn int,dbname sysname,backup_type varchar(16),backup_path_filename nvarchar(512))DECLARE @RESTORE_TYPE VARCHAR(6)IF @TYPE='FULL'BEGINSET @RESTORE_TYPE='D'ENDIF @TYPE='DIFF'BEGINSET @RESTORE_TYPE='I'ENDIF @TYPE='LOG'BEGINSET @RESTORE_TYPE='L'ENDINSERT 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 (SELECTbjs.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_TYPEAND bjs.backup_finish_date>=@DATE_TIMEAND bjs.database_name not in ('master','msdb','model','tempdb','DBA_Maintenance'))AS BACKUP_DATA WHERE NOT EXISTS( SELECT 1FROM msdb .dbo. backupset AS bus WITH(NOLOCK)WHERE BACKUP_DATA.database_name=bus.database_nameAND BACKUP_DATA.first_lsn= bus.first_lsnAND BACKUP_DATA.last_lsn=bus.last_lsnAND 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_dateFROM msdb .dbo. backupset AS bus WITH(NOLOCK)WHERE bus.backup_finish_date>=@DATE_TIMEAND type=@RESTORE_TYPEGROUP BY database_name)tmpWHERE BACKUP_DATA.database_name=tmp.database_nameAND BACKUP_DATA.backup_finish_date<=tmp.backup_finish_date)SELECT @count=count(*) from @tmpWHILE @i<=@countBEGINSET @DB_NAME=NULLSET @RESTORE_PATH_FILENAME=NULLSET @RESTORE_PATH=NULLSET @ERROR=0SELECT @DB_NAME=dbname,@RESTORE_PATH_FILENAME=backup_path_filenameFROM @tmp WHERE rn=@iDECLARE @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_FilelistSQLDECLARE @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'BEGINSET @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_LSET @ERROR=@ERROR+@@ERRORENDIF @TYPE='DIFF'BEGINSET @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_LSET @ERROR=@ERROR+@@ERRORENDIF @TYPE='LOG'BEGINSET @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_FILENAMESET @ERROR=@ERROR+@@ERRORENDIF @ERROR=0BEGININSERT 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)ENDSET @i=@i+1ENDSET NOCOUNT OFFENDGO
