Database bakup with PDF extension

Yes, I know you drag your buttock to the edge of your chair, be aware don’t let it fall on ground, they are precious, handle it with care :D. SQL Server does not have problem with the file extension as long as the file is a valid file. So you can name you bakup extension with any type.

But general industry standard says .BAK for full bakup, .DIFF for Differential and .TRN for transactional backup.

Let try out with .PDF extension.

BACKUP DATABASE [TestDB] TO  DISK = N'D:\SQL Server\myDatabaseBakup.pdf'

WITH FORMAT,  MEDIANAME = N'MyTest1',  NAME = N'TestDB-Full Database Backup'

GO

Processed 11432 pages for database 'TestDB', file 'TestDB' on file 1.

Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 1.

BACKUP DATABASE successfully processed 11434 pages in 5.080 seconds (17.582 MB/sec).

Lets now try to restore it.

USE [master]

RESTORE DATABASE [MyTest101] FROM  DISK = N'D:\SQL Server\myDatabaseBakup.pdf'

WITH  FILE = 1,  MOVE N'TestDB' TO N'D:\SQL Server\Log\MyTest101.mdf',  MOVE N'TestDB_log' TO N'D:\SQL Server\Log\MyTest101_log.ldf'

GO

Processed 11432 pages for database 'MyTest101', file 'TestDB' on file 1.

Processed 2 pages for database 'MyTest101', file 'TestDB_log' on file 1.

RESTORE DATABASE successfully processed 11434 pages in 4.236 seconds (21.086 MB/sec).

Conclusion: SQL Server does not have problem with the file extension as long as the file is a valid file.

For coders world is colorful, this is just because of you SQL Server :) Ah! You are evergreen!!!

Post Reference: Vikram Aristocratic Elfin Share

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

Changing the color of status bar depending upon the server you are connected to

I remember last year, when I was sitting with my colleague, I saw something very interesting on his SSMS, when he was connecting to production environment, the status bar changes its color to red and when he was connecting to UAT, it gets converted to green.

I came to my n desk and google it out. I found it really a simple but great feature roll out by SSMS. These are such small feature that really makes our life more meaningful, colorful and simple.

Let me show you how we can change the color of status bar. When you try to connect to any database server, expand login screen by pressing OPTION button.

 Here for the 2014 server “ATOSHI\MSSQLSERVER2014”, we set the color to Red. Lets try to set the color of another server say “ATOSHI\SQLEXPRESS” to green.

 

Now since we have set one server to Red and another one to green, lets see whether it is reflecting on status bar.

First I am connecting to “ATOSHI\SQLEXPRESS” whose status bar we set to green.

 

That’s great, we can see status bar color changes to color Green, lets try to connect to server2014.

 Here we can see status bar color changes to color Red.

Conclusion: You can change the status bar color to the environment you are connected to.

The season of cold bring hotness, when you are with me SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share