Archive for the ‘Database Backups’ Category

How to Remove Database Backups Older Than X Number of Days?

Have you ever created a GUI Maintenance Plan to remove database backups older than X number of days and noticed that the full backups, transaction log backups, and differential backups older than X numbers of days are removed based on the specified retention days which is technically correct, but since the full baseline backups are removed the differential and/or transaction log backups that still exist and depend on the full baseline backups are rendered invalid in case restores are required?  I know that’s a fully loaded question. Let’s slow down and break it into pieces.

There are generally 3 common database backup types in SQL Server:

  • Full
  • Differential
  • Transaction log

Simply put, full database backup type is considered as the baseline backup as it is required to be restored first in case of disaster recovery before any other backup types. If you have differential backups and transaction log backups, they are then restored depending on the specific time and point of restore is needed.

Backup schedules and types are usually based on the 5 common factors:

  • Size
  • Restorability requirements
  • Disk space availability
  • Speed
  • Common sense (don’t laugh because common sense is not common practice and they are very different)

All of the above 5 common factors are interrelated. For example, if the database you are backing up is over 1 TB, most likely you should consider weekly full backup, daily differential backup, hourly or more frequent transaction log backups. Depending on the restorability requirements, you may need to adjust the frequencies provided that you have enough disk space storage. Usually, the C-level executives will wish to have database backups for the past year or since the company been in business. It is your job to give them the facts about the required budget. Notice that I did not say disk space because budget will make more sense to them than megabytes, gigabytes, and terabytes. Just translate GB to GBP if you are in the United Kingdom or GB to USD if you are in the United States. Speed of the backups and restores can also be a critical factor. You don’t want your backups to run and overlap their next subsequent scheduled runs. Don’t laugh, it happened too often. To increase the speed of the backups and restores, you can consider splitting the backups and enable backup compression if your SQL Server instance is running SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 Standard Edition or later. You can even consider third party backup software such as Idera SQL Safe Backup, Red Gate SQL Backup Pro, Dell (Quest) Litespeed, EMC Avamar, and many more if the budget permits.

Let’s head back to the main discussion without further digression. If you had ever created maintenance plans to remove backups older than 3 days for databases that are less than 100 GB with daily full backups and hourly transaction log backups, most likely you had noticed that the full backups and transaction log backups that are older than 3 days are removed. But, since the full baseline backups older than 3 days are removed the transaction log backups before the next full backup are invalid for restore as the baseline full backups are removed. How can we resolve this issue and make sure the transaction log backups that have dependencies on the full baseline backups are also removed?

The key to resolution can be described in the following steps:

  1. Decide on the retention day as per your business requirement for the database backups.
  2. Based on the retention days on step 1, find the next earliest full backup start date and time for all databases.
  3. When the full backups are removed, you need to make sure the transaction logs before the next earliest full backups are also removed.

The following is the example code. Make sure you review it carefully and modify it to suite your retention day and backup path folder.  Sure, you can code it in many different ways. For example, put it in a stored proc, find the backup path using the msdb.dbo.backupfile, and many others just to show off your talent.  Please share with us your solutions because great talents are terrible things to waste.

--/*
--Remove database backups older than # of day.

--Note: If you had removed the full baseline backup, all the diff and tlog backups between the full baseline backup up to the next full backup can't be restored if the baseline backup is missing. Therefore, they should be removed too.
--*/

DECLARE @RetentionDays tinyint 
SET @RetentionDays = 3 -- keep 3 days of backups

DECLARE @DeleteDateForFullBackups NVARCHAR(50)
DECLARE @DeleteDateTimeForFullBackups DATETIME

DECLARE @DeleteDateForTlogBackups NVARCHAR(50)
DECLARE @DeleteDateTimeForTlogBackups DATETIME

-- find the latest full backup for the threshold day
SELECT @DeleteDateTimeForFullBackups = MIN(bckset.backup_start_date)
FROM msdb..backupset bckset
                INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id
WHERE (bckset.[Type] = 'D') AND (DATEDIFF(dd, bckset.backup_start_date, GETDATE()) = @RetentionDays)

-- find the latest diff or tlog backup for the threshold day
-- the date and time range should be from the full backup to be removed up and before the next full backup
SELECT @DeleteDateTimeForTlogBackups = MIN(bckset.backup_start_date)
FROM msdb..backupset bckset
                INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id
WHERE (bckset.[Type] = 'D') AND (bckset.backup_start_date > @DeleteDateTimeForFullBackups) -- this is where the magic happens

select @DeleteDateTimeForFullBackups, @DeleteDateTimeForTlogBackups

-- remove the full backups
SET @DeleteDateForFullBackups = (SELECT REPLACE(CONVERT(NVARCHAR, @DeleteDateTimeForFullBackups, 111), '/', '-') + 'T' + CONVERT(NVARCHAR, @DeleteDateTimeForFullBackups, 108))
EXECUTE master.dbo.xp_delete_file
0 -- file type: 1 report files, 0 backup files
, N'C:\SQLBackup\' -- file path, make sure there is a trailing '\'
, N'bak' -- file extension, no dot in front of it
, @DeleteDateForFullBackups -- delete all files before this date and time
, 1 -- searching subfolders to delete: 1 yes, 0 no


-- remove the diff and tlog backups, create another block of code with modifications if you have diff backups
SET @DeleteDateForTlogBackups = (SELECT REPLACE(CONVERT(NVARCHAR, @DeleteDateTimeForTlogBackups, 111), '/', '-') + 'T' + CONVERT(NVARCHAR, @DeleteDateTimeForTlogBackups, 108))
EXECUTE master.dbo.xp_delete_file
0 -- file type: 1 report files, 0 backup files
, N'C:\SQLBackup\' -- file path, make sure there is a trailing '\'
, N'trn' -- file extension, no dot in front of it
, @DeleteDateForTlogBackups -- delete all files before this date and time
, 1 -- searching subfolders to delete: 1 yes, 0 no

REFERENCES:

Backupset
http://msdn.microsoft.com/en-us/library/ms186299(v=sql.90).aspx

backupmediafamily
http://msdn.microsoft.com/en-us/library/ms190284(v=sql.90).aspx

How to backup a database to split to multiple backup files?

This past week, a request was made for us to configure database mirroring for a database that’s over 100 GB. The native SQL Server backup file was approximately 80 GB. The primary data center is located in NYC and the secondary data center is located in Miami. That’s 1,283 miles apart according to Google Maps.  Based on our past experiences transferring a backup file that’s over 80 GB will likely result in failure as there will be possible data packet losses or intermittent network connections that will cause file transfer problems. We have decided to use the split backups features that’s available in SQL Server to backup to multiple backup files to decrease the backup files sizes, transfer them one by one, and then restore them to the Mirrored server. Here’s the overview steps and the example implementation steps required:

Overview steps:

  1. Backup the database to split to multiple backup files to decrease the files sizes.
  2. Transfer the backup files one by one over to the Mirrored server.
  3. Restore the database in the Mirrored server.
  4. Run the transaction log backup(s) in the Principal server.
  5. Transfer the transaction log backup files over to the Mirrored server.
  6. Restore the transaction log backups in the Mirrored server.
  7. Configure database mirroring once the transaction logs are up-to-date in the Mirrored server.

Implementation steps:

    1. Backup the database to split to multiple backup files to decrease the files sizes.
      -- Backup the database to split to multiple backup files
      BACKUP DATABASE AdventureWorks
      TO DISK = 'C:\SQLBACKUPS\AW\aw_file1.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file2.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file3.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file4.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file5.bak'
      WITH STATS = 10 -- report the backup progress status every with every 10 percent elapses
      
    2. Transfer the backup files one by one over to the Mirrored server.
    3. Restore the database in the Mirrored server.
      -- Restore the database in the Mirrored server
      RESTORE DATABASE AW
      FROM DISK = 'C:\SQLBACKUPS\AW\aw_file1.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file2.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file3.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file4.bak',
      DISK = 'C:\SQLBACKUPS\AW\aw_file5.bak'
      WITH MOVE 'AdventureWorks_Data' TO 'C:\AW\AW_data.mdf',
      MOVE 'AdventureWorks_Log' TO 'C:\AW\AW_log.ldf',
      NORECOVERY -- restore with no recovery because you will need to restore the transaction logs
      
    4. Run the transaction log backup(s) in the Principal server.
      -- Run the transaction log backup(s) in the Principal server
      BACKUP LOG AdventureWorks
      TO DISK = 'C:\SQLBACKUPS\AW\aw_tlog.trn'
      
    5. Transfer the transaction log backup files over to the Mirrored server.
    6. Restore the transaction log backups in the Mirrored server.
      -- Restore the transaction log backup(s) to the Mirrored server
      RESTORE LOG AW
      FROM DISK = 'C:\SQLBACKUPS\AW\aw_tlog.trn'
      WITH NORECOVERY -- restore with no recovery as you may have more transaction logs to restore
      
    7. Configure database mirroring once the transaction logs are up-to-date in the Mirrored server.

You can refer to the post on how to setup database mirroring for more details on the configuration steps.

REFERENCES:

BACKUP
http://msdn.microsoft.com/en-us/library/ms186865(v=sql.105).aspx

RESTORE
http://msdn.microsoft.com/en-us/library/ms186858(v=sql.105).aspx

How to detect missing database backups?

Equally important to scheduling database backups is to at the same time get alerted when the database backups are missing. This will help you sleep better at night without AmBien or the preferred extra shots of Tequila. When backups are ran, the histories get written to the msdb.dbo.backupset system table.  We can query it to find the backup history information or the lack of it of which implies no backups were ever taken. Yes. It is that simple.

If you have scheduled full database backups to run once per day and transaction log backups to run every 3 hours, it is within your best interest to get alerted if the full backups are missing with the time elapsed passed 24 hours and the same for transaction log backups with the time elapsed passed 3 hours.

Here’s the code to query for missing full backups with time elapsed passed 24 hours or more:

-- missing full backups, time elasped > 24
SELECT db.name as DatabaseName
, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
	ON db.name = bs.database_name and bs.type IN ('D') -- backup type D = Database, I = Differential, L = Log
WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
	AND db.state = 0 -- database state is online
GROUP BY db.name
HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > 24
	OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
ORDER BY db.name	

Here’s the code to query for missing transaction backups with time elapsed passed 3 hours or more:

-- missing tlog backups, time elasped > 3 hours
SELECT db.name as DatabaseName
, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
	ON db.name = bs.database_name and bs.type IN ('L') -- backup type D = Database, I = Differential, L = Log
WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
	AND db.state = 0 -- database state is online
	AND db.recovery_model < 3 -- recovery model: 1 = full, 2 = bulk_logged, 3 = simple
GROUP BY db.name
HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > 3
	OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
ORDER BY db.name

To send out the alerts in html report format via database mail, you can embed the code in html and schedule a job to run hourly. Here’s the example for the alert on missing full backups:

-- Alert on missing full backups with time elapsed over 24 hours
-- Replace with your recipients in line #31

-- declare the variable for interval hours to check for full database backups
DECLARE @IntervalInHours TINYINT -- check new database(s) in the past hour(s)
SET @IntervalInHours = 24 -- replace with the number of hours you wish to check for missing full backups with time elapsed

-- check for missing full backups
IF EXISTS(	
	SELECT db.name as DatabaseName
	, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
	FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
		ON db.name = bs.database_name and bs.type IN ('D') -- backup type D = Database, I = Differential, L = Log
	WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
		AND db.state = 0 -- database state is online
	GROUP BY db.name
	HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) >= @IntervalInHours
		OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		) 
		
BEGIN -- missing full backups with time elapsed over 24 hours

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html><table border = "1">' +
		'<tr>
			<th>Database Name</th>
			<th>Last Backup DateTime</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td>' + db.name + '</td>' +
			'<td>' + ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') + '</td></tr>'
		FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
			ON db.name = bs.database_name and bs.type IN ('D') -- backup type D = Database, I = Differential, L = Log
		WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
			AND db.state = 0 -- database state is online
		GROUP BY db.name
		HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) >= @IntervalInHours
			OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		ORDER BY db.name	
		
		SELECT @TableHTML = @TableHTML + '</table></html>'
		
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' missing full database backups alert!'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
END

This is what it looks like when the alert hits your email inbox (click on the image to view the full size):
Missing_Full_Backups

Here’s the example for the alert on missing transaction log backups:

-- Alert on missing transaction log backups with time elapsed over 3 hours
-- Replace with your recipients in line #32

-- declare the variable for interval hours to check for missing transaction log backups
DECLARE @IntervalInHours TINYINT 
SET @IntervalInHours = 3 -- replace with the number of hours you wish to check for missing transaction log backups with time elapsed

-- check for missing transaction log backups
IF EXISTS(	
	SELECT db.name as DatabaseName
	, ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') AS LastBackupDateTime
	FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
		ON db.name = bs.database_name and bs.type IN ('L') -- backup type D = Database, I = Differential, L = Log
	WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
		AND db.state = 0 -- database state is online
		AND db.recovery_model < 3 -- recovery model: 1 = full, 2 = bulk_logged, 3 = simple
	GROUP BY db.name
	HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) >= @IntervalInHours
		OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		) 
		
BEGIN -- missing transaction log backups with time elapsed over 3 hours

-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
	BEGIN
		SELECT @TableHTML = 
		'<html><table border = "1">' +
		'<tr>
			<th>Database Name</th>
			<th>Last Backup DateTime</th>
		</tr>'
		
		SELECT @TableHTML = @TableHTML +
			'<tr>' +
			'<td>' + db.name + '</td>' +
			'<td>' + ISNULL(CONVERT(VARCHAR, max(bs.backup_finish_date), 120), 'Never') + '</td></tr>'
		FROM sys.databases db LEFT JOIN msdb.dbo.backupset bs
			ON db.name = bs.database_name and bs.type IN ('L') -- backup type D = Database, I = Differential, L = Log
		WHERE db.name NOT IN ('tempdb') -- list of databases to exclude from checking
			AND db.state = 0 -- database state is online
			AND db.recovery_model < 3 -- recovery model: 1 = full, 2 = bulk_logged, 3 = simple
		GROUP BY db.name
		HAVING DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) > @IntervalInHours
			OR DATEDIFF(HH, max(bs.backup_finish_date), GETDATE()) IS NULL
		ORDER BY db.name	
		
		
		SELECT @TableHTML = @TableHTML + '</table></html>'
		
		-- Send email notification
		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' missing transaction log backups alert!'
		
		-- Find the database mail profile name
		create table #dbmail_profile
		(
		profileid int null,
		profilename varchar(125) null,
		accountid int null,
		accountname varchar(125) null,
		sequencenumber int null
		)
		insert #dbmail_profile
		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

		select @MailProfile = profilename
		from #dbmail_profile
		where sequencenumber = 1

		drop table #dbmail_profile

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = @MailProfile,
			@recipients = @Recipients,
			@subject = @MailSubject,
			@body_format = 'HTML',
			@body = @TableHTML;
	END
END

This is what is looks like when the alert hits your email inbox(click on the image to view the full size):
Missing_Tlog_Backups

REFERENCES:

sys.databases
http://msdn.microsoft.com/en-us/library/ms178534(v=sql.105).aspx

backupset
http://technet.microsoft.com/en-us/library/ms186299(v=sql.105).aspx

DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

How to enable backup compression?

You wish to increase backup speed and reduce the amount of disk space required for all the database backups. You had wondered if SQL Server has any compression functions or features. The good news is yes. But, it depends on what version and edition of SQL Server you are running.

As per Microsoft, backup compression was made available back in the SQL Server 2008 Enterprise edition. In SQL Server 2008 R2, all editions with Standard and above will also have backup compression as a feature. In addition, all editions of SQL Server 2008 and later can restore a compressed backup.

You can enable the backup compression on the instance level by configuring the ‘backup compression default’ option and you can override this instance level option on the backup statements. In addition, you can query the backupset system table to find the backup compression ratio.

To enable the backup compression on the instance level:

-- enable backup compression on the instance level
USE master
GO
EXEC sp_configure 'backup compression default', 1 -- 0 to turn off
RECONFIGURE WITH OVERRIDE

-- verify the current setting for backup compression
select c.name
, c.description
, c.value
, c.value_in_use
from sys.configurations c
where c.name like 'backup compression default'

To use the backup statement to override the instance level option for backup compression:

-- backup with compression
BACKUP DATABASE AdventureWorks -- replace with your database here
TO DISK = 'C:\SQLBackup\aw.bak' -- replace with your backup path here
WITH COMPRESSION -- NO_COMPRESSION to turn off compression on the statement level

To query the backupset system table to find the backup compression ratio:

-- querying the backup compression ratio
SELECT bs.database_name
, bs.backup_start_date
, bs.backup_finish_date
, convert(decimal(10,2),bs.backup_size/1024/1024) [Backup Size in MB]
, convert(decimal(10,2), bs.compressed_backup_size/1024/1024) [Compressed Size in MB]
, convert(decimal(10,2), bs.backup_size/bs.compressed_backup_size) [Compression Ratio]
FROM msdb..backupset bs

Note that third party backup software such as Idera SQL safe backup, Redgate SQL Backup Pro, EMC AVAMAR, Quest Software LiteSpeed, and etc will usually have a higher backup compression ratio than the native SQL Server backup compression. But, the native SQL Server backup compression is already available to you with no extra charge if the version and edition of SQL Server you are running has the backup compression feature. You must evaluate your budget, backup time requirement, and disk space availability to make more informative decisions on selecting a backup strategy.

REFERENCES:

Backup Compression
http://technet.microsoft.com/en-us/library/bb964719(v=sql.105).aspx

Backup compression default Option
http://technet.microsoft.com/en-us/library/bb677250(v=sql.105).aspx

Backupset
http://msdn.microsoft.com/en-us/library/ms186299.aspx

How to find the database backup paths?

Billy Bob, the primary SQL Server DBA for the Sales and Trading department, has gone on vacation. Mary Ann, the Team Lead of the development team has submitted a ticket for an urgent database restore from all the backups of yesterday to a test server so that her team can start testing the data as soon as they can. This will include the full backup from midnight (12:00 am) of yesterday and all the transaction log backups of yesterday up to 23:59:59 pm.

You are the substitute SQL Server DBA for Billy Bob for the week while he’s on vacation. You don’t know where the database backups are stored. Time is not on your side to start looking for the documentations or run-books. You need to know the backup paths for the backups so that you can do a restore to the test server. What can do you?

Every time a database backup is ran, entries are logged in the MSDB.DBO.BACKUPFILE, MSDB.DBO.BACKUPFILEGROUP, MSDB.DBO.BACKUPMEDIAFAMILY, MSDB.DBO.BACKUPMEDIASET, and MSDB.DBO.BACKUPSET system tables of the MSDB system database. To see the backup histories for all the databases in the instance and identify the backup paths for the backup files, you can run the following query. Remember to modify the database name in line #12:

-- Querying the database backup histories in the instance
SELECT @@SERVERNAME as InstanceName
,bs.database_name as DatabaseName
,bmf.physical_device_name as BackupPath
,bs.type as BackupType
,bs.backup_start_date as BackupStartDate
,bs.backup_finish_date as BackupFinishDate 
,CAST(bs.backup_size / 1024 / 1024 as DECIMAL(18,2)) as BackupSizeMB
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf
	ON bs.media_set_id = bmf.media_set_id
WHERE bs.type IN('D', 'L') -- D = Full, I = Differential, L = Log, F = File or filegroup	
AND bs.database_name = 'AdventureWorks' -- Modify the database name here
AND (bs.backup_start_date >= CAST(CONVERT(VARCHAR(25), GETDATE() - 1, 112) AS DATETIME) 
	AND bs.backup_start_date < CAST(CONVERT(VARCHAR(25), GETDATE(), 112) AS DATETIME)) -- Show yesterday's backups
ORDER BY bs.backup_finish_date desc	

REFERENCES:

backupset
http://msdn.microsoft.com/en-us/library/ms186299(v=sql.105).aspx

backupmediafamily
http://msdn.microsoft.com/en-us/library/ms190284(v=sql.105).aspx

 

How to run full backups for ad hoc emergency roll-outs

Pop Quiz Hotshot: The development team wants you to run an emergency roll-out. Now! The email or ticket has been carbon copied (CC) to your boss and your boss’s boss. You know that possible rollback may be needed if the scripts don’t go as planned (I’m sure that never happened to you). You have requested or collaborated with the development team to notify the users and the required parties of the emergency roll-out and asked them to sign out of the applications or the corresponding systems. The databases are relatively small in size (total < 5 GB). Both you and the development understand that it is a best practice to take the required full backups prior to the roll-out. You must make sure the full backups are accounted for as soon as you can. What do you do? What do you do?

1. Find out the backup location you can run the backups. Modify the path in the following script in line #11.
2. Know what databases the roll-out will affect. Include or exclude the database(s) in line #30.

Don’t worry about backup compression. It is built-in into the script for the editions and versions that have the backup compression capability. Good luck and here we go:

-- Full database(s) backup(s) with date and time appended
-- Change the backup path in line 11
-- Include or exclue the database(s) in line 30

DECLARE @db VARCHAR(123) -- database name, 123 is the limit
DECLARE @path VARCHAR(125) -- backup path
DECLARE @filename VARCHAR(250) -- backup filename
DECLARE @filedate VARCHAR(25) -- append to the backup filename

-- set the backup path
SET @path = 'C:\SQLBACKUPS\' -- set it to the desired path

-- set the date and time format to YYYYMMDD_HHMMSS
SELECT @filedate = CONVERT(VARCHAR(20),GETDATE(),112) +'_'+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

-- create a temp table to store the databases that require backups
IF OBJECT_ID('TEMPDB..#DatabaseBackups') IS NOT NULL
DROP TABLE #DatabaseBackups

CREATE TABLE #DatabaseBackups
(
	ID int identity (1,1)
	, DatabaseName varchar(128) not null
)

-- load the database(s) into the temp table
INSERT INTO #DatabaseBackups (DatabaseName)
SELECT name
FROM SYS.DATABASES
WHERE name not in ('tempdb') -- databases to exclude from the backups
--WHERE name in ('databases to be included')

-- backups the databases
DECLARE @id TINYINT
DECLARE @max TINYINT
DECLARE @dbname VARCHAR(128) -- to store the database name

SELECT @id = 1
	,@max = MAX(ID)
FROM #DatabaseBackups

-- find the SQL Server Edition and Version
DECLARE @edition VARCHAR(50) -- backup compression available for SQL Server 2008 enterprise and 2008 R2 or above
DECLARE @sqlVersion DECIMAL(4,2) -- 8.00, 9.00, 10.00, 10.50
DECLARE @compression VARCHAR(17) -- enable compression
DECLARE @sqlcmd VARCHAR(250)
SELECT @edition = LEFT(CAST(SERVERPROPERTY('EDITION') AS VARCHAR),3)
SELECT @sqlVersion = LEFT(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR), 4)
SELECT @compression = 'WITH COMPRESSION'

WHILE (@id <= @max) 
BEGIN 	
  SELECT @dbname = DatabaseName 	
  FROM #DatabaseBackups 	
  WHERE ID = @ID 	 	

  SET @filename = @path + 'Full_' + @dbname + '_' + @filedate + '.bak' 			 	

  IF ((@edition = 'Dev' or @edition = 'Ent') AND (@sqlVersion >= 10.00))
      SELECT @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''' + @filename + ''' ' + @compression
  ELSE
      SELECT @sqlcmd = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''' + @filename + ''' '

  EXEC(@sqlcmd)

  SET @id = @id + 1 -- increase the counter by 1
END

Speed – Pop Quiz Hotshot Clip: