Know the type of bakup and database without restoring it to your database

I was killing out time with one of my SQL DBA community, while juggling various queries, a particular question squeeze my interest lime, the question was

“How to find which database and type of bakup it is without restoring the bak file”  

The answer to this is very simple, lets try to see with an example, I have a bakup file at “D:\SQL Server\myBakupFile.bak” lets see how we can get the detail of bakup type and database name without restoring it.

Step1: Verify whether the bakup file is valid for restore.

RESTORE VERIFYONLY FROM

disk = 'D:\SQL Server\myBakupFile.bak'

The backup set on file 1 is valid.

Step2: Get the header info of the bak file.

RESTORE HEADERONLY FROM 

disk = 'D:\SQL Server\myBakupFile.bak'

BackupName                  DatabaseName 

--------------------------  ---------------

TestDB-Full Database Backup TestDB       

(1 row(s) affected)

That’s all!!! :D pretty simple

Conclusion: Use HEADERONLY option of Restore to get the bak file information without actually restoring the bakup file to the server.

Pretty simple you are, anyone who came closer to you, fall in love with you, SQL Server :)

The media set has 2 media families but only 1 are provided.

Msg 3132, Level 16, State 1, Line 1

The media set has 2 media families but only 1 are provided. All members must be provided.

Msg 3013, Level 16, State 1, Line 1

VERIFY DATABASE is terminating abnormally.

This is very common error, which generally comes with Restore and Backup. To dig into the problem lets stimulate this error.

Here I have a bakup file stored in D:\ drive, I want to use RESOTE common to verify the bak file.

RESTORE VERIFYONLY FROM

Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

Msg 3132, Level 16, State 1, Line 1

The media set has 2 media families but only 1 are provided. All members must be provided.

Msg 3013, Level 16, State 1, Line 1

VERIFY DATABASE is terminating abnormally.

As I hit F5, I got this error, the error clearly specify, that the backup of the database wrote its data to two files, not one.   Therefore, you need both files in order to do a successful restore.

To locate the missing part you can use the following query.

SELECT b.physical_device_name

FROM msdb..backupmediaset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id

WHERE a.media_uuid = '<Media_UUID>'

Now Media_UUID you can get from

RESTORE LABELONLY FROM

disk = 'D:\SQL Server\myBakup.bak'

MediaName MediaSetId                          

----------------------------------------------

NULL      A335C850-4788-4A6B-BAE0-984AF5F4E6CC

Now here we have MediaSetId, we can use this mediaSetId to the above query to find the missing bak file location.

SELECT b.physical_device_name

FROM msdb..backupmediaset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id

WHERE a.media_uuid = 'A335C850-4788-4A6B-BAE0-984AF5F4E6CC''

physical_device_name

--------------------------------------------------------------------------------------

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak

D:\SQL Server\myBakup.bak

So here we found that the bakup was splited into two different file.  Now we can use both the RESTORE Command to use both the

RESTORE VERIFYONLY FROM

disk = 'D:\SQL Server\myBakup.bak',

Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

The backup set on file 1 is valid.

Conclusion: You can msdb..backupmediaset and msdb..backupmediafamily table to find the missing file location.

The only gears that loom in coders dream are their love to code. Love you dear SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share

Backup and Restore with Split and Mirror

Backup and Restore your SQL Database

You can take the backup of your database by few line of t-sql like the one below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

WITH FORMAT : means if there exist any backup with the name u defined in the query then it will delete/format that backup.

How to Split your database backup

There are situation where you want to split your database backup fine and want one file to stored in say d drive and another in e drive. You can do this very easily. Look at the code below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

Here the backup is not going to stored in two location instead the whole backup get divided into two parts. One part get stored in D:/ and another will in D:/try

What is mirror backup and how can you take this?

Mirror backup is nothing but taking the same backup in two different locations.

BACKUP DATABASE [PracticeDatabase] TO
DISK = N'D:\PracticeDatabase.bak'
MIRROR TO DISK = N'D:\try\PracticeDatabase.bak'
WITH FORMAT
NAME = N'PracticeDatabase-Full Database Backup'

How to restore database

Restoring a database is very simple you just need to write few line of sql statement specifying the database name where you want to restore your database and the path where the backup file is lying.

RESTORE DATABASE TempPractice
FROM DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

Here we are restoring our database backup file at TempPractice database. And the backup file is lying at C:\Backup\SingleFile\.

How to restore Split database

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [TempPractice]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Here the database AdventureWork split into three part AdventureWork 1, AdventureWork 2, AdventureWork 3.  So to restore such split database you need to specify each and every split backup file in Disk option.    

Post Reference: Vikram Aristocratic Elfin Share