Restore filelistonly & headeronly & labelonly & verifyonly

Instructions

-- List backup content

Restore filelistonly from disk = 'C: \ northwind \ 2012_46 \ northwind_20121115_log.bak'

 

-- Restore headeronly searches all backup headers of all backup sets on a specific backup device. The result of executing restore headeronly is a result set.

Restore headeronly from disk = 'C: \ northwind \ 2012_46 \ northwind_20121116_155022_full.bak'

 

-- Restore labelonly returns a result set consisting of information about the backup media identified by the specified backup device.

Restore labelonly from disk = 'C: \ northwind \ 2012_46 \ northwind_20121116_154237_full.bak'

-- Restore verifyonly verifies the backup but does not restore the backup. Check whether the backup set is complete and whether all volumes are readable.
-- However, restore verifyonly does not attempt to verify the data structure in the backup volume. If the backup is valid, Microsoft SQL Server 2000 returns the message "This backup set is valid ".

Restore verifyonly from disk = 'C: \ northwind \ 2012_46 \ northwind_20121116_140924_log.bak'

 

 

Demo

-- List all database files

Select * From sysaltfiles s

 

-- Modify the file name
-- Link: http://blog.erp100.com/home.php? MoD = Space & uid = 101227 & Do = Blog & id = 13554900

Alter database northwind Modify file (name = northwind_log, newname = northwind_log _)

 

-- Displays the number of rows, the disk space reserved, and the disk space used by the tables in the current database, or the disk space reserved and used by the entire database.

Exec sp_spaceused [[@ objname =] 'objname'] [, [@ updateusage =] 'updateusage']

 

-- Add a temp table and insert the results of restore filelistonly.

Create Table # T (LN nvarchar (128), PN nvarchar (260), TP char (1), FGN nvarchar (128), SZ numeric (20, 0 ), msz numeric (20, 0)
insert into # T exec ('Restore filelistonly from disk ='' C: \ northwind \ 2012_47 \ northwind_20121119_193715_full.bak ''')
select * from # T
drop table # T