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 backupset
JOIN 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.