針對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 <Database name>

FROM TAPE = '\\.\tape0'

WITH

NOREWIND,

NOUNLOAD,

STATS = 1

GO