Setting up and then keeping track of the backups is a large part of this chore. Once you've determined whether to set each dB on Full or Simple recovery, it's time to create a maintenance plan on each instance. For some peculiar reason known only to Microsoft, I've only been able to consistently do this RDP'd into the server with SSMS fired up connected to (local). I like to keep my backup jobs separate from all the other maintenance, so when I run the maintenance Wizard I select just the three options of Log Backups, Database Backups, and Backup Cleanup. That's not the order in which the checkboxes appear, but when you arrange them, sequence them in that order.
Needless to say, you don't place your backup files on the same device where you host your log or mdf files. One lesser known useful fact however is, although you can't select it from clicking on treeviews, you can manually type in a path to a UNC share (\\myBackupSrv\sqlBackkups, or even \\10.10.44.44\myStuff) ahead of your backup file name. You can even address remote shares on the cloud this way.
Once your plans are in place, you need to verify they are working properly. Things get more complicated once you backup to a cloud vendor: not only do you have your cloud backup routines but for safety you should keep a local backup copy occasionally as well. Also if you are hosting gigantic databases supporting OLTP then chances are it makes more sense to run daily differential backups instead of full backups (with maybe a weekly or monthly full depending on how much the dB grows and changes). After a while therefore you can end up with a fairly complicated blended backup strategy.
If you're like me then, you'll want to open up your SSMS every morning and examine the Explorer Details window. Add the column "Last Backup" and sort it, and right away you know what needs some attention. Also you will find this query helpful:
USE msdb
GO
SELECT b.backup_set_id, b.name, b.user_name, b.database_creation_date, b.backup_start_date, b.backup_finish_date, b.type, b.backup_size, b.database_name,
b.server_name, b.machine_name, b.recovery_model, b.is_snapshot, b.is_readonly,
b.is_single_user, b.is_damaged, f.physical_drive, f.physical_name, f.state,
f.state_desc, f.is_present, b.is_copy_only, m.physical_device_name
FROM backupset AS b INNER JOIN
backupfile AS f ON f.backup_set_id = b.backup_set_id INNER JOIN
backupmediafamily AS m ON b.media_set_id = m.media_set_id
WHERE (f.physical_name LIKE '%.mdf%') AND (b.backup_start_date > GETDATE() - 14)
ORDER BY 1 DESC
GO
Often I'll add into the where-clause a specific b.database_name if I'm looking specifically for the backups for just one database. Note the results of this query show not only the dB backups but also the log, differential, and VM snapshots. It's useful to recognize that when you do a Restore through the user-interface, these are the tables the system references to reach the correct restore file suggestions.
In some environments, as when you have another team moving reporting databases around, it's helpful to see the inverse condition (when were the restores performed?):
USE msdb
GO
SELECT DBRestored = destination_database_name ,
RestoreDate = restore_date ,
SourceDB = b.database_name ,
SourceFile = physical_name ,
BackupDate = backup_start_date
FROM RestoreHistory h
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
where physical_name like '%.mdf%'
ORDER BY RestoreDate desc
GO
What's great about this query is it shows the name of what backupset was used to restore each database and the date that backup got created.
Naturally (or maybe I should phrase that as "unfortunately") if you yourself are asked to do a restore, a good percentage of time it will be under duress with the boss breathing down your neck. Hence I always keep this template by my side to pull up in times of emergency:
RESTORE DATABASE [ThisBizBI]
FROM DISK = N'\\bkupStore\sql backups\DEV-ThisBizDB\ThisBizBI\ThisBizBI_backup_2014_08_21_040010_8176618.bak'
WITH FILE = 1, MOVE N'ThisBizBI' TO N'E:\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ThisBizBI.mdf',
MOVE N'ThisBizBI_log' TO N'E:\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ThisBizBI_log.ldf',
NOUNLOAD, STATS = 10
GO
\\*------------------------ *\\
ALTER DATABASE [ThisBizBIDW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [ThisBizBIDW]
FROM DISK = N'\\bkupStore\sql backups\OLTP2SRV\ThisBizBIDW\ThisBizBIDW_backup_2014_09_08_040001_0041150.bak'
WITH FILE = 1, NOUNLOAD, REPLACE,
STANDBY = N'E:\XMPIEDB\MSSQL11.XMPIE\MSSQL\DATA\ThisBizBIDW_sby.BAK',
STATS = 10
GO
ALTER DATABASE [ThisBizBIDW] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
\\*------------------------ *\\
RESTORE LOG [ThisBizOSDEV_Restore]
FROM DISK = N'\\bkupStore\sql backups\OLTP2SRV\ThisBizOSDev\ThisBizOSDev_backup_2015_05_10_010007_0679267.trn'
WITH FILE = 1, NOUNLOAD,
STANDBY = N'E:\XMPIEDB\MSSQL11.XMPIE\MSSQL\DATA\ThisBizOSDev_Restore_sby2.BAK',
STATS = 10
GO
This template shows three different variations of a restore; I seldom use any one of them straight vanilla but may modify or copy a line or two from one to the other to achieve the results that I desire. The first is a restore of one database over another, the second example is a restore of a live production dB (kicking off the current users in the process) and leaving the database in "standby" mode, and the third example is a logfile restore. If you haven't yet, do read up on restoring a database with NoRecovery or Standby -- these are the options to use if you need to restore more than a single backup file to achieve currency.
No comments:
Post a Comment