LiteSpeed’s Backup Analyzer can do more than just find the right backup compression and encryption settings like I discussed in an earlier LiteSpeed video tutorial.  It can also help pinpoint your SQL Server’s backup bottleneck, showing why you can’t back up your databases faster.

Here’s a screenshot of the Backup Analyzer’s report:

LiteSpeed Backup Analyzer

When you’re looking for the backup bottleneck, check out these columns:

• Read Speed – indicates how fast your data files can pull the database off disk and give it to the CPU.  This is the first step in the backup process – getting the uncompressed data in.  This number is dictated by the speed of the storage subsystem where your SQL Server data files live, and by the connection between your server and the data files.
• Throughput – indicates how fast your CPUs can process the data.  The more compression and encryption performed, the less data can move through here.  To improve this performance, use less compression or a weaker encryption method.  (Generally, though, this isn’t a bottleneck except on very old servers with slow CPUs.)
• Write Speed – indicates how fast your storage subsystem can write out the compressed data.  This is the last step in the backup process.  To improve this number, you can use faster drive arrays or stripe the data between multiple drives, and the Backup Analyzer can be used to test those scenarios too.

In my screenshot above, my lab server’s data drives are connected via iSCSI.  Notice that my read speeds are consistent across all of my tests.  That’s because I just flat out can’t read the data any faster than I’m reading it.  The only solution to make this server faster is to upgrade the connection between the data file drives and the server itself.

However, since I know I just can’t read that fast, that means I can use as much encryption and compression as I want without affecting performance.  In this situation, I might retest with stronger encryption methods and higher compression levels.  Even though I’m read-constrained, that doesn’t mean I can’t get benefits from using LiteSpeed’s compression.

If you’re confused about the output from Backup Analyzer, feel free to contact Quest support or leave us a comment below with the output from your tests.

Are you the kind of database administrator who likes to get under the hood and tweak settings for CPU affinity masking, throttling, transfer sizes, and more?  This video is for you.

Quest LiteSpeed for SQL Server has advanced configuration options that will let you finely tune how much power LiteSpeed uses, and on which processors.  Learn how in this video by Brent Ozar:

Quest LiteSpeed for SQL Server can read transaction log files (LDFs), transaction backups (TRNs) and the online transaction log – including transactions that haven’t even been backed up yet.  Learn how in this video by Brent Ozar:

Toad for SQL Server also has this transaction log reader built-in, too!

Less than 24 hours after posting a list of things I am thankful for, where I expressed Thanks for not having to restore master, I found myself awake at 3AM in order to restore master after we had a server lose its data drive. Before I got started I decided to check on the definition of irony. Yep, it fit.

OK, show of hands: How many people out there use LiteSpeed for your database backups? Now, for those with their hands raised, put your hands down before someone thinks you are crazy for raising your hand while sitting at your computer. Next question: are you also using LiteSpeed to back up your system databases? Final question: do you know how to restore master from a LiteSpeed backup?

No?

Well, how hard can it be? And I am not talking about rebuilding master, but doing a restore of master. So, you startup the instance in single user mode (using -m, which stands for maintenance I believe), and do the restore. What could be easier? Well, lots of things, actually. Turns out that unless you have your process rehearsed you could be fumbling for answers at 3AM. Not good times.

I decided to put together the details on how to get the restore done. Since we rely heavily on support from people on the other side of the Earth (that I have never met) it is vital that I can document as much as possible. And I decided to share the details with all of you as well. You’re welcome.

Since only the data drive was affected in this event all I needed to do was get SQL up and running and start doing restores of the system databases, starting with master. A trick I learned a while back is to copy the master, msdb, and model .mdf and .ldf files from another server to the server we need to recover. We have gone through great pains here to make certain we locate these .mdf and .ldf files in the same path for every server. So, when we start MSSQL, the instance comes alive except for the user databases which are all marked suspect.

But with the instance alive you are now able to get started on recovering master, the important first step. The general outline for this process is as follows:

1. Shut down the instance completely, by whatever means you prefer.
2. Open a command prompt and navigate to the directory that contains the sqlservr.exe executable. This is most likely the \Binn directory and you could run the following: cd \Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\Binn
3. Next, you will run the following command: sqlservr.exe -m, which will start the instance. Leave this command window open.
4. Open a second command window and navigate to directory that contains the SQLLitespeed.exe executable. Most likely this is found by running: cd \Program Files\Quest Software\LiteSpeed\SQL Server\Engine (NOTE: do not confuse the SQLLitespeed.exe executable with the Litespeed.exe executable, as I did several times)
5. Next, run the following command, substituting in the file path you want to use for the restore: sqllitespeed.exe -Rdatabase -T -Dmaster -F “[backup file to restore from]“
6. Once complete you will be returned to a prompt in the current command window and the original command window should also have a prompt as the instance will have been shut down.
7. You can now start the instance as a service as you would do normally.

If you are looking to test that this works then I suggest you create a new login right before you shut down the instance in step one. You can create one real quick by running:

USE [master]
GO
CREATE LOGIN [master_restore_test]
WITH PASSWORD=N’test’,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

This will create a login, which you can verify quickly, then you can do the restore of master and verify that the login has disappeared.

I put together these details to share with my team and asked that they all took five minutes to practice restoring master form a Litespeed backup. I hope that none of us will ever need to use these instructions, but if the time comes that such actions are needed I feel better knowing that we have rehearsed these steps.

Believe me, it is better than trying to practice when you are half asleep. And knowing you can recover quickly when disaster strikes gives you confidence to take action when an event happens.

Did you know Quest LiteSpeed for SQL Server has dozens of compression and encryption options?  Not to mention which backup drives you want to use and whether to stripe or mirror them – your configuration choices can be bewildering!

LiteSpeed’s Backup Analyzer does the hard work for you by testing all the different possibilities and combinations to find the fastest and smallest backups.  Brent Ozar shows how to use it in this five-minute video.

Did you know you can query backup files without restoring them? Just open up the LiteSpeed console, point to a native or LiteSpeed backup file, and start your SELECT statements.  Brent Ozar shows you how in this five-minute tutorial video:

Quest’s free Discovery Wizard tool helps you discover and report on your SQL Server instances.  One of the things it checks is for the installed version of Quest LiteSpeed for SQL Server.  If you need to find out where LiteSpeed is installed, watch this short tutorial video to learn how.

Need to recover just one table?  Or maybe someone dropped a stored procedure?

LiteSpeed’s Object Level Recovery helps you restore individual objects from a SQL Server backup – either native or LiteSpeed.  Brent Ozar shows how in this short video.

LiteSpeed’s built-in wizards make it easy to restore a database.  No matter what combination of full backups, differential backups, and transaction log backups are involved, it’s point-and-click easy.  LiteSpeed even generates the right combination of RESTORE DATABASE scripts.  Brent Ozar shows how in this short video.