数据库备份方案

mxbing1984

--1.完整备份+差异备份
Create Database Back

Alter database Back Set Recovery Simple

Use Back

Create Table Test
(
        Col Varchar(100)
)

Insert Into Test Values('完整备份')
Backup database Back to disk='c:\Back1.bak'

Insert Into Test Values('差异备份1')
backup database Back to disk='c:\Back2.bak' With DIFFERENTIAL

Insert Into Test Values('差异备份2')
backup database Back to disk='c:\Back3.bak' With DIFFERENTIAL

Use master

--还原数据库1
Restore Database Back From Disk='C:\Back1.bak' With Recovery,REPLACE
Select * From Back.dbo.Test

--还原数据库2
Restore Database Back From Disk='C:\Back1.bak' With noRecovery,REPLACE
Restore Database Back From Disk='C:\Back2.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库3
Restore Database Back From Disk='C:\Back1.bak' With noRecovery,REPLACE
Restore Database Back From Disk='C:\Back3.bak' With Recovery
Select * From Back.dbo.Test

--删除备份文件
EXECUTE master.dbo.xp_delete_file 0,N'C:',N'bak',N'2015-07-05'

--删除数据库
Drop DataBase Back


--2.完整备份+日志备份
Create Database Back

Use Back


Create Table Test
(
        Col Varchar(100)
)

Insert Into Test Values('完整备份')
Backup Database Back To Disk='C:\Back.bak' With Init

Insert Into Test Values('日志备份1')
Backup Log Back To Disk='C:\Back_Log1.bak' With Init

Insert Into Test Values('日志备份2')
Backup Log Back To Disk='C:\Back_Log2.bak' With Init

Use master

--还原数据库1
Restore Database Back From Disk='C:\Back.bak' With Recovery,REPLACE
Select * From Back.dbo.Test

--还原数据库1
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Log Back From Disk='C:\Back_Log1.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库2
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Log Back From Disk='C:\Back_Log1.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log2.bak' With Recovery
Select * From Back.dbo.Test

/*
--还原数据库3
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Log Back From Disk='C:\Back_Log1.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log2.bak' With Recovery,Stopat='2015-07-04 10:59:43'
Select * From Back.dbo.Test
*/

/*
如果数据库显示正在还原
Restore Database Back With Recovery
*/

--删除备份文件
EXECUTE master.dbo.xp_delete_file 0,N'C:',N'bak',N'2015-07-05'

--删除数据库
Drop DataBase Back

--3.完整备份+差异备份+日志备份
Create Database Back

Use Back

Create Table Test
(
        Col Varchar(100)
)

Insert Into Test Values('完整备份')
Backup Database Back To Disk='C:\Back.bak'

Insert Into Test Values('日志备份1')
Backup Log Back To Disk='C:\Back_Log1.bak'

Insert Into Test Values('日志备份2')
Backup Log Back To Disk='C:\Back_Log2.bak'


Insert Into Test Values('差异备份1')
Backup Database Back To Disk='C:\Chay1.bak' With DIFFERENTIAL

Insert Into Test Values('日志备份3')
Backup Log Back To Disk='C:\Back_Log3.bak'

Insert Into Test Values('日志备份4')
Backup Log Back To Disk='C:\Back_Log4.bak'

Insert Into Test Values('差异备份2')
Backup Database Back To Disk='C:\Chay2.bak' With DIFFERENTIAL

Insert Into Test Values('日志备份5')
Backup Log Back To Disk='C:\Back_Log5.bak'

Insert Into Test Values('日志备份6')
Backup Log Back To Disk='C:\Back_Log6.bak'

Use master

--还原数据库1
Restore Database Back From Disk='C:\Back.bak' With Recovery,REPLACE
Select * From Back.dbo.Test

--还原数据库2
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Log Back From Disk='C:\Back_Log1.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库3
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Log Back From Disk='C:\Back_Log1.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log2.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库4
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Database Back From Disk='C:\Chay1.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库5
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Database Back From Disk='C:\Chay1.bak' With noRecovery
Restore Log Back From Disk='C:\Back_Log3.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库6
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Database Back From Disk='C:\Chay1.bak' With noRecovery
Restore Log Back From Disk='C:\Back_Log3.bak' With noRecovery
Restore Log Back From Disk='C:\Back_Log4.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库7
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Database Back From Disk='C:\Chay2.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库8
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Database Back From Disk='C:\Chay2.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log5.bak' With Recovery
Select * From Back.dbo.Test

--还原数据库9
Restore Database Back From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Database Back From Disk='C:\Chay2.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log5.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log6.bak' With Recovery
Select * From Back.dbo.Test

--删除备份文件
EXECUTE master.dbo.xp_delete_file 0,N'C:',N'bak',N'2015-07-05'

--删除数据库
Drop DataBase Back

--4.完整备份+日志备份(按页面还原数据库)
Create Database Back

Use Back

Create Table Test
(
        Col Varchar(100)
)

Insert Into Test Values('完整备份')
Backup Database Back To Disk='C:\Back.bak'

Insert Into Test Values('日志备份1')
Backup Log Back To Disk='C:\Back_Log1.bak'

Insert Into Test Values('日志备份2')
Backup Log Back To Disk='C:\Back_Log2.bak'

--得到Test表的数据页
Select * from msdb.dbo.suspect_pages
DBCC TRACEON (3604,-1)
DBCC IND([back], 'Test', -1)  
DBCC PAGE([back], 1, 22,3)  
DBCC TRACEOFF (3604,-1)


Use master

--还原数据库2
Restore Database Back page='1:22' From Disk='C:\Back.bak' With NoRecovery,REPLACE
Restore Log Back From Disk='C:\Back_Log1.bak' With NoRecovery
Restore Log Back From Disk='C:\Back_Log2.bak' With NoRecovery
Backup Log Back To Disk='C:\Back_Log3.bak'
Restore Log Back From Disk='C:\Back_Log3.bak' With Recovery
select * from back.dbo.Test

--删除备份文件
EXECUTE master.dbo.xp_delete_file 0,N'C:',N'bak',N'2015-07-05'

--删除数据库
Drop DataBase Back

--还原时间点Stopat根据实际操作而定

yanpinhui

好复杂的样子

shenglanlt

看不懂，不好意思。

112586036

学习学习。。。。。。。。