针对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 FROM Tape = ' \\.\tape0' WITH NOUNLOAD

--- 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' WITH NOUNLOAD, FILE =(file_number)

Step 5: Perform the actual restore operation.

RESTORE DATABASE [Database name] FROM DISK = Tape = ' \\.\tape0'

WITH FILE =(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