My backup script (where, how, how big, etc.)
5November 26, 2018 by Kenneth Fisher
A while back I wrote about how to find where your backups are. And I’ve also written about I use a solution in SSMS to hold a bunch of my scripts. I was using this again today (I use it a lot!) and thought I would bring it back up with some additional detail.
In the first part of the script (and by script I mean a .sql file) I have a commented out backup command. A few things you’ll notice here. I have two spaces after the word DATABASE. This is so I can just type (or copy and paste) the database name right in. I also have the end of my file I’m restoring to (_CO.BAK). This is because this is for ad-hoc backups and they will almost always be copy only so I want a CO extension on the end. I just need to type in the path (that I’ll get from the later script) and a date (I like to have a date in the filename) and I’m good to go. Last but not least, there is COMPRESSION, COPY_ONLY and STATS so I don’t forget them. COPY_ONLY so I don’t mess something up, COMPRESSION because not every server I work on has backup compression on by default and STATS because I find it annoying if I can’t tell about how long my backup has to go.
-- BACKUP DATABASE TO DISK = '_CO.BAK' WITH COMPRESSION, COPY_ONLY, STATS = 10
Next I have a commented out restore command. Pretty similar to the backup command but in this case I also have a sample of the MOVE clause because I end up using it a lot for ad-hoc restores.
/* RESTORE DATABASE FROM DISK = '_CO.BAK' WITH MOVE '' TO '.mdf' ,MOVE '_log' TO '.ldf' ,STATS = 10 */
Note: Both of the commands above are for ad-hoc tasks. Upgrades, refreshes, moves, DR, etc. You should have a regular backup schedule using jobs and you should probably even have a regular restore schedule to test your backups.
This next commented out script is really handy if you are doing big restores/backups. It tells you what backups/restores are running, the percent complete, ETA, elapsed time and ETA in minutes and hours along with the command being run. I don’t think I wrote this but unfortunately I didn’t make a note where I got it from. If you wrote it please tell me so I can add an attribution.
/*
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000
ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
*/
And last but not least my most common script. Which is why it’s not commented out. This way I just hit CTRL-E and it runs. This script tells me what backups have been taken, how long they took, how big they are and where they go. This is particularly useful if I’m working on an instance the first time and want to know where the backups are going, or if I need to take a backup and want to know an approximate size and how long it will take.
USE msdb GO SELECT TOP 100 backupset.database_name, backupset.type, backupset.name, backupmediafamily.physical_device_name, backupset.backup_finish_date, backupmediafamily.logical_device_name, backupmediafamily.device_type, CAST(backupset.backup_size/1024/1024 as int) AS Size_in_MB, datediff(minute,backupset.backup_start_date,backupset.backup_finish_date) backuptime_minutes, is_copy_only FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id WHERE 1=1 AND backupset.type = 'D' -- Full (D), Differential (I) or Log (L) -- AND physical_device_name NOT LIKE 'VNB%' -- Ignore backups going to tape -- AND backupset.database_name like '' -- Only backups for a specific DB or group of DBs ORDER BY backupset.backup_finish_date DESC, backupset.database_name DESC;
Now you may not need all of these pieces. Take some or all and hopefully they will be as useful to use as they have been to me. And BOY are they useful at times.
SQL Studies 
Great scripts. I use things like this all the time, but have never bothered building a template for them. I might have to steal shamelessly from your blog post.
I do think your last script could use an extra condition on your WHERE clause:
AND backupset.backup_finish_date >= (SELECT MAX(A.backup_finish_date) FROM msdb.dbo.backupset AS A WHERE A.type = ‘D’ AND A.database_name = backupset.database_name)
If you use this in lieu of any of the backupset.type = ‘D’ clause it will return all of the backups taken since the last full backup, which is great if you’re in a shop where you only take fulls once a week and have differential and log backups you need to restore in case of an emergency.
Of course it would also help to reverse the clauses of your order by too in this case.
You are more than welcome to use the scripts 🙂 That’s why I post them in the first place. And of course you should change/add to the where clause to suit your needs! I haven’t run into that particular issue yet which is why I don’t have the addition to the where clause in mine but by all means add it to yours!
I did String and Gum scripts for backups for years … and then replaced it with a Stored Procedure. I had contractors and colleagues taking “just in case” backups, putting them somewhere obvious … to them … that made a Restore hard finding any odd DIFF or TRANS, or finding those files clogging up disk space years later, as they weren’t part of any routine cleanup.
I now have a backup SProc. With no parameters it displays help info – no excuse for contractors! The parameters are Database name, Backup type and a comment. The comment is usually the most important thing when a restore is needed e.g. “Before Step 4 of Upgrade 5” … all the routine backup stuff happens on a schedule, so any adhoc backup is for a “belt and brace” reason. This SProc can also be called from the top of an upgrade script …
There is also a retention period parameter, but the default is usually fine.
It puts the backup in the same place as all the other, scheduled, backups. It is guaranteed to be part of the normal server back up. The file gets deleted after the normal (or specific) retention period, like all the other backups.
The “Type” parameter allows for Full / Diff / Transaction etc. but also for things like “Both Transaction and then Full/Diff” which is typical here – I want the FULL to be as small as possible, and to restore as quickly as possible, so lets not save a potentially significant amount of open transactions in it too. If the backup runs at the end of a Script chances are good that there is abnormally large transaction log usage.
Then I also have a Restore SProc. If I am restoring a DB its either ‘coz I copied a backup file from somewhere, and want to mount that DB, or I screwed up and want to restore to some earlier point in an upgrade. So my restore lets me “develop” the parameters incrementally.
No parameters: display a list of databases
@DatabaseName only – display the most recent 10 full backups
With @DatabaseName provided it then displays a template for the SProc call and I fill in whatever bits I know, and then it gives me more information until it has enough for an actual restore command.
@RestorePath = ‘DEFAULT’ – usually the restore is from the “normal” drive/folder as configured for this server.
@RestoreFile – a Wildcard such as ‘MyDatabase*FULL.BAK’ displays a directory list. A specific file displays the RESTORE FILELISTONLY and HEADERONLY commands in case needed, along with the details of the specific file AND the comment 🙂
@DiffFile – for optional DIFF restore too
@DataPath, @LogPath – the locations for MDF/LDF. I don’t think I’ve ever had to change these from what the SProc spits out in its template … but one day I might have to.
@TLogFile is a template for any TLog restores e.g. “MyDatabase*LOG.BAK”. If provided this will output a set of restore commands for all the TLog files found (optionally after a given date/time or after the time of a given Backup file). I can then had edit the list to only include the ones I want – there’s a commented out STOP AT command in the template, with an example of the correct format for the time …
@RestoreProfiles – optional flag to restore Profiles for users / logons.
Once enough parameters are provided the SProc spits out the SQL script for the restore. That includes all the MOVE phrases, which keep my logical DB names sensible 🙂 and with ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE at the start and other things, commented out, such as changing the DB to MULTI_USER, READ_WRITE, doing CHECKDB, and Updating Statistics … not often used, but helpful to have the command available and fully-formed in the template
Saves me lot of time, and saves me from myself :), particularly at critical times doing upgrades etc.
Nice! Our problem is that we periodically get a big influx of new servers and until they are rebuilt to our standards the backups could be anywhere. That was the original reason I started the script 🙂
[…] from the rest of my sometimes literally dozens of queries. Things like my query to figure out where the backups for this instance are, and my diagnostic queries are almost always up there at the top left. So I open the file, pin the […]