針對(duì)SQL的數(shù)據(jù)庫(kù)操作腳本范例

--執(zhí)行語(yǔ)句: 

restore   headeronly   from   tapedump1 

在返回的結(jié)果集中, 

通過(guò): 

Position              備份集在卷中的位置,通過(guò)它來(lái)區(qū)分每次備份(備份號(hào)) 

BackupStartDate       備份操作的開(kāi)始日期和時(shí)間。 

BackupFinishDate     備份操作的完成日期和時(shí)間。 

這兩個(gè)字段,可以確定這是那一次的備份 

然后就在恢復(fù)的時(shí)候,就可以用類似下面的語(yǔ)句來(lái)恢復(fù)指定時(shí)間做的備份: 

restore   database   pubs 

 from   tapedump1   with   file=N     --n就是上面查詢到的備份號(hào) 

--完整備份

Backup Database NorthwindCS

To disk='G:\Backup\NorthwindCS_Full_20070908.bak'

--差異備份

Backup Database NorthwindCS

To disk='G:\Backup\NorthwindCS_Diff_20070908.bak'

With Differential

--日志備份，默認(rèn)截?cái)嗳罩?/p>

Backup Log NorthwindCS

To disk='G:\Backup\NorthwindCS_Log_20070908.bak'

--日志備份，不截?cái)嗳罩?/p>

Backup Log NorthwindCS

To disk='G:\Backup\NorthwindCS_Log_20070908.bak'

With No_Truncate

--截?cái)嗳罩静槐Ａ?/p>

Backup Log NorthwindCS

With No_Log

--或者

Backup Log NorthwindCS

With Truncate_Only

--截?cái)嘀笕罩疚募粫?huì)變小

--有必要可以進(jìn)行收縮

--文件備份

Exec Sp_Helpdb NorthwindCS --查看數(shù)據(jù)文件

Backup Database NorthwindCS

File='NorthwindCS'   --數(shù)據(jù)文件的邏輯名

To disk='G:\Backup\NorthwindCS_File_20070908.bak'

--文件組備份

Exec Sp_Helpdb NorthwindCS --查看數(shù)據(jù)文件

Backup Database NorthwindCS

FileGroup='Primary'   --數(shù)據(jù)文件的邏輯名

To disk='G:\Backup\NorthwindCS_FileGroup_20070908.bak'

With init

--分割備份到多個(gè)目標(biāo)

--恢復(fù)的時(shí)候不允許丟失任何一個(gè)目標(biāo)

Backup Database NorthwindCS

To disk='G:\Backup\NorthwindCS_Full_1.bak'

     ,disk='G:\Backup\NorthwindCS_Full_2.bak'

--鏡像備份

--每個(gè)目標(biāo)都是相同的

Backup Database NorthwindCS

To disk='G:\Backup\NorthwindCS_Mirror_1.bak'

Mirror

To disk='G:\Backup\NorthwindCS_Mirror_2.bak'

With Format --第一次做鏡像備份的時(shí)候格式化目標(biāo)

--鏡像備份到本地和遠(yuǎn)程

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

--每天生成一個(gè)備份文件

Declare @Path Nvarchar(2000)

Set @Path ='G:\Backup\NorthwindCS_Full_'

+Convert(Nvarchar,Getdate(),112)+'.bak'

Backup Database NorthwindCS

To disk=@Path

--從NoRecovery或者

--Standby模式恢復(fù)數(shù)據(jù)庫(kù)為可用

Restore Database NorthwindCS_Bak

With Recovery

--查看目標(biāo)備份中的備份集

Restore HeaderOnly

From Disk ='G:\Backup\NorthwindCS_Full_20070908.bak'

--查看目標(biāo)備份的第一個(gè)備份集的信息

Restore FileListOnly

From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'

With File=1

--查看目標(biāo)備份的卷標(biāo)

Restore LabelOnly

From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'

--備份設(shè)置密碼保護(hù)備份

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