Restore master database with Dell NetVault Litespeed for SQL Server

The DBA teams asked if I could create a job in our maintenance routines to do a native backup of the master database instead of a Litespeed backup. Their reason was they weren't able to restore master using Litespeed because the database server had to be in single-user mode to do such a restore and Litespeed required two connections to the server to do the task.

I mentioned that there are instructions in the Litespeed help files for a master database restore.

The team told me that it didn't work for them. So, I took the task to validate the instructions found here https://support.quest.com/search/SolutionDetail.aspx?id=SOL13484

I took a backup of the master database at 9:44 AM.

Next, I created a login on the server after the backup called ThisLoginWillNotBeHereAfterMasterDatabaseRestore to prove the master database restore did in fact happen.

This login will no longer exist after the upcoming master database restore because it was not captured by the backup taken at 9:44 AM.

I stopped the SQL Server services for the instance.  Either SSMS or SQL Configuration Manager can be used to stop the services.  When you stop the database instance, the corresponding SQL Agent will also be stopped.

I started the database instance in single user mode in one command window.

The instance has started in single user mode.

I open a second command window and ran the database restore command.

The restore runs successfully in seconds.

Additional messages are written to the first command window and the command prompt returns.

I restart the database instance using the SQL Configuration Manager.  

I start SQL Server Management Studio to find the login created after the backup no longer exists proving the master database has been restored.

The database restore was completed in a few seconds. The entire process probably takes less than 5 minutes.  

I took a bit longer because I was taking screenshots. ;-)

There is more than one way to find database backup files

I was told during the initial implementation of Ola Hallengren's Maintenance Solution at my workplace that it was too hard to find database backup files in the default folder structure his solution creates.  The concern was that it took too long to find the oldest file or the biggest file when trying to resolve a space issue on a database server.



Ola's solution creates a folder tree with these elements. Drive:\root\ServerName\DatabaseName\TypeOfBackup



You specify the backup drive and root folder using the Directory parameter of the DatabaseBackup stored procedure.


When I originally solved this problem, Windows XP was in use.  I wish I had spent more time figuring this out because I had to modify Ola's database backup procedure to dump all the backups into one folder in order for the DBA teams to sign off on the initial deployment.


It only takes a couple of minutes to search using Windows Explorer in Windows XP but finding files in a folder tree has gotten much easier on Windows 7 and higher.  



Given Windows XP's imminent retirement, I will only cover Windows 7 or higher.










GUI

Start Windows Explorer and navigate to the backup root folder on the server.


Type *.bak in the Search field in the upper right hand corner of Windows Explorer on Windows 7 or 8 and press Enter.


By default, Windows Explorer in Windows 7 and higher searches sub-folders.

The Search result returns all the database backups
in the entire folder tree.

The two requirements given were finding files by size or date.



Both are easily solved by clicking on the appropriate header. 

In the search result, headers are clickable and allow sorting by any column. 

So, click on Date Modified to find files by date or click on Size to find files by size.

Finding files to delete by size or date is done in less than a minute.

You can even save searches now.

http://www.howtogeek.com/howto/5316/how-to-save-searches-in-windows-7/

Microsoft's GUI interfaces are sufficient if you only have a few servers to check. But GUIs don't scale when you need to do the same task on a few hundred servers. So, you'll need to do a bit of scripting.  

PowerShell

Google answered this question pretty quickly.

Find the ten largest files in a directory.

http://stackoverflow.com/questions/798040/find-the-10-largest-files-within-a-directory-structure



get-childitem -path  C:\Backup -recurse | ?{ -not $_.PSIsContainer } | sort-object Length -desc | select-object fullname -f 10

gci C:\Backup -r |  ?{ -not $_.PSIsContainer } | sort Length -desc | select fullname -f 10



Finding the oldest ten files in a folder tree by date

get-childitem C:\Backup  -recurse | ?{ -not $_.PSIsContainer } | sort-object LastWriteTime | select-object fullname -f 10

gci C:\Backup  -r | ?{ -not $_.PSIsContainer } | sort LastWriteTime | select fullname -f 10

gci \\ServerName\FolderName\dump_data  -r |  ?{ -not $_.PSIsContainer } | sort Length -desc | select fullname -f 10



Including ?{ -not $_.PSIsContainer } in the pipeline ensures only files not folders are included in the results. 



If you want to run these commands on multiple servers, wrap them in a foreach loop that reads a list of text files from a server. Check out my PowerShell posts for examples. 



Old School

If you are not comfortable with PowerShell, the venerable dir command from the Windows command line is still available.



Oldest files first in a folder tree

dir /S /OD | more


Files ordered by size largest first in folder tree

dir /S /O-S | more



So, it doesn't take very long to find the largest or oldest database backup using any of these methods.  Which means I didn't need to modify Ola's scripts for the next release. ;-)

SQLSaturday #232 Orlando - The case of the pesky projector

On Saturday, September 14, 2013, I presented my session "Cure your sysadmin addiction" at SQL Saturday #232 in Orlando at Seminole State College. 



I can prove it. I have pictures. 



On the left, I'm wearing my speaker shirt "Bubba-style". 













The best #SQLSat232 tweet was...

My session was right after lunch and the speaker group picture so I had to hurry to my session at 2 PM



Once again, getting the projector to play nice with my laptop proved to be difficult.



I could not get PowerPoint Presenter View to work correctly.  



Running Windows 8 as a guest OS in VirtualBox on a Ubuntu Linux 12.04 LTS host should not be this difficult.  Should it?



Why does this always happens when Rob Volk is in the room?

That's Rob on the right.


























I did test my setup prior to my talk.  It worked at home.  Here's proof.  

Believe me now Mr. Dunagan?  ;-)

I think I need to run the Guest OS in Fullscreen mode for it to work correctly.

It might have been a resolution issue also. Adjusting the resolution I'm sending to the projector may have helped. I'll keep that in mind next time.



Despite the issues with the projector, I did get through my entire presentation and still had time to answer questions along the way.  It was my third time presenting this session so I was pretty pleased I remembered most of my notes.



If you need another good reason to manage sysadmin strictly, consider this.



Another great job by Karla Landrum, Kendal Van Dyke and the team in Orlando.  Seminole State College is one of the best SQL Saturday venues. Keller's BBQ provided a great lunch again with the speakers serving up the goodies. I'm glad the speaker shirts were the standard polo.  I can wear it to work although I could be mistaken for a Florida Gators or Clemson Tigers fan.



I'll get a chance to do my talk again at the Tampa SQL User group meeting at the end of this month.  Hopefully, I can get the pesky projector working.

PASS Security Virtual Chapter webinar - Cure Your Sysadmin Addiction

I had the pleasure of presenting for the new PASS Security Virtual Chapter on August 29, 2013.  



My presentation  "Cure your sysadmin addiction" describes the fundamentals of Separation of Duties, reviews SQL Server fixed server roles and granular permissions and the steps I took to reduce DBA permissions in our Production environments to satisfy a mandate from the management and our IT Risk and Compliance group.



If you missed it, the session was recorded. 

Please forgive the audio difficulties at the beginning. Once I dialed into the session, it went smoothly.



Thank you to K. Brian Kelley, Argenis Fernandez, and Robert Davis for the opportunity.

PASS Summit 2012 - Red Gate SQL in the City

http://community.invisionpower.com/gallery/image/7454-space-needle-downtown-and-mt-rainier-seattle/

I was fortunate enough to get my company's approval to travel for the entire week.



My week began with Red Gate SQL in the City on Monday at the Bell Harbor Conference Center on the Seattle waterfront. SQL in the City is a FREE community event solely sponsored by Red Gate that deftly walks the line between vendor showcase and community event.  Upon arrival, I immediately got some great news regarding product enhancements that will help resolve issues at work. It included a great lineup of speakers and attendees like Brent Ozar, Jeremiah Peschka, Allen White, Gail Shaw, Steve Jones, Grant Fritchey and Buck Woody.



I met fellow PowerShell fan, Laerte Junior from Brazil in person for the first time at this event.  Laerte ended up with the best collection of photos and shared them via Twitter.  He could have been the official photographer for the PASS Summit.

A great lunch was served and the event finished up with a Drinks Celebration at the end. This event just further confirmed the awesomeness that is Red Gate Software.  

How cluttered is MSDB?

# My quick and dirty PowerShell script to check for the oldest entry in MSDB.

# I'm using a SQLPSX 1.6 function Get-SqlData to execute queries. 

# Runs against multiple servers

   1:  $start = get-date

   2:  write-host "Start: "  $start

   3:   

   4:  $serverName = "SERVER\INSTANCE"

   5:  $databaseName = "dbINVENTORY"

   6:   

   7:  $FilePath = "C:\Output"

   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

   9:   

  10:  # here string contains SQL query

  11:  $qry = @"

  12:  SELECT DISTINCT

  13:          ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM

  14:                                         WHEN 'DEFAULT' THEN ''

  15:                                         ELSE '\'

  16:                                       END + CASE INST_NM

  17:                                               WHEN 'DEFAULT' THEN ''

  18:                                               ELSE ISNULL(INST_NM, '')

  19:                                             END AS InstanceName

  20:  FROM    dbInventory A

  24:  ORDER BY InstanceName

  25:  "@

  26:   

  27:  $Servers = Get-SqlData $serverName $databaseName $qry

  28:   

  29:  $qry = @"

  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset 

  31:  "@

  32:  $databaseName = "msdb"

  33:   

  34:  # Version inventory

  35:  @(

  36:  foreach ($svr in $Servers)

  37:  {

  38:      $serverName = $svr.InstanceName

  39:      trap {"Oops! Query failed. $_"; continue } Get-SqlData $serverName $databaseName $qry

  40:      

  41:  }

  42:  )  | export-csv -noType $OutFile

  43:   

  44:  $end = get-date    

  45:  write-host "End: "  $end

When does a practice become BEST?

How many good references does an idea need before it becomes a best practice?

For starters, Ola Hallengren's script for Backup, Index Maintenance, and DBCC checks.

I've done the research and spoke to Ola in person at the 2010 PASS Summit.

Ola's good references come from no less than Microsoft, Red Gate Software, Quest Software, and Microsoft MVPs.

I think Ola Hallengren's maintenance solution is a BEST PRACTICE.  

I have a few questions for you.  

Do you agree?  

Do you use Ola's maintenance solution?  

Do you use Ola's solution as is or have you modified it?  

Please respond in the comments.