Monday, December 23, 2013

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

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. ;-)

Tuesday, December 10, 2013

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.

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.

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.  
Google answered this question pretty quickly.
Find the ten largest files in a directory.

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. ;-)

Monday, November 11, 2013

How do I handle stress at the office? It beats loading bombs.

When you've done this for a living.

This isn't usually that hard.

I recall a discussion on Twitter about how IT pros handle stress.

My own personal experience was...

WORKING CONDITIONS: Works indoors and outdoors, in inclement weather, and on icy, wet, or slippery ramps and aircraft surfaces in temperature and humidity extremes. Subject to dangers from exposure to toxic fumes, high-pressure air and fluids, fast actuating aircraft surfaces such as landing gear, speed brakes, and flaps.  Dangers also include engine noise, jet blast, jet intakes, explosive munitions, electrical voltage cartridge actuated devices, liquid oxygen, fire, aircraft fuels, lubricants, paints, hydrazine, and solvents.

When a production outage occurs, wouldn't you rather have someone who has safely handled bombs? Anything at the office is a cake walk after loading bombs or having RPGs shot at you.  Hire a vet.

I salute our nation's veterans on Veteran's Day.  

It's not all bad, the above "working conditions" are from a job posting in Honolulu, Hawaii. ;-)

Wednesday, October 2, 2013

Tampa Hillsborough SQL User Group meeting.

I spoke at the Tampa - Hillsborough SQL Server User Group meeting on Thursday, September 26, 2013.

I presented "Cure your sysadmin addiction" covering how to strictly manage your SQL Server sysadmin role membership.

The projector cooperated or I've gotten better at using one. 

Interestingly, I am presenting a PowerPoint presentation using LibreOffice Impress running on Ubuntu Linux 12.04 LTS. How's that for interoperability? Linux plays nice even with Microsoft formats.

There were several good questions and a nice amount of discussion about the topic.

The materials can be downloaded here

Thank you to all that attended.

Tuesday, September 24, 2013

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.

Wednesday, September 4, 2013

SQL Saturday #232 Orlando - I'm speaking!

I'll be speaking at SQL Saturday #232 in Orlando, FL on Saturday, September 14, 2013.

The information for this always outstanding event can be found here.

I will be presenting "Cure your sysadmin addiction".  If you have the requirement to reduce DBA permissions in your Production environment due to Risk and Compliance mandates, you should attend. 

This will be my third time speaking in Orlando and my eleventh SQL Saturday overall.

SQL Saturdays are an extremely inexpensive way for IT Pros to gain an edge in a competitive job market and to develop a network that can help you at your current job or find that next job.

Get an edge, attend!

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.