More than once I have had the need to recover the native backup file from within the compressed Litespeed backup file. Fortunately Quest provides an extractor utility in order to help with the process. Otherwise, you would be forced to do a restore followed by a native backup. If your database is of a considerable size then the time needed to accomplish that task could be prohibitive; it will always be simpler to do the extraction as opposed to a restore and a backup.

Extraction is very straightforward. First step is to open a command window and navigate to the directory where the utility is located, this is typically the C:\Program Files\Quest Software\LiteSpeed\SQL Server directory.

Next, issue the following command to extract the native backup files.  Yes, files. In fact you will have one file created for each thread that was used when Litespeed was taking the initial database backup. The command is roughly as follows:

extractor.exe -F [path to backup file taken by litepseed] -E [name of file you want to be created]

So, if you had a backup of AdventureWorks saved to C:\AdWks.bak, and you wanted to extract the native dump from that file and create new files in the same directory, you would run the following command:

extractor.exe -F c:\AdWks.bak -E c:\AdWks_ext.bak

This command results in the creation of multiple files. You can configure a path and filename here if you want to have the files stored somewhere other than the current directory.

OK, so what do you do with these files? Well, you restore them, of course, using either T-SQL or SSMS. One common reason for needing to have a native backup is for those rare times when you need to provide a vendor with a copy and that same vendor does not have a copy of Litespeed. And while the extraction utility makes it easy to provide the necessary files, you have no idea who is on the receiving end of the multiple files. I have lost count of the number of vendors I have sent the extracted files to and received an email back that either says “we don’t know what to do with these files” or “can you send us just one file instead?”

Sure, I can do that as well. Or, I could take a minute to teach you how to do it for yourself. Here goes:

Open up SSMS and all you need to do is point to the files. Just add in each file as shown below, and click OK. Then, select your restore point and click OK again. You should be fine, the database restore should be underway at this point. 

Now, if you prefer T-SQL, the equivalent syntax would be:

RESTORE DATABASE [AdventureWorks]
FROM  DISK = N’C:\AdWks_ext.bak2′,
DISK = N’C:\AdWks_ext.bak1′,
DISK = N’C:\AdWks_ext.bak0′
GO

It’s that simple.

Most DBAs don’t read blogs.

They have “real jobs” that don’t afford them the time to surf the web, improve their training, or meet like-minded SQL Server professionals who want to help. When I talk to them about the power of the community and all this free help that’s available, they’re often completely surprised.

I think it has to do with the lonely nature of the DBA career. We usually stumble into this job by accident. We start as developers or network administrators, and for some odd reason we end up managing a SQL Server because nobody else is doing it. We tinker around with it, learn a lot of lessons the hard way, and struggle finding good training.

How Quest and SQLServerPedia Are Making a Difference

At the beginning of this year, we announced that SQLServerPedia would offer blog syndication.  We knew that a lot of bloggers were writing top-notch material, but they weren’t getting the exposure they deserved.  We wanted to help bloggers get their work to a wider audience.

Now, we’re kicking it up a notch.  Here’s the CPU diagnostics screen inside Quest Spotlight on SQL Server v6, and check out the links at the bottom right:

Quest Spotlight on SQL Server Enterprise

When you’re trying to troubleshoot a complex issue like CPU bottlenecks due to insufficient plan cache reuse, or too many adhoc queries running, you need help.  So when you click on those links….

SQLServerPedia Search Results

You’re introduced to community members, bloggers, wiki authors, and other folks who want to share their knowledge with you.

This is a completely new way that syndication pays off for bloggers. When you cover topics users don’t understand, you can show up on end user screens everywhere.  We’re only including our syndicated blogs in this search.

How Bloggers Can Benefit

In my Syndication FAQ for Bloggers, I talked about some ways you can leverage syndication to bring more readers to your site. These tips include:

• Include links to your other posts. When someone’s reading one of your posts, that’s your chance to bring them deeper into your site. For example, in this very blog post just a couple of lines above, I linked to my own syndication FAQ, and I’m going to do it again in a second.
• Include sample code. If you’re discussing table partitioning, for example, include the scripts to demonstrate what you’re talking about. The more scripts you include, the more likely someone will stumble across your blog entry when they’ve got questions about a particular command.
• Toot your own horn. If you’re a consultant and you happen to specialize in the area you’re blogging about, include a footer on every post with links to contact you for more information. FeedBurner makes this particularly easy.
• Include affiliate links to books. If you’re a big fan of a particular book to dive deeper into the blog post’s subject matter, include an Amazon Affiliate link to buy the book. You get paid 4% of the Amazon purchase, and if you’re an author, this is above and beyond your normal cut of the proceeds.
• Read your web statistics reports. Every now and then, dig into your reports to find out if one of your posts has become popular. If it has, update it to include more links to your other posts, as I discussed in my Buried Treasure Blog Posts article.

To read more tips like this, check out my Syndication FAQ for Bloggers. See how I did that?

Suggested Topics for Maximum Exposure

If you’d like some ideas on topics to write about, here’s a sampling of the keywords used as SQLServerPedia search links:

• Adhoc Queries
• Blocking
• Buffer Cache
• CPU Bottleneck
• Memory
• Performance Counters
• Plan Cache Reuse
• Procedure Cache
• Query Optimization
• Query Plan Hash
• Query Plan Parallel
• Query Plan Sort
• Query Tuning

If you wanted to get more exposure to more readers, you might look for keywords with less competition.  You could hit those links, see what kinds of results they bring back, and figure out how you could write something better.

When writing, keep in mind that SQLServerPedia can’t syndicate posts about third party products – and that includes Quest’s own products. SQLServerPedia has a very firm editorial policy because we focus strictly on things you can do with the native SQL Server tools. Sharp-eyed readers will notice that this particular post isn’t syndicated to SSP, for example, but it IS syndicated somewhere else – and I’ll talk more about that soon!

How Quest is Bringing DBAs Online is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

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.

Stephanie McCulloch is the Chief Schwag Officer for the SQL Server business unit of Quest.  She’s on Twitter as SMcCulloch52, but she hasn’t posted anything yet.  I’m not saying she likes to watch, but back to the subject at hand.

She regularly gives away schwag to webcast viewers, user groups, booth attendees, and so on.  She wants to know what you would like to win.

So in the comments, let us know what your ideal prizes would be in the $50, $100, $250, and $500 price range.  I’m curious to see what you think!

What Should We Give Away? is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.