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!

Quest gets a lot of requests from our LiteSpeed customers asking how they can better understand what SQL Servers are on the network and where they have LiteSpeed deployed. Quest has two products that can help customers get answers to both of these questions:

• Quest Discovery Wizard for SQL Server 3.0 Beta
• Toad for SQL Server 4.6

Discovery Wizard for SQL Server is our freeware SQL Server discovery tool. If you do not have a comprehensive list of SQL Servers in your environment, you can use the Discovery Wizard to check your network for installed instances of SQL Server. If you already have a comprehensive list of instances registered in Toad for SQL Server, you can skip to the next section.

The Discovery Wizard uses a variety of scan methods to search for SQL Servers by Domain or IP Address, including:

• Domain searching using NetEnum discovery
• Domain searching using Active Directory
• IP Scan using Network Registry
• IP Scan using WMI
• IP Scan using Login Attempt
• IP Scan using TCP Connection

Select the scan method (Domain or IP Address), enter the domains and/or IP Address ranges, check the appropriate scan types, and click the Start button.

The Discovery Wizard maintains a list of login credentials for each discovered instance. Right-click on instance and select the Test Connection option to enter the login credentials and then select the Collect Data option to query the instance. Once this is done, you’ll see a list of all the interesting SQL Server and Operating System information as well as the installed LiteSpeed version:

If you don’t have a comprehensive list of SQL Server instances, this is a good place to start. But you can do even more when you combine the results of the Discovery Wizard with the Toad for SQL Server 4.6 Group Execute functionality.

Group Execute is a feature of Toad that allows a user to easily run a query across multiple SQL Server instances. Using this feature, we can query each instance for LiteSpeed licensing information, and then review these results in Toad or export to Excel.

First things first. If you used the Discovery Wizard to find your instances, you can easily export them to Toad using the “Export to Toad for SQL Server” option on the main menu.

This feature creates a compatible Toad Connection file which you can then import into Toad using the Import function on the Toad Connection Manager:

Once your connections are imported into Toad, connect to any instance, open an Editor window, and select the Group Execute tab on the left (default location). If you don’t see it, right-click in the Editor and select the “Show Group Execute” function.

First check the “Enable Group Execute” option in the Group Execute tab. Second, select the instances you want to check for LiteSpeed. In order for Group Execute to work, you must save login credentials for each connection so Toad can automatically connect, so make sure this information is entered in the connection properties before proceeding.

Now we’ll write a simple query to check for LiteSpeed on each instance. This T-SQL batch executes the LiteSpeed Licensing extended stored procedure if it is installed in the master database:

IF EXISTS (

  SELECT 0

  FROM master..sysobjects where name = N'xp_sqllitespeed_licenseinfo')

EXEC master.dbo.xp_sqllitespeed_licenseinfo

GO

Let’s save the file before executing. Use the default TEF file format. This Toad file format saves both the selected connections and the SQL statement in a single file to make it easier to execute in the future.

Last step is to execute the SQL. Toad automatically connects to each instance and runs the query. If there is a connection or execution problem, the Status column next to each connection will indicate there was an error. Simply click on the Messages tab for more detail about the issue and try and correct.

The results are combined into a single grid for easy review. You’ll notice a “Connection” column in the output. This allows you to see which SQL Server each row of results belongs to. Looking at the Evaluation column, you can determine the type of license:

• 0 = a permanent license that does not expire
• 1 = a license that is only good for a specified number of days, like the built-in 15 day trial key (see the Evaluation Days columns)
• 2 = A license that expires on a specific date (see the Expires column)

If you prefer working in Excel, simply right-click the results and select the Quick Export to Excel option.

You now have a repeatable way to not only discover your installed SQL Servers, but also check to see where you have LiteSpeed deployed.

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!