SQL备份与还原(二)
本文介绍了SQL备份与还原的实战用例.
我们日常使用的MOSS进行公司部门站点的信息管理与收发,后台使用MS SQL2005.
我计划采用完全备份,差异备份和日志备份来实现MOSS数据的日常备份和管理.
计划如下：
采用完全恢复模型。
备份设备：disk为主，可以用本地磁盘或网络磁盘备份。
备份方法：完全备份+差异备份+事务日志备份。
备份日程：完全备份每天进行一次，差异备份每四个小时一次，事务日志备份每三十分钟一次。备份网络磁盘仅保留近一周的备份文件。
备份文件命名：
完全备份：数据库实例名称+“_full.bak”
差异备份：数据库实例名称+“_diff_”+hh+”.b”
事务日志备份：数据库实例名称+“_log_+hhmm
备份路径：网络磁盘\YYMMDD\
下文以eip数据库实例为例来说明操作步骤。
每天检查并删除过时的备份文件
1 --Function:full backup database
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 --2 Initialize variables
7 set @DbName = 'wsseip' --custom
8 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\'
9 set @FileName = @DbPath +@DbName+ '_full.bak'
10 --3 Net connect and create file
11 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
12 EXEC master.dbo.xp_create_subdir @DbPath
13 --4 Create backup
14 backup database EIP to disk=@FileName with init
15 go
16
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 --2 Initialize variables
7 set @DbName = 'wsseip' --custom
8 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\'
9 set @FileName = @DbPath +@DbName+ '_full.bak'
10 --3 Net connect and create file
11 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
12 EXEC master.dbo.xp_create_subdir @DbPath
13 --4 Create backup
14 backup database EIP to disk=@FileName with init
15 go
16
每天做一次完全备份
1 --Function:del backup database
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 declare @CmdShell varchar(500)
7 --2 Initialize variables
8 set @DbName = 'wsseip' --custom
9 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),dateadd(day,-5,getdate()),112)
10 set @CmdShell ='rd /S /Q ' + @DbPath
11 --3 Net connect and create file
12 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
13 --4 Del log backup
14 exec master..xp_cmdshell @CmdShell --删除5天前的备份，也就是只保留5个最新备份
15 go
16
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 declare @CmdShell varchar(500)
7 --2 Initialize variables
8 set @DbName = 'wsseip' --custom
9 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),dateadd(day,-5,getdate()),112)
10 set @CmdShell ='rd /S /Q ' + @DbPath
11 --3 Net connect and create file
12 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
13 --4 Del log backup
14 exec master..xp_cmdshell @CmdShell --删除5天前的备份，也就是只保留5个最新备份
15 go
16
每4个小时做一次差异备份
1 --Function:diff backup database
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 --2 Initialize variables
7 set @DbName = 'wsseip' --custom
8 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\'
9 set @FileName = @DbPath +@DbName+ '_diff_'+cast(datepart(hour,getdate()) as varchar)+'.bak'
10 --3 Net connect and create file
11 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
12 EXEC master.dbo.xp_create_subdir @DbPath
13 --4 Create diff backup
14 backup database EIP to disk=@FileName with differential --差异备份数据库
15 go
16
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 --2 Initialize variables
7 set @DbName = 'wsseip' --custom
8 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\'
9 set @FileName = @DbPath +@DbName+ '_diff_'+cast(datepart(hour,getdate()) as varchar)+'.bak'
10 --3 Net connect and create file
11 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
12 EXEC master.dbo.xp_create_subdir @DbPath
13 --4 Create diff backup
14 backup database EIP to disk=@FileName with differential --差异备份数据库
15 go
16
每30分钟做一次事物日志备份
1 --Function:diff backup database
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 --2 Initialize variables
7 set @DbName = 'wsseip' --custom
8 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\'
9 set @FileName = @DbPath +@DbName+ '_log_'+cast(datepart(hour,getdate()) as varchar)+cast(datepart(minute,getdate()) as varchar)
10 --3 Net connect and create file
11 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
12 EXEC master.dbo.xp_create_subdir @DbPath
13 --4 Create log backup
14 ALTER DATABASE eip SET RECOVERY FULL
15 backup log eip to disk=@FileName --日志备份数据库
16 go
17
2 --1 Variable declaration
3 declare @DbPath varchar(500)
4 declare @DbName sysname
5 declare @FileName varchar(500)
6 --2 Initialize variables
7 set @DbName = 'wsseip' --custom
8 set @DbPath='\\172.22.8.121\D$\dbbackup\'+@DbName+'\'+convert(varchar(10),getdate(),112) +'\'
9 set @FileName = @DbPath +@DbName+ '_log_'+cast(datepart(hour,getdate()) as varchar)+cast(datepart(minute,getdate()) as varchar)
10 --3 Net connect and create file
11 exec master.dbo.xp_cmdshell 'net use \\172.22.8.121\D$\dbbackup'
12 EXEC master.dbo.xp_create_subdir @DbPath
13 --4 Create log backup
14 ALTER DATABASE eip SET RECOVERY FULL
15 backup log eip to disk=@FileName --日志备份数据库
16 go
17
