Category Archives: restore

All about Restores, running restore operatations, querying backups, reporting against last restores, etc

Location of very last Backups — All Databases

October 22, 2017Backups, Reports, restoreMiguel

One script with some CTE, but two ways to show the info.

A narrow view:

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
--ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
--ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
--top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
--ORDER BY  s.backup_finish_date desc
)
, final as
(
select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff' 'bk_type', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1

UNION ALL

select c.database_name, c.recovery_model, 'tlog' 'bk_type', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1
)
select * from final
order by 1,3

A wide view


 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
)
, Diffbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
)
, TlogBK as (
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
)

select a.database_name, a.recovery_model
	, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
	, 'DIFF' 'DIFF_BK', b.physical_device_name,b.Seconds_TimeTaken,b.age_in_days, b.backup_start_date
	,  'TLOG' 'TLOG_BK', c.physical_device_name,c.Seconds_TimeTaken,c.age_in_days, c.backup_start_date
from fullbk a
left join Diffbk b on a.dbid = b.dbid
left join TlogBK c on a.dbid = c.dbid

where 1=1
and a.number  = 1 
and coalesce(b.number,1)=1
and coalesce(c.number,1)=1

union all
select Distinct
    convert(varchar(30),name) as DBname
    ,DATABASEPROPERTYEX(name,'recovery') 'RecoveryModel'
    ,'NO BACKUPS RECORDED ON MSDB',NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Location of very last Backups

October 22, 2017Backups, Reports, restoreMiguel

This script will list the location of the last Full, Differential and Tlog (if exists)

I use three CTE and at the end I use a UNION all

the last two select statements are joins so it displays NULL if either Diff or Tlog do not exist

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012
MSSQL2014

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = db_name()
--SET @dbname = 'ELMO'
set @LastXdays = '30'
 
;with fullbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
ORDER BY  s.backup_finish_date desc
)

select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.bk_type,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1		 

UNION ALL
select c.database_name, c.recovery_model, 'tlog', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1

Resynchronize Orphaned Users

December 13, 2014restoreMiguel

This issue happens often when restoring databases from one server to another.

Let's say we have a database A on Server X.

If database A has a user user1. It most likely maps to a SQL login user1, with its own SID (unique identifier).

If we restore database A on server Y, it will attempt to map the user1 to a SQL login on server Y, by using the SID. Since the SQL login user1 exists on server Y, but with a different SID, the mapping doesn't happen, hence the user on the database gets orphaned.

We fix that by running


EXEC sp_change_users_login 'Update_one', @UserName, @UserName

However, below we have a cursor that identifies orphaned users and synchronized them.

SET NOCOUNT ON
USE [database name goes here]

--Variable to hold current UserName to process
 
DECLARE @UserName nvarchar(255)
DECLARE @rowcount int
 
--Create Table to hold the orphan Users

DECLARE @OrphanUserTable table
(rownum int identity(1,1), UserName sysname)

insert into @OrphanUserTable 
SELECT a.name 
FROM sysusers a
inner join master..syslogins b on a.name =b.name
WHERE       a.issqluser = 1
		AND a.sid is not null
		AND a.sid <> 0X01
		AND suser_sname(a.sid) is null
ORDER BY a.name

IF (select count(UserName) from @OrphanUserTable)=0
PRINT 'No orphan users found'
 
SET @rowcount = 1
WHILE @rowcount < = (select count(UserName) from @OrphanUserTable)
BEGIN
 
PRINT @UserName + ' user name being resynced'
--Re-sync the currently selected orphan user
 
EXEC sp_change_users_login 'Update_one', @UserName, @UserName


--Get Next Orphan User
 
SET @rowcount = @rowcount + 1

END

Restore Generator for all BAK files in one folder

November 11, 2014restore, Script GeneratorcommandShell, cursor, TableVariableMiguel

Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual path to the folder where the BAK files are kept, and we generate a resotre script using the RESTORE HEADERONLY and FILELISTONLY operations for all databases on that folder.

/*
DESCRIPTION Script Generator
It creates the RESTORE scripts for all backup (bak) files on a directory
and lists the space needed AT THE BOTTOM of output.
 
Configuration At the top:
 
Compatibility list:
MSSQL2008
MSSQL2012
 
 
Does not work:
MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modified.
 
*/
set nocount on
 
DECLARE @pathname VARCHAR(1024)
DECLARE @DataPath NVARCHAR(3000)
DECLARE @TlogPath NVARCHAR(3000)
DECLARE @RELOCATE INT
DECLARE @OVERRIDE INT
 
 
--************** CONFIGURATION **************
 
 
-- Set the pathname for the location of the backup files
-- must end wit ha backslash \
 
set @pathname = '"t:\bk"'

-- Overrider: Say 1 if the database already exist and needs to be over written
-- Overrider: Say 0 if the database is new to this server
 
set @OVERRIDE = 1
 
--RELOCATE: If you want to relocate the data files, say 1 and fill in the following:
-- if not, say 0 and skip.
 
set @RELOCATE = 1
-- Data files must end wit ha backslash
set @DataPath = 'E:\MSSQL\DATA\'
-- Tlog Files must end wit ha backslash
set @TlogPath = 'F:\TlogFolder\'
 
--************** END CONFIGURATION **************
 
 
/*
**************************************************
**************************************************
Declare Variables DO NOT MODIFY BELOW
**************************************************
**************************************************
*/
DECLARE @databasename NVARCHAR(1000)
DECLARE @physicalName NVARCHAR(1000)
DECLARE @filename NVARCHAR(1000)
DECLARE @filepath NVARCHAR (1000)
DECLARE @temp1 NVARCHAR (1000)
DECLARE @temp2 NVARCHAR (1000)
DECLARE @temp3 NVARCHAR (1000)
DECLARE @move NVARCHAR (4000)
DECLARE @CMD VARCHAR(512)
DECLARE @bksizeTotal BIGINT
DECLARE @DATAsizeTotal BIGINT
DECLARE @TLOGsizeTotal BIGINT
 
 
-- initialize size counters
set @bksizeTotal = 0
set @DATAsizeTotal = 0
set @TLOGsizeTotal = 0


-- Create Tables to store data
  IF OBJECT_ID('tempdb..#filelist') IS NOT NULL
DROP TABLE #filelist
IF OBJECT_ID('tempdb..#header') IS NOT NULL
DROP TABLE #header

CREATE TABLE #filelist
(
LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(128),Size NUMERIC(20,0)
,MaxSize NUMERIC(20,0),Fileid BIGINT, CreateLSN numeric(25,0),
DropLSN numeric(25, 0),UniqueID UNIQUEIDENTIFIER,ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),BackupSizeInBytes BIGINT,SourceBlocSize INT,FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,IsPresent BIT
)
CREATE TABLE #header
(
BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT,
Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128)
,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT
,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0),
DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME
,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT,
SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128)
,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128)
,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT
,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit
, isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60),
dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER
,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER
)

 
DECLARE @Version numeric(18,10)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

-- If this is SQL2008 or  higher, add more columns to support compression
IF @VERSION >= 10
BEGIN
ALTER TABLE #filelist
ADD TDEThumbPRINT bit
 
ALTER TABLE #header
ADD CompressedBackupSize BIGINT 
END
-- If this is SQL2012 or  higher, add more columns to support contained database
IF @Version >= 11
BEGIN
ALTER TABLE #Header
ADD containmnet bit
END

IF OBJECT_ID('tempdb..#CommandShell') IS NOT NULL
DROP TABLE #CommandShell


CREATE TABLE #CommandShell
(	Line VARCHAR(1024)	)
-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
 
-- these variables keep track if we changed these options, we'll change them back before finishing
 

DECLARE @chkOLE as sql_variant
DECLARE @adv_opt as sql_variant
DECLARE @op_ch1 as INT
DECLARE @op_ch2 as INT
print '/* 
Ignore this commented block'
set @op_ch1=0
set @op_ch2=0
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE;
set @op_ch1 = 1
end
select @chkOLE = value from sys.configurations where name = 'xp_cmdshell'
if @chkOLE = 0
begin
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE;
set @op_ch2 = 1
end


-- Adds CMD compatible quotation marks if there is none
IF ((SELECT CHARINDEX('"',@pathname))=0)
SET @pathname = '"'+@pathname+'"'

-- Set the DIRECTORY to search for backups
SET @CMD = 'DIR ' + @PathName + ' *.bak /A /B'
 
-- Populate table with the files in directory
 
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD
-- Delete lines not containing a filename
DELETE FROM #CommandShell WHERE Line is null
 
-- checking if we change settings, and reverting them
 
if @op_ch2 = 1
begin
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE;
end
if @op_ch1 = 1
begin
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE;
end
print '
Ignore this commented block*/ 
'

IF EXISTS (select TOP 1 (Line) from #CommandShell where Line = 'Access is denied.')
BEGIN
Print '
Either the SQL Server does not have access to the path, or it does not exist'
print @pathname
GOTO EOF
END
--- end CMDshell

-- Removing double quotations from pathname
set @pathname = REPLACE(@pathname,'"','')

-- Makes sure path ends with a slash, and adds one if needed
IF ((SELECT CHARINDEX('\',reverse(@pathname)))>1)
SET @pathname = @pathname+'\'

-- Removing Last line of table
delete from #CommandShell where Line = 'File Not Found' 

-- First Cursor, grabs a file, and extracts the database name
-- **********************************************
-- **********************************************
-- ********************************************** 
DECLARE LIST CURSOR FOR
SELECT @pathname+Line FROM #CommandShell
 
 
OPEN LIST
FETCH NEXT from LIST INTo @temp3
WHILE (@@fetch_status = 0)
BEGIN
 
-- populate the tables with the BAK file info
insert #header
exec ('restore headeronly from disk = ''' + @temp3 + '''')
 
insert #filelist
exec ('restore filelistonly from disk = ''' + @temp3 + '''')
 
-- Grabs DATABASE name for the BAK file
 
select @databasename=DatabaseName from #header
 
-- start adding size info
select @bksizeTotal=@bksizeTotal+backupsize from #header
 
set @move = '
-- *************************************************
-- Restore Script For Database ' + @databasename + '
-- *************************************************
'
if @OVERRIDE=1
set @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
set @move=@move+
'
RESTORE DATABASE ['+@databasename+']
FROM DISK ='''+@temp3+'''
WITH FILE = 1,
'
 
-- Second Cursor for data files, nested.
 
DECLARE cur CURSOR FOR
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) from #filelist
where [type]= 'D'
 
OPEN cur
FETCH NEXT from cur INTO @temp1 ,@temp2
WHILE (@@fetch_status = 0)
BEGIN
 
-- start adding size info
SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist WHERE LogicalName =@temp1
 
-- Set the new path
SET @temp2 = @DataPath+@temp2
 
-- If Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist
WHERE LogicalName = @temp1
 
-- concatenante the move portion of the restore script.
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
FETCH NEXT from cur INTo @temp1 ,@temp2
END
CLOSE cur
DEALLOCATE cur
 
-- Third cursor for Tlog files. Nested.
DECLARE cur cursor for
SELECT LogicalName, reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist
WHERE [type]= 'L'
 
OPEN cur
FETCH NEXT FROM cur INTo @temp1 ,@temp2
WHILE (@@fetch_status = 0)
BEGIN
 
-- start adding size info
select @TLOGsizeTotal=@TLOGsizeTotal+size from #filelist where LogicalName =@temp1
 
-- If Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName from #filelist
where LogicalName = @temp1
 
 
set @temp2 = @TlogPath+@temp2
set @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
FETCH NEXT from cur INTo @temp1 ,@temp2
END
close cur
deallocate cur
print @move +'
NOUNLOAD, REPLACE, STATS = 10
GO'
if @OVERRIDE=1 set @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER
GO'
 
 
truncate table #filelist
truncate table #header
FETCH NEXT FROM LIST INTO @temp3
END
CLOSE LIST
DEALLOCATE LIST


-- **********************************************
-- **********************************************
-- **********************************************
 
print ' /* ***********************--'
 
Print 'Total Size Needed in all disks '+ cast (@BkSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
Print 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
Print 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
print ' *********************** */'
 
 


--Clean up
drop table #CommandShell
drop table #filelist
drop table #header


-- */

EOF:

Restore Script Generator one DB

November 11, 2014restore, Script GeneratorMiguel

Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual BAK file and its path, we read the backup file using the RESTORE HEADERONLY and FILELISTONLY operations.

Make sure your account has privileges to read the backup if its stored on a network location.

 /*
DESCRIPTION Script Generator
It creates the RESTORE scripts for ONE backup (bak) file
and lists the space needed.
Configuration At the top:
Author: Miguel Quintana
Compatibility list: MSSQL2016 MSSQL2012 MSSQL2008 MSSQL2005
not tested on 2014
Does not work:
MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modIFied.
*/
SET NOCOUNT ON
--************** CONFIGURATION **************
DECLARE @path VARCHAR(1000)
DECLARE @DataPath NVARCHAR(3000)
DECLARE @TlogPath NVARCHAR(3000)
DECLARE @RELOCATE INT
DECLARE @OVERRIDE INT
-- Enter ful path to the BACK file
SET @path = 'k:\MSSQL11.SQL2012\MSSQL\Backup\AMTENTSQL1202T$SQL2012\Clratsdb7\FULL\AMTENTSQL1202T$SQL2012_Clratsdb7_FULL_20150501_010000.bak'
-- Overrider: Say 1 IF the database already exist and needs to be over written
-- Overrider: Say 0 IF the database is new to this server
SET @OVERRIDE = 1
--RELOCATE: IF you want to relocate the data files, say 1 and fill in the following:
-- IF not, say 0 and skip.
SET @RELOCATE = 1
-- Data files must end wit ha backslash
SET @DataPath = 'E:\MSSQL\DATA\'
-- Tlog Files must end wit ha backslash
SET @TlogPath = 'F:\TlogFolder\'
--************** END CONFIGURATION **************
DECLARE @databasename NVARCHAR(100)
DECLARE @physicalName NVARCHAR(100)
DECLARE @filename NVARCHAR(100)
DECLARE @filepath NVARCHAR (1000)
DECLARE @temp1 NVARCHAR (100)
DECLARE @temp2 NVARCHAR (100)
DECLARE @rownum INT
DECLARE @move NVARCHAR (1000)
DECLARE @bksizeTotal BIGINT
DECLARE @DATAsizeTotal BIGINT
DECLARE @TLOGsizeTotal BIGINT
-- initialize size counters
SET @bksizeTotal = 0
SET @DATAsizeTotal = 0
SET @TLOGsizeTotal = 0
IF OBJECT_ID('tempdb..#filelist') IS NOT NULL
DROP TABLE #filelist
IF OBJECT_ID('tempdb..#header') IS NOT NULL
DROP TABLE #header
CREATE TABLE #filelist
(
LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(128),Size NUMERIC(20,0)
,MaxSize NUMERIC(20,0),Fileid BIGINT, CreateLSN numeric(25,0),
DropLSN numeric(25, 0),UniqueID UNIQUEIDENTIFIER,ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),BackupSizeInBytes BIGINT,SourceBlocSize INT,FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,IsPresent BIT
)
CREATE TABLE #header
(
BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT,
Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128)
,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT
,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0),
DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME
,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT,
SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128)
,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128)
,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT
,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit
, isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60),
dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER
,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER
)


DECLARE @Version numeric(18,10)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

-- If this is SQL2008 or  higher, add more columns to support compression
IF @VERSION >= 10
BEGIN
ALTER TABLE #filelist
ADD TDEThumbPRINT bit
 
ALTER TABLE #header
ADD CompressedBackupSize BIGINT 
END
-- If this is SQL2012 or  higher, add more columns to support contained database
IF @Version >= 11
BEGIN
ALTER TABLE #Header
ADD containmnet bit
END

-- If this is SQL2016 or  higher, add more columns to support contained database
IF @Version >= 13
BEGIN
ALTER TABLE #Header
ADD KeyAlgorithm nvarchar(32)
ALTER TABLE #Header
ADD EncryptorThumbprint varbinary(20)
ALTER TABLE #Header
ADD EncryptorType nvarchar(32)

ALTER TABLE #filelist
add SnapshotURL nvarchar(360)
END
 

 
-- Insert the data in our tables
INSERT #header
EXEC ('restore headeronly FROM disk = ''' + @path + '''')
INSERT #filelist
EXEC ('restore filelistonly FROM disk = ''' + @path + '''')
SELECT @databasename=DatabaseName FROM #header
SET @move = '
-- *************************************************
-- Restore Script For Database ' + @databasename + '
-- *************************************************
'
IF @OVERRIDE=1 -- if override, place database on single_user mode
SET @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 
SET @move=@move+
'
RESTORE DATABASE ['+@databasename+']
FROM DISK ='''+@path+'''
WITH FILE = 1, -- assumes only one database backup per media family
'
-- First WHILE for data files.
DECLARE @DataFileTable table
(rowcnt INT identity(1,1), LogicalName varchar(4000), physpath varchar(4000))
 
INSERT INTO @DataFileTable
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist         --'
where [type]= 'D'
SET @rownum =1
WHILE @rownum < = (select count(*) from @DataFileTable)
BEGIN
SELECT @temp1=LogicalName, @temp2=physpath FROM @DataFileTable WHERE rowcnt = @rownum
-- start adding size info
SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist where LogicalName =@temp1
-- SET the new path
SET @temp2 = @DataPath+@temp2
-- IF Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist where LogicalName = @temp1
-- concatenante the move portion of the restore script.
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
 
set @rownum = @rownum+1
END
-- Second cursor for Tlog files.
DECLARE @TlogFileTable table
(rowcnt INT identity(1,1), LogicalName varchar(4000), physpath varchar(4000))
 
INSERT INTO @TlogFileTable
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist
where [type]= 'L'
SET @rownum =1
WHILE @rownum <= (select count(*) from @TlogFileTable)
BEGIN
SELECT @temp1=LogicalName, @temp2=physpath FROM @TlogFileTable WHERE rowcnt = @rownum
-- start adding size info
SELECT @TLOGsizeTotal=@TLOGsizeTotal+size FROM #filelist where LogicalName =@temp1
-- IF Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist
where LogicalName = @temp1
SET @temp2 = @TlogPath+@temp2
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
 
SET @rownum = @rownum+1
END
 
 
SET @move = @move +'
NOUNLOAD, REPLACE, STATS = 10
GO
'
IF @OVERRIDE=1
SET @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER
GO
SET @BKSizeTotal = @DATASizeTotal + @TLOGSizeTotal
'
PRINT @move
PRINT ' /* ***********************--'
PRINT 'Total Size Needed in all disks '+ cast (@BkSizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/1024/1024/1024 as varchar(10))+ ' GB'
PRINT ' --'
PRINT ' *********************** */'
DROP TABLE #filelist
DROP TABLE #headervers

Check When The Last Restore Was Performed

November 11, 2014restore, Script GeneratorMiguel

This script returns information about the last time a database has been restored on this server.

You can either search all databases by leaving the parameter NULL or search for a single specific database.

The other configurable item is the time frame to look for. It defaults to 30 days, and it should be entered as a negative number (i.e., -7)

This script queries the MSDB database, so it'll return what's on the history only

Source: MSSQL SQL Tips

/*
DESCRIPTION:
Queries the RESTORE operation that have taken place in the last 30 days
CONFIGURATION
 
Compatibility list: MSSQL2012 MSSQL2008 MSSQL2005
DOES NOT WORK
UNKNOWN: MSSQL2000
*/
  
DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -30 --previous number of days, script will default to 30
SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To],
bs.server_name AS [CameFromServer]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name like ISNULL(@dbname, '%') --if no dbname, then return all
and rf.file_number = 1                     --- Only lists MDF
ORDER BY rsh.restore_history_id DESC
GO

Restore Full Backup and Tlog Generator

November 11, 2014restore, Script GeneratorTableVariableMiguel

This script is a classic dynamic TSQL.

It uses the MSDB information to build a Restore script with all the backups available on MSDB.

/*
DESCRIPTION:
Looks inside MSDB history information for a particular Database.
Gets the newest FULL backup and Tlogs
and generates a script to restore the Full BK + Diff+ Tlogs
Assumes the backup files are still in original location.
 
Configuration:
@dbname = the DB that we want to restore
@newdbname = The name of the new DB. If no name change, make this values the same, or leave null.
 
Compatibility list: MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000
 
*/

SET NOCOUNT ON
DECLARE @dbname VARCHAR(500)
DECLARE @newdbname VARCHAR(500)
SET @dbname = 'SALES_ODS'
SET @newdbname = NULL			-- if target DB name will be the same, leave NULL


/*
do not modify below
*/

set @newdbname = coalesce(@newdbname,@dbname)


-- gets the latest FULL backup and all Tlog after that.

DECLARE @bkfiles table
( id int identity(1,1),physical_device_name nVARCHAR (512), [type] char)

INSERT @bkfiles (physical_device_name, [type])
	SELECT m.physical_device_name, s.[type]
	FROM msdb.dbo.backupSET s
	inner join msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
	WHERE
	 s.database_name like @dbname
	 and s.[backup_finish_date] >=
		(SELECT MAX(msdb.dbo.backupSET.backup_finish_date)
		FROM msdb.dbo.backupmediafamily
		INNER JOIN msdb.dbo.backupSET ON msdb.dbo.backupmediafamily.media_SET_id = msdb.dbo.backupSET.media_SET_id
		WHERE msdb..backupSET.type = 'D' and msdb.dbo.backupSET.database_name = @dbname and is_copy_only = '0'
		GROUP BY msdb.dbo.backupSET.database_name
		)
and is_copy_only = '0' -- does not include copy_only backups
 
-- Remove Tlogs and Diff before very last Diff from the table
DELETE FROM @bkfiles
	WHERE ID < (SELECT TOP 1 (ID) FROM @bkfiles
				WHERE TYPE = 'I' ORDER BY ID DESC) 
		and type <> 'D'
-- creates table to hold our Tsql scripts

DECLARE @cmd table
(id int identity(1,1),script NVARCHAR (4000))

INSERT @cmd (script)
	Select 'RESTORE DATABASE ['+@newdbname+'] FROM DISK =N'''+physical_device_name+''''
	from @bkfiles
	where [type]='D'

INSERT @cmd (script)
SELECT 'WITH FILE = 1,'
 
-- Prepare the MOVE Statements

select 'MOVE N'''+name+''''+' to N'''+physical_name+''','  
FROM sys.master_files where database_id=db_id(@dbname) and type_desc = 'ROWS'
 
insert @cmd (script)
select ' NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'

-- Restores the Differential
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'I'
 
 
-- Build the Tlog chain
insert @cmd (script)
select 'RESTORE LOG ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'L'

-- Set DB with recovery mode
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+'] WITH RECOVERY'
-- Display the info
select script from @cmd order by id

How long backups will take

October 29, 2014Backups, Performance-Scripts, restoredmvMiguel

Very simple script that queries a DMV to get an estimate of backup duration

/*
DESCRIPTION:    Queries the dm_exec requests and gives you an estimate of how long a backup/restore request is going to take
Configuration:    No Configuration needed
 
Compatibility list:
MSSQL2005 MSSQL2008  MSSQL2008R2 MSSQL2012

DOES NOT WORK MSSQL2000
Update Log:
*/
USE MASTER
SELECT
session_id as SPID
, r.command
, a.text AS Query
, start_time
, percent_complete
, dateadd(second,estimated_completion_time/1000, getdate())
                       as estimated_completion_time
, estimated_completion_time/1000/60 as time_left_mins
, r.blocking_session_id
, r.wait_type
FROM SYS.DM_EXEC_REQUESTS r
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(r.sql_handle) a
WHERE 1=1
and r.command in
('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE LOG')

Location of last X backups – Single DB

October 29, 2014Backups, restoreMiguel

Check the location of backups for the last 7 days for a single database. Useful to see where the backups are going, and if there is any fail backup. Very similar to
http://www.cookingsql.com/2014/10/backups-location-of-all-last-x-backups-all-dbs/

 /*
DESCRIPTION    List location of  all backup for a single database.
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = 'master'
set @LastXdays = '30'
  
SELECT
    serverproperty('servername')
    ,s.database_name
    ,s.is_copy_only
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
 
--  ,cast(s.compressed_backup_size/1000000 as varchar(24))+' '+'MB' as bkSize  -- only on SQL2008
    ,CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(6))+' '+'Seconds' TimeTaken
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,CASE s.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        END as BackupType
    -- ,s.recovery_model    --- does not work 2000
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
ORDER BY  s.backup_finish_date desc

Location of all last X backups – all DBs

October 28, 2014Backups, restoreMiguel

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

Cooking SQL

A place for my scripts, tips, and occasional pictures of food