数据库异地备份还原

最新推荐文章于 2023-08-09 17:32:06 发布
最新推荐文章于 2023-08-09 17:32:06 发布
阅读量878 收藏
点赞数
分类专栏： SQL SERVER2008 文章标签： 数据库 sqlserver database windows 网络
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhaowenzhong/article/details/6273055
版权 


---备份
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



Felixzhaowenzhong
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库自动异地备份与恢复
nbwinpe的博客
01-08 6108
数据库在企业中的作用越来越重要，所以我们需要做好数据库的备份。如果我们把数据库备份到安装数据库的服务器上，这样做的风险还是很大的，因为有时候数据库损坏的原因就是安装数据库的服务器或者虚拟机损坏引起的。所以我们备份数据库时，尽量选择异地备份数据库。这里分享下用Navicat异地备份与恢复数据库的方法。 1、 首先用navicat连接上远端的数据库 这里我们连接到测试的服务器10.195.2...
数据库定时异地备份与还原软件
12-08
数据同步备份与还原软件，主要是实际现实生活中要将生成环境下的数据备份到公司进行相关历史数据的统计，也同时为了保证生产环境下的数据的安全性，做到生产环境下的定时备份，同时也达到异地备份的效果，运行环境要...
SQL数据库异地备份
u013597888的专栏
03-11 1414
方法一：做“数据库维护计划” 1.本地服务器（计算机管理里）建立一个操作系统用户，例如sa（用户名和密码需要跟远程计算机所建立的用户情况相同），赋予此用户administrators组权限，加上相应的密码。 2.在企业管理器→右键点击(local)(windows nt)选择属性→在“安全性”选项里的“启动服务帐户”选择使用“本帐号”，在里面填入第一步建立的用户和密码（sa）。（这里的
mongodb的备份，数据迁移--05
欢迎查阅
11-27 842
备份和迁移1. mongoexport（导出）/mongoimport（导入） 备份1.1 mongoexport参数1.2 mongoimport导入到参数1.3 MySQL的数据迁移到mongodb2. mongodump（导出）和mongorestore（导入）2.1 mongodump参数2.2 mongorestore恢复 两种备份的区别 mongoexport/mongoimport mongodump/mongorestore 导入/导出的是JSON格式或者CSV格式 导入/导
Linux下MongoDB定时异地备份方案
Wcybaonier
08-09 828
首先安装crontabs。
对ubuntu中MongoDB数据进行异地备份
ai_0922的博客
03-10 269
对ubuntu上的MongoDB数据库中的数据进行异地备份，想到异地备份肯定就需要用到网络协议方面的知识点，第一印象想到就是用的比较多的ftp和sftp，想着追求新知识，于是本人使用了没接触过的smb协议。 在上篇文章<mongodb自动备份>的基础上来实现异地备份 安装smb服务 查看是否安装过smb服务smbd -V 如未安装，通过以下命令安装 sudo apt-get...
mysql 数据库备份和还原方法集锦 推荐
09-11
- 配合灾备策略：考虑异地备份和高可用架构。 总结，MySQL的备份和恢复策略涉及到多种方法，选择哪种取决于业务需求、数据量、恢复速度和资源限制。正确理解和运用这些方法，能够有效保护数据安全，减少因系统故障...
藏经阁-数据库异地备份及不还原快速查询备份集最佳实践.pdf
最新发布
09-11
藏经阁-数据库异地备份及不还原快速查询备份集最佳实践 本文档旨在介绍阿里云数据库异地备份及不还原快速查询备份集的最佳实践，旨在帮助用户更好地理解和应用数据库异地备份技术。 一、方案背景 数据库异地备份...
mysql数据库自动备份
08-16
下面将详细介绍如何设置MySQL数据库的自动备份，包括本地备份、异地备份以及过期备份文件的自动删除，并简要提及备份恢复的过程。 **一、数据库数据的自动备份** 1.1 **自动备份介绍** 自动备份是指通过编写脚本和...
Mysql数据库备份恢复测试报告-xtrabackup.docx
03-29
通过已经备份的数据库文件对数据库进行恢复测试，并输出测试报告，本次测试对数据库的全量及增量备份进行恢复，并在恢复完成后验证数据是否正常，恢复时间是否与备份时间相一致。...
mongodb和mysql的expect方式的异地备份
04-23
mongodb和mysql的expect方式的异地备份 mysql数据的异地备份： （1）在mysql服务器上安装.exp文件运行需要的运行环境.并检测 #yum install expect
SQL Server2005 异地备份的多种方法
12-15
第一种方法： 这是一个很常见的需求，网上也有相关的解决方法，主要是通过映射网络盘和执行cmdshell命令来实现，他们的不好处在于数据重复传递 今天所说的实现方法利用SQL Server服务帐号模式和安全机制来访问网络途径，方法如下： 　　1：要实现异地备份，必须使用域用户帐号来启动SQL Server服务以及SQL Server Agent服务，因为本地系统帐户无法访问网络。在计算机管理服务里找到这两个服务，将登录账号改为域用户，注意密码也要录入，如果以后这个域用户的密码更改了，记得这里也要改，否则这两项服务无法启动。 　　2:在异地机器中建立一个共享文件夹，并设置合适权限。 　  3：在S
MongoDB数据库的备份与恢复详解
孙彦辉的专栏
09-13 2372
本文主要介绍了MongoDB数据库的备份与恢复的知识，包括冷备份、以及备份和恢复两种工具的使用，最后介绍了读扩展式的备份机制，希望能够对您有所帮助。 MongoDB是怎么实现数据的备份与恢复，故障切换以及数据库服务器的负载均衡等功能的呢？本文我们就介绍这些知识。 备份与恢复 在创建MongoDB服务的时候，通过--dbpath指定目录就是存放mongdb数
数据备份与数据还原 ----数据库的异地容灾策略
wjb123sw99的博客
07-02 5860
Tip:最近，老大让在数据库加密系统里添加数据备份与还原的功能，在不影响原有系统的性能基础上保证设计备份策略，欲哭无泪。一个关于数据库的系统，首先要保证数据的稳定性和安全性，在保证数据的不会被丢失的基础上保证数据不会被第三方窃取。威胁数据库的因素大致分二种：1：物理威胁：对数据库服务器的物理损坏，例如：电磁干扰，硬盘损坏等。2：网络威胁：通过网络窃取数据库中内容，常用手段有：通过窃取服务器权限获取...
sqlserver 数据库 给予用户 只读db_datareader权限与存储过程权限
从事厨房信息化行业，万能对接所有点餐系统，kds，智能控菜，酒店协调软件
07-10 4742
注：db_datareader没有存储过程访问权限，若想全开，如下： GRANT EXEC TO datareader GRANT ALTER TO datareader
Linux下Mongodb安装、定时备份及ftp异地回传
weixin_45950088的博客
07-10 209
定时备份 #!/bin/sh DUMP=/mnt/mongodb/mongodb-linux-x86_64-4.0.1/bin/mongodump OUT_DIR=/backup/mongod_bak/mongod_bak_now TAR_DIR=/backup/mongod_bak/mongod_bak_list DATE=`date +%Y_%m_%d` DB_NAME=heosun DAYS...
RHEL6.5 安装 DB2 10.5 错误 SQL1001N is not a valid database name. SQLSTATE=2E000
navida的专栏
10-13 3991
RHEL6.5 安装 DB2 10.5  执行脚本来创建数据库 $ db2 create db hcba_appstore using codeset UTF-8 territory CN COLLATE USING SYSTEM 出现错误 SQL1001N  "hcba_appstore" is not a valid database name.  SQLSTATE=2E000
MongoDB数据备份与恢复
seteor的专栏
09-05 9362
备份：mongodump     mongodump --help #命令帮助 mongodump  -h 192.168.0.1 -u "mydbuser" -p "123" -d mydb  -o d:\mydb #备份192.168.0.1上的mydb库到本地的 d:\mydb 目录，如果mongod服务启用了认证，则必须使用用户名密码进行谁 #注意此处的认证用
迁移数据库——rman异地恢复
事后诸葛亮的程序人生（微信：zq9017197）
07-02 4003
数据库版本是：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 1、记录一下源数据库的DBID sys@ORCL> select DBID from v$database;       DBID ---------- 1301772781 2、open之后，在源数据库端执
数据库异地备份方案.docx
01-13
数据库异地备份方案是为了保证在生产数据库发生异常情况下能够快速恢复数据而设计的。该方案通过手动rman备份脚本并测试恢复成功来确保数据的可恢复性。具体的脚本过程被详细描述在"数据库异地备份方案.docx"中。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值