Month: December 2013

List of all the latest existing backups to be restored

When we need to restore a database, the very first thing we need to find is list of backups that needs to be restored.Of course, you might need to take tail log backup and restore at the very end based on the situation you are in. The query below will list all the EXISTING backups of a particular database to be restored

I have a similar post here,however, the difference between these two scripts is that the below query will list only the backups that needs to be restored where as the previous one will list all the backups after the latest full backup.

The query first checks for latest full backup and latest differential backup, if any, and all the log backups after the latest differential or full backup.I am eliminating the Copy_Only Backups, so the restore sequence chain is not interrupted.

Note:The query will only work on SQL 2005 and above versions.

declare @DatabaseName varchar(200)
set @DatabaseName = 'DatabaseName';

select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and backup_set_id=(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type='D' and is_copy_only=0) and is_copy_only=0
union
select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name
from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and
A.backup_set_id =
(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type in ('I','D') and is_copy_only=0) and type = 'I'
union
select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and backup_set_id>
(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type in ('I','D') and is_copy_only=0) and type = 'L' and is_copy_only=0

Advertisement

Report file sizes in proper units

Adding to my previous post about reporting time in proper units, I made a similar function to report file size in appropriate units. Again, the issue here is same as in my previous post (i.e. some backups are relatively small and some are really big).So,I was reporting all file sizes in bytes and it is kind of hard to understand the big numbers(It is easy understand 52428800 as 50.00 MB). So, to fix this, I made a scalar function which takes input in bytes and returns output in its nearest lower Bytes/KB/MB/GB/TB units.

Below is the function:

USE [msdb]
GO
CREATE FUNCTION [dbo].[fnBackupSize](@backupsize decimal(20,3))
RETURNS varchar(20)
AS
Begin
DECLARE @size varchar(20)
IF (@backupsize<1024)
Begin
Select @Size = cast(@backupsize as varchar(10)) +' Byte(s)'
End
IF (@backupsize>=1024 and @backupsize<=1048576)
Begin
Select @Size = cast(cast(@backupsize/1024 as decimal(20,3)) as varchar(10)) +' KByte(s)'
End
IF (@backupsize>=1048576 and @backupsize<=1073741824)
Begin
Select @Size = cast(cast(@backupsize/1048576 as decimal(20,3)) as varchar(10)) +' MByte(s)'
End
IF (@backupsize>=1073741824 and @backupsize<=1099511627776)
Begin
Select @Size =cast(cast(@backupsize/1073741824 as decimal(20,3)) as varchar(10)) +' GByte(s)'
End
IF (@backupsize>=1099511627776)
Begin
Select @Size =cast(cast(@backupsize/1099511627776 as decimal(20,3)) as varchar(10)) +' TByte(s)'
End

Return @size

End
Output: Select msdb.dbo.fnBackupSize(53687091200) as [Size]
Capture1
Select msdb.dbo.fnBackupSize(53680) as [Size]
Capture

Report Time in proper format

I was working on a restore script to test our backups and also, made a reporting services report which will list out what backups have been used to restore the database,how long each one took and size of each backup. While the report was very helpful, it was kind of very hard to understand the restore time and backup sizes. The main reason for this is because some databases are kind of very small compared to others,so,their backup sizes and restore times are much lesser than others.

Below post is on how I addressed restore time issue in my report.I also made similar function to report on backup size and you can read that HERE.

I was initially reporting the time in secs but as mentioned the bigger databases were taking almost 2 hrs to restore, so, restore time shows this as 7200. It is easy to understand 7200 seconds as 2 hrs. So, to fix this, I made a scalar function which takes input time in seconds and returns time in hh:mm:ss format.

For Example: If restore took 240 seconds, it will return 00:04:00.
If restore took 7200 seconds, it will return 2:00:00

Below is the function:

USE [MSDB]
GO
CREATE FUNCTION [dbo].[fncTime] (@TimeIn int)
RETURNS varchar(11)
AS
Begin
declare @Hr int
declare @hold int
declare @TimeOut varchar(11)
IF (@TimeIn<60)
Begin
select @Timeout = '00:00:'+right('0'+cast(@TimeIn as varchar(2)),2)
End
IF (@TimeIn>=60 and @TimeIn<=3600)
Begin
select @TimeOut = '00:'+right('0'+cast((@TimeIn/60) as varchar(2)),2) +':'+right('0'+cast((@TimeIn%60) as varchar(2)),2)
End
IF (@TimeIn>=3600)
Begin
select @Hr=@TimeIn/3600
select @Hold =@TimeIn%3600
IF (@Hold)>0
Begin
select @TimeOut = case when @Hold>60 then cast(@hr as varchar(5)) +':'+
right('0'+cast((@Hold/60) as varchar(2)),2)+':'+
right('0'+cast((@Hold%60) as varchar(2)),2)
else cast(@hr as varchar(5)) +':00:'+right('0'+cast((@Hold%60) as varchar(2)),2) End
End
Else
Begin
Select @TimeOut = cast(@hr as varchar(5)) +':00:00'
End
End
RETURN @TimeOut
End

Output : select msdb.dbo.[fncTime](45085) as [RestoreDuration(hh:mm:ss)]

Capture