Search results matching tags 'maintenance' and 'SQL Server'

Backup File Naming Convention

Andrew Kelly — Fri, 05 Oct 2012 19:41:00 GMT

I have been asked this many times before and again just recently so I figured why not blog about it. None of this information outlined here is rocket science or even new but it is an area that I don’t think people put enough thought into before implementing. Sure everyone choses some format but it often doesn’t go far enough in my opinion to get the most bang for the buck. This is the format I prefer to use:

ServerName_InstanceName_BackupType_DBName_DateTimeStamp_nn.xxx

ServerName_InstanceName = Pretty self explanatory but lets look at it. Let’s say that the machine name is M432 and the instance is Dev2008. That would normally be W432\Dev2008 however I don’t like special characters so I change it to W432_Dev2008. If it was a default instance it would be W432_W432. Some people (including myself) prefer to leave off the Server Name if it is a default instance but that is up to you. Since the default instance is always the name of the server it’s still pretty clear where it came from.

BackupType = FULL, DIFF or LOG. Nothing more is needed here.

DBName = Full name of the database. One note here is that if the name has spaces I like to replace the space with some other valid character and some people prefer to remove the space altogether. Spaces in an object name is a whole debate in itself and I wont go there now .

DateTimeStamp = yyyymmddhhmmss. This allows me to know exactly when the backup started just by looking at the name and makes it unique as well. I don’t know any one who takes two backups of the same db in less than a second so this convention works to avoid file name conflicts.
_nn = The individual number associated with backing up to multiple files for a single backup operation. Typically backing up to multiple files for a FULL or DIFF backup can be more effecient with larger dbs so appending a number from 01 to nn ensures uniqueness as the rest of the name will be the same. If it is a single file then you can simply use 01 or omit that part altogether.

.xxx = I also like to use the extension to identify the tool used to create the backup file. For native SQL Server backups I use .bak regardless of the type of backup (Log, Diff or Full). For backups done using a 3rd party utility such as the one from Red-Gate I would use .sqb and so on.

This naming convention allows me to do several things. First it ensures each backup file will have a unique name. Second it allows me with a quick glance to see where the backup originated, what type of backup it is, which database it is for, when the backup started and which tool was used to create the backup. So again there is nothing particularly new to this approach but I often see the naming falling short of this and generally only having the DB name and timestamp. Why not take the extra few steps to ensure you get the most out of your naming that you can. The code to generate the whole file name is pretty simple and can be done dynamically so why not go this route? OK there are always exceptions so let’s not start a debate war .

Have fun,

Andy

Backup those keys, citizen

BuckWoody — Tue, 20 Apr 2010 12:14:50 GMT

Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.

The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the BACKUP SERVICE MASTER KEY command, which you can read more about here.

The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the BACKUP MASTER KEY command, which you can read more about here.

Finally, you can use the keys to create certificates and other keys – those should also be backed up. Read more about those here.

Anyway, the important part here is the backup. Make sure you keep those keys safe!

Backup Meta-Data

BuckWoody — Tue, 30 Mar 2010 14:46:00 GMT

I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this T-SQL Script.

A few words here - you need to be in the MSDB database for this to run, and you can add a WHERE clause to limit to a database, timeframe, type of backup, whatever. For that matter, I won't use all of the data in this query in my PowerShell script, but it gives me lots of avenues to graph:

SELECT distinct t1.name AS 'DatabaseName'

,(datediff( ss, t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'

,t3.user_name AS 'UserResponsible'

,t3.name AS backup_name

,t3.description

,t3.backup_start_date

,t3.backup_finish_date

,CASE WHEN t3.type = 'D' THEN 'Database'

WHEN t3.type = 'L' THEN 'Log'

WHEN t3.type = 'F' THEN 'FileOrFilegroup'

WHEN t3.type = 'G' THEN 'DifferentialFile'

WHEN t3.type = 'P' THEN 'Partial'

WHEN t3.type = 'Q' THEN 'DifferentialPartial'

END AS 'BackupType'

,t3.backup_size AS 'BackupSizeKB'

,t6.physical_device_name

,CASE WHEN t6.device_type = 2 THEN 'Disk'

WHEN t6.device_type = 102 THEN 'Disk'

WHEN t6.device_type = 5 THEN 'Tape'

WHEN t6.device_type = 105 THEN 'Tape'

END AS 'DeviceType'

,t3.recovery_model

FROM sys.databases t1

INNER JOIN backupset t3

ON (t3.database_name = t1.name )

LEFT OUTER JOIN backupmediaset t5

ON ( t3.media_set_id = t5.media_set_id )

LEFT OUTER JOIN backupmediafamily t6

ON ( t6.media_set_id = t5.media_set_id )

ORDER BY backup_start_date DESC

I'll munge this into my Excel PowerShell chart script tomorrow.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Have you backed up your keys lately?

BuckWoody — Mon, 01 Mar 2010 14:06:04 GMT

Did you know that you already have a Server Master Key (SMK) generated for your system? That’s right – while a Database Master Key (DMK) is generated when you encrypt a certificate or Asymmetric Key with code, the Server Master Key is generated automatically when you start the Instance.

So you should back all of those keys up periodically, and then store that backup AWAY from the server itself.

There are two reasons for this – first, if the drives get stolen and you’re storing the key backup there, well, that should be obvious why that’s bad. Second, you want to protect the keys in case the system is destroyed or you can’t recover the drives. You will need those keys if you have encrypted anything in the database to get the data back.

More here: http://technet.microsoft.com/en-us/library/bb964742.aspx

No, the standard Maintenance Wizards don’t get this data. And no, I haven’t seen it addressed in most of the maintenance scripts out there anyway – sometimes for good reason, but this means you need to take care of it manually, and then document where you put that backup.

The SQL Server Health Check

BuckWoody — Thu, 11 Feb 2010 14:16:05 GMT

My friend Brent Ozar, who is a top-notch SQL Server Professional, mentioned on his blog (http://www.brentozar.com/archive/2007/04/sql-server-health-check/comment-page-1/#comment-17520) that he brought in Microsoft Support to do a “Health Check”. There were some questions about what this actually entails – so I thought I would post that description here.

A SQL Server Health Check from Microsoft is an offering provided through our support and consulting branches. If you’ve purchased a Premier support agreement, you can use the hours you pay for to have someone come out and check out your systems using two basic vectors: standard best practices, and the best practices for your environment. Let me explain that a little further.

Microsoft Consulting Services (MCS) has an advanced set of tools and techniques to reach deep into your systems to evaluate them against a set of criteria that we establish for the best performance, safety and reliability for SQL Server. But your SQL Server Instances will be different than someone else’s, so the consultant will also spend some time in an interview with various teams to find out how you’re using the system. They’ll consult with experts in that area back at Redmond, and at the end of the engagement you get what I feel is the most valuable part of the exercise – the report. The report shows what was done, how it was done, what the findings are and what recommendations the consultant makes. It’s a thing of beauty.

You don’t have to have a Premier agreement to get a SQL Server Health Check, and companies other than Microsoft have versions of this as well. The prices vary, so if you want a Microsoft person then contact your local Microsoft office and ask for the Consulting Manager for your area. They will get you to the right person and you can work out the costs from there. If you take a hard look, most of the time the cost is well justified.

On a related note, I think it’s fantastic that Brent has the foresight to bring in “another pair of eyes” on his systems. Brent is actually in the program to gain a SQL Server “Master” certification – something only a few people in the world have. He works for a software development firm that creates fantastic products for – wait for it – SQL Server! He could evaluate his own systems, and of course he does. But he realizes that there’s always something more to learn, and someone out there may know one more little detail that he doesn’t. This is the mark of a very bright person.

Tracking SQL Server Time

BuckWoody — Mon, 25 Jan 2010 14:10:28 GMT

In the past few blog posts I’ve showed you how to use several methods to track things in SQL Server. You can use the “tags” to the right of this post here at this site to list things like PowerShell, Performance Tuning and so on. Now that you’re armed with these tools, what should you track?

Well, one of the items I track is time. I track the time it takes for lots of things, but they fall into three general buckets:

Queries – Normally I track the five longest running queries with their query plans.
Maintenance – From how long each backup takes to index reorgs and rebuilds, I want to know how long these things take.
Jobs – Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is very useful.

For each of these, I track the minimum, maximum and average times. I look for outliers – things that suddenly change and so on.

There are a lot of uses for this information. From performance tuning to developing a recovery plan, all of these actions need to be taken into account.

I think I’ll write up an article sometime on how I do this – it’s a little long for a blog post.