针对SQL的数据库操作脚本范例
--执行语句:restore headeronly from tapedump1
在返回的结果集中,
通过:
Position 备份集在卷中的位置,通过它来区分每次备份(备份号)
BackupStartDate 备份操作的开始日期和时间。
BackupFinishDate 备份操作的完成日期和时间。
这两个字段,可以确定这是那一次的备份
然后就在恢复的时候,就可以用类似下面的语句来恢复指定时间做的备份:
restore database pubs
from tapedump1 with file=N --n就是上面查询到的备份号
--完整备份
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Full_20070908.bak'
--差异备份
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Diff_20070908.bak'
With Differential
--日志备份，默认截断日志
Backup Log NorthwindCS
To disk='G:\Backup\NorthwindCS_Log_20070908.bak'
--日志备份，不截断日志
Backup Log NorthwindCS
To disk='G:\Backup\NorthwindCS_Log_20070908.bak'
With No_Truncate
--截断日志不保留
Backup Log NorthwindCS
With No_Log
--或者
Backup Log NorthwindCS
With Truncate_Only
--截断之后日志文件不会变小
--有必要可以进行收缩
--文件备份
Exec Sp_Helpdb NorthwindCS --查看数据文件
Backup Database NorthwindCS
File='NorthwindCS' --数据文件的逻辑名
To disk='G:\Backup\NorthwindCS_File_20070908.bak'
--文件组备份
Exec Sp_Helpdb NorthwindCS --查看数据文件
Backup Database NorthwindCS
FileGroup='Primary' --数据文件的逻辑名
To disk='G:\Backup\NorthwindCS_FileGroup_20070908.bak'
With init
--分割备份到多个目标
--恢复的时候不允许丢失任何一个目标
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Full_1.bak'
,disk='G:\Backup\NorthwindCS_Full_2.bak'
--镜像备份
--每个目标都是相同的
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
Mirror
To disk='G:\Backup\NorthwindCS_Mirror_2.bak'
With Format --第一次做镜像备份的时候格式化目标
--镜像备份到本地和远程
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
Mirror
To disk='\\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak'
With Format
--每天生成一个备份文件
Declare @Path Nvarchar(2000)
Set @Path ='G:\Backup\NorthwindCS_Full_'
+Convert(Nvarchar,Getdate(),112)+'.bak'
Backup Database NorthwindCS
To disk=@Path
--从NoRecovery或者
--Standby模式恢复数据库为可用
Restore Database NorthwindCS_Bak
With Recovery
--查看目标备份中的备份集
Restore HeaderOnly
From Disk ='G:\Backup\NorthwindCS_Full_20070908.bak'
--查看目标备份的第一个备份集的信息
Restore FileListOnly
From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'
With File=1
--查看目标备份的卷标
Restore LabelOnly
From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'
--备份设置密码保护备份
Backup Database NorthwindCS
To disk='G:\Backup\NorthwindCS_Full_20070908.bak'
With Password = '123',init
Restore Database NorthwindCS
From disk='G:\Backup\NorthwindCS_Full_20070908.bak'
With Password = '123'
RESTORE HEADERONLY FROMTape =' \\.\tape0' WITHNOUNLOAD
--- this tries to fetch the header information from
the tape for a specified amount of time.
Step 4: Obtain the list of files present in the backup set/media.
RESTORE FILELISTONLY FROM Tape =' \\.\tape0'WITHNOUNLOAD,FILE =(file_number)
Step 5: Perform the actual restore operation.
RESTORE DATABASE FROMDISK = Tape =' \\.\tape0'
WITHFILE =(file_number), NORECOVERY,NOUNLOAD,STATS = (percentage)
In GUI this fails for large databases due to the fact that certain operations
like the below sequence have a built-in timeout of 20 seconds.
Right click on a database >> Tasks >> Restore >> Database >> From Device >>
Click on button >> Backup Media = TAPE >> Add >> Select Backup tape >> OK >> OK.
The timeout will happen on the "Specify Backup" dialog.
This timeout is to prevent these dialog boxes from hanging forever when
there is no tape present in the drive.
If you run the "RESTORE HEADERONLY" command from Tsql, you can see that it
takes several minutes to complete which is much longer than the 20 second timeout for GUI.
To work around the issue, we can need to use T-SQL from Management Studio.
For the restore:
RESTORE DATABASE
FROM TAPE = '\\.\tape0'
WITH
NOREWIND,
NOUNLOAD,
STATS = 1
GO
页:
[1]