【Sqlserver】基于备份和差异备份的还原测试

时间:2022-12-02 14:07:57
--------------------step1. create database & table 
USE master;

-- Drop database
IF DB_ID('TestBakDB') IS NOT NULL DROP DATABASE TestBakDB;

-- If database could not be created due to open connections, abort
IF @@ERROR = 3702
RAISERROR('Database cannot be dropped because there are still open connections.', 127, 127) WITH NOWAIT, LOG;

-- Create database
CREATE DATABASE TestBakDB;
GO

USE TestBakDB;
GO
if(object_id('bak_version','U') is not null ) drop table bak_version;
create table bak_version(
id bigint not null identity(1,1) constraint PK_bak_version primary key ,
val varchar(MAX),
dt datetime2 default (current_timestamp)
);


insert into bak_version(val) values('basic version');

select * from bak_version;

--------------------step2.Full bakup
declare @willBackDB nvarchar(100)=N'TestBakDB';/*Define*/
---声明变量
declare
@disk_path nvarchar(MAX)=N'N''C:\\DataBak\Full\'/*Edit path*/+@willBackDB+'_backup_'+CONVERT(VARCHAR(10),GETDATE(),120)+'.bak''',
@backup_name nvarchar(MAX)=N'N'''+@willBackDB+'_backup_'+CONVERT(VARCHAR(10),GETDATE(),120)+'''',
@execStr nvarchar(MAX)='';
/*---清理连接
DECLARE @kid varchar(100)
SET @kid=''
SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses
WHERE dbid=DB_ID(@willBackDB)
PRINT @kid
EXEC(@kid);
*/

---备份数据库
set @execStr=
'BACKUP DATABASE ['+@willBackDB+'] TO
DISK = '
+@disk_path+' WITH NOFORMAT, NOINIT, NAME = '+@backup_name+', SKIP, REWIND, NOUNLOAD,
COMPRESSION/*压缩选项*/, STATS = 10'
;

exec (@execStr);

---------验证备份完整性
declare @willBackDB nvarchar(100)=N'TestBakDB';/*Define*/
select backup_set_id,database_name,name,database_creation_date,backup_start_date,backup_finish_date
from msdb..backupset
where database_name = @willBackDB and backup_set_id =
(select max(backup_set_id) from msdb..backupset where database_name = @willBackDB)

--------------------step3.修改数据,差异备份
insert into bak_version(val) values('version 1');

select * from bak_version;

BACKUP DATABASE [TestBakDB] TO DISK = N'C:\DataBak\Diff\TestBakDB_backup_2017_08_02_173059_8788179.diff' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'datayesdb_backup_2017_08_02_173059_8788179', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10;

insert into bak_version(val) values('version 2');
select * from bak_version;

BACKUP DATABASE [TestBakDB] TO DISK = N'C:\DataBak\Diff\TestBakDB_backup_2017_08_02_173059_878817_2.diff' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'datayesdb_backup_2017_08_02_173059_878817_2', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10;

/*
差异备份:上次完整备份后的所有内容

还原一个数据库=完整备份+最近的一个差异备份
*/