Category Archives: Backup

Location of SQL Binaries can flip out Bare Metal backups

Posted on by

Bare Metal recovery allows system administrators to recover a system from failure within a short period of time and also manage system state backups in a more efficient manner. However, there is a slight catch here when you have SQL Sever binaries installed in a non-default location.

I recently worked on an issue where Windows Backup Admin was listing a non-system drive as a critical component when trying to perform a bare-metal backup using the following command:

wbadmin start backup -allcritical -backupTarget:<any existing drive name>:

When I looked into the contents of the drive, I found that the drive only had SQL Server database files and binaries on it. This is the output that I got:

wbadmin start backup -allcritical -backupTarget:S:
wbadmin 1.0 – Backup command-line tool
(C) Copyright 2004 Microsoft Corp.

Retrieving volume information…
This will back up volume OS(C:),New Disk(D:),New Disk(E:) to S:.
Do you want to start the backup operation?
[Y] Yes [N] No N

The operation ended before completion.

I then decided to use DiskShadow to show me all the critical components marked by the VSS Writers. I got the following output:

WRITER “System Writer”:

– Volumes affected by this component:

– \\?\Volume{2407f815-f9c4-11df-aef7-806e6f6e6963}\ [C:\]
– \\?\Volume{22c3ff31-f965-11df-a20b-00155df1c42a}\ [D:\]
– \\?\Volume{0342683d-f96a-11df-8a1f-00155df1c42a}\ [E:\]

This is because the SQL Server program binaries were installed on the D: and E: drives. And these are the paths that show up as critical as per the System Writer:

– d:\program files\microsoft sql server\100\dts\binn
– d:\program files\microsoft sql server\msas10.mssqlserver\olap\bin
– d:\program files\microsoft sql server\msrs10.mssqlserver\reporting services\reportserver\bin
– d:\program files\microsoft sql server\mssql10.mssqlserver\mssql\binn
– e:\program files\microsoft sql server\msas10.inst01\olap\bin
– e:\program files\microsoft sql server\msrs10.inst01\reporting services\reportserver\bin
– e:\program files\microsoft sql server\mssql10.inst01\mssql\binn

DiskShadow command used: list writers detailed

From http://msdn.microsoft.com/en-us/library/aa384630(VS.85).aspx

A volume is a critical volume if it contains system state information. The boot and system volumes are included automatically. The requester must include all volumes that contain system-critical components reported by writers, such as the volumes that contain the Active Directory. System-critical components are marked as “not selectable for backup.” In VSS, “not selectable” means “not optional.” Thus, the requester is required to back them up as part of system state.

Based on the above description, it is clear that I cannot perform a Bare Metal backup without including D: and E: drives.

Note: The above is just an example. There might be other system-critical components which are marked as not selectable for backup by other VSS Writers. VSS works on Volume Shadow concept. So, if a particular drive in a volume is marked as critical, then the entire volume would need to be backed up.

As I mentioned earlier, for system critical components, the “Is selectable: FALSE” is set (VSS_CF_NOT_SYSTEM_STATE flag is set for non-system components). In the above example, the drive on which the SQL binaries exist is marked as critical by the System Writer.

How to convert a LiteSpeed backup into a native SQL backup

Posted on by

I know all you folks out there are probably more proficient at playing around with LiteSpeed backups than I am. Recently, I had to play around with LiteSpeed backups on a regular basis while troubleshooting a particular case for a customer. I knew that there was a utility called Extractor for LiteSpeed which can convert the LiteSpeed backups into native SQL backups. 

Parameters: 

-F|–BackupFile {path}        Path of the backup or restore file devices 

-N|–BackupIndex  {n}       File number (If multiple backups appended within BackupFile(s))

-E|–MTFFile {path}            Path of the Microsoft Tape Format file(s) to be written.  Specify either one or the correct number of files.  If only one file is named, set will be created with index suffixes. If no files listed, Extractor will print number of MTF files to be written.

-K|–Key {key}                 String used to decrypt database backup file(s) Only required if backup is encrypted.
-I|–Overwrite                   Overwrite MTFFile(s). Equivalent of INIT in native SQLServer backups

-L|–LogLevel {n}              Logging option

Example: 

Extractor.exe -FC:\LiteSpeedTran.BKP -EC:\NativeBackup.BKP -N1  

NOTE: To use Extractor utility, you do not need LiteSpeed to be installed on the box. (But the licensing policies and EULA still apply if you are using Extractor) 

How to get backup header information for LiteSpeed backups 

If you have LiteSpeed installed on your box, then you can executed the following XSP command to get a RESTORE HEADERONLY output for a LiteSpeed backup file. This will give a similar output that the T-SQL command RESTORE HEADERONLY provides: 


exec master.dbo.xp_restore_headeronly @filename =  'C:\LiteSpeedTran.BKP'

Hope this information is helpful for individuals whose working knowledge is at not an EXPERT level with this product. I am a LiteSpeed noob!

Blog post update

Posted on by

I published a new post on SQL Server FAQ on how incorrect buffercount values used or the lack of using the buffercount value can lead to Out of Memory errors in SQL Server.

Read about it here:

Incorrect BufferCount data transfer option can lead to OOM condition

Is my RESTORE operation complete?

Posted on by

When you perform a database restore operation using Management Studio Object Explorer, you might be baffled as to why the confirmation pop-up window doesn’t pop-up saying that the database restore operation is complete.

image

This is because the RESTORE operation in SQL Server can be divided into two stages if you are having a high level discussion and don’t want to get into the intricacies:

1. Copy the information from the backup media and write them into the physical files on the Windows File System

2. Perform recovery on the database to bring it online (provided in the OPTIONS tab you have selected the first radio button under the Recovery State)

The highlighted counter shows you the progress of the first stage only. The second stage doesn’t have a progress counter associated with it. If you look at the percent_complete column value in the sys.dm_exec_requests DMV.

So, if your database restore screen is showing 100% completed status and the SQL Server Errorlog is not reporting the following for the database being restored:

2010-03-13 02:56:22.650 spid61       Starting up database ‘distribution_new’.
2010-03-13 02:56:23.140 Backup       Database was restored: Database: distribution_new, creation date(time): 2009/08/04(01:37:56), first LSN: 865:804:70, last LSN: 865:833:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\distribution.bak’}). Informational message. No user action required.

Then you are phase 2 of the restore provided that session performing the database restore is not experiencing blocking.

Now for the most interesting part, Why is this happening i.e. the long recovery? One of the reasons is documented here.

Why is VSS complaining about SQL Data files

Posted on by

I recently came across a question on why a NTBackup of a Text File on a drive reported the following error in the application event logs:

Event Type:    Error
Event Source:    VSS
Event Category:    None
Event ID:    6005
Description:
Sqllib error: Database %1 is stored on multiple volumes, only some of which are being shadowed.

The interesting part is that the drive that stores the Text file that I am backing up also has a LDF file for a database. The MDF file of the same database resides on another drive. The intelligent SQL Writer was able to find out that only one file for the database in question is on this drive and the other file(s) is not present on the same drive. Hence, it reported the error causing confusion in our minds. A red cross in any log file is a BAD thing!! Not in this case. 🙂

I have already blogged about the behavior of NTBackup/VSS when a Snapshot is created in a previous post.

The conclusion is that if you have database files residing on multiple drives and you are using VSS to backup non-SQL database files on only one of these drives, then you can ignore the above error (if that is the only error). This is yet again another of the infamous by-design behaviors! 🙂