Category Archives: Backups

All about Backups, running backups, querying backups, reporting against backups

Location of all last X backups – all DBs

Backups, restore

Check the location of backups for the last 7 days for all databases, or narrow down to a single one
Useful to see where the backups are going, and if there are any failed backups during the last week 

/*
DESCRIPTION List location of last X days backup for all databases.
CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/
 

DECLARE @days INT
DECLARE @dbname sysname

SET @days = 7			-- Number of days
SET @dbname = NULL		-- Specify database like 'database1' . If Null it'll do all

SELECT
 serverproperty('servername')
, serverproperty('productversion')
, serverproperty('productlevel')
, bs.database_name
, datediff(ss,bs.backup_start_date, bs.backup_finish_date) 'Duration_Sec'
, bs.backup_finish_date
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, ceiling(bs.backup_size/10214/1021) 'BK_Size_MB'
--, ceiling(bs.compressed_backup_size/10214/1021) 'BK_Size_MB' -- Only on 2008 and up
, bmf.physical_device_name
--, bmf.device_type
--, bs.user_name
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
-- and msdb..backupset.type = 'D'
and bs.database_name like coalesce(@dbname,'%')
ORDER BY
bs.database_name,
bs.backup_finish_date

Generate script to backup all databases

Backups, Script Generator

This is a SCRIPT generator. It does not execute code.

The output can be used as a script to backup databases, enter into a SQL job to run backups, etc.

  

 /*
DESCRIPTION:
The following code generates a SQL script to do a FULL backup ALL databases to the specified location. It also creates a folder for each database on that location
CONFIGURATION
Just change the value for @LOCATION. example: SET @LOCATION = 'c:\temp'
If @EachFolder = 1 it makes a subfolder for each databases
If @EachFolder = 0 it does not. all BAK in same folder
Compatibility list: MSSQL2012 MSSQL2008 MSSQL2005
DOES NOT WORK MSSQL2000
*/
DECLARE @LOCATION VARCHAR(2000)
SET @LOCATION = 'F:\SQL Backups\BeforeUpgrade'
DECLARE @EachFolder BIT
-- If @EachFolder = 1 it makes a subfolder for each databases
-- If @EachFolder = 0 it does not. all BAK in same folder
SET @EachFolder = 0
-- ***** DO NOT MODIFY BELOW THIS LINE ********
DECLARE @mydb VARCHAR(2000)
DECLARE @rowcount INT
DECLARE @table table (rownum int identity(1,1),name sysname)
 
 
USE MASTER
PRINT 'DECLARE @loc VARCHAR(2000)'
PRINT 'set @loc = '''+@LOCATION+''''
PRINT 'DECLARE @mydb VARCHAR(2000)'
PRINT 'DECLARE @subdir VARCHAR(2000)'
PRINT 'DECLARE @sufixbk VARCHAR(20)'
PRINT 'DECLARE @full_bk_name VARCHAR(2000)'
PRINT 'DECLARE @bk_name VARCHAR(2000)'
PRINT 'DECLARE @msg VARCHAR(2000)'
PRINT 'DECLARE @backupSetId AS INT'
PRINT ''
 
-- Optional : you can modify the SELECT list for the while loop
-- This SELECT statement determines what databases to generate the backup scripts
 
INSERT INTO @table
SELECT name FROM sysdatabases WHERE VERSION is not null
and databasepropertyex(name,'Status') = 'online'
and name not in ('tempdb')
-- and name in ('database1','database2','database3')
 
SET @rowcount =1
WHILE @rowcount < = (select count(name) from @table)
BEGIN
 
SELECT @mydb = name from @table where rownum =@rowcount
 
 
PRINT '--*************** processing '+@mydb+' ************'
PRINT ' '
PRINT 'set @subdir = '''+ @mydb+''''
IF (@EachFolder=1) PRINT 'set @loc = @loc +''\'' +@subdir + ''\'''
PRINT 'set @sufixbk = CONVERT(VARCHAR(20),GETDATE(),112)'
PRINT 'set @bk_name = '''+@mydb+ '''+''_'' + @sufixbk + ''.bak'''
PRINT 'set @full_bk_name = @loc + ''\'' + @bk_name'
IF (@EachFolder=1) PRINT 'EXECUTE master.dbo.xp_create_subdir @loc'
PRINT 'BACKUP DATABASE ['+@mydb+'] TO DISK = @full_bk_name WITH NOFORMAT, NOINIT, NAME =@bk_name, SKIP, REWIND, NOUNLOAD, STATS = 10'
PRINT ',BUFFERCOUNT = 2200, BLOCKSIZE = 65536, MAXTRANSFERSIZE=2097152'
PRINT 'select @backupSetId = position from msdb..backupset where database_name='''+@mydb+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='''+@mydb+''')'
PRINT 'set @msg =''Verify failed. Backup information for database ['+@mydb+'] not found.'''
PRINT 'if @backupSetId is null begin raiserror(@msg, 16, 1) end'
PRINT 'RESTORE VERIFYONLY FROM DISK = @full_bk_name WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
PRINT ' '
 
SET @rowcount = @rowcount+1
END