Warning : This section is under contruction
Script 1: Get Last backups
select @@servername as ServerName, A.*
from
( select
ROW_NUMBER() OVER ( PARTITION BY sd.name, bs.Type ORDER BY bs.backup_start_date DESC ) AS 'RowNumber',
sd.name as DBName,
sd.recovery_model_desc as RecoveryModel,
bs.Type,
bs.backup_start_date,
bs.backup_finish_date,
datediff(hour, bs.backup_finish_date, getdate()) as ElaspsedTimeInHour,
datediff(minute, bs.backup_start_date, bs.backup_finish_date) as DurationInMin,
bm.Physical_Device_name
from master.sys.databases sd
left join msdb.dbo.backupset bs on db_id(bs.database_name) = sd.database_id -- on bs.database_name = sd.name collate SQL_Latin1_General_CP1_CI_AS
left join msdb.dbo.backupmediafamily bm on bm.media_set_id = bs.media_set_id
where (sd.name not in ('tempdb'))
and (bs.Type in('D', 'L', 'I') or bs.Type is null) -- D:full, I:Diff. L:Log
)
A
where A.RowNumber <= 1
order by A.DBName, A.Type, A.backup_start_date desc
go
Script 2: Generate a restore scrit from .bak file
declare
@BackupFile nvarchar(256),
@DatabaseName nvarchar(128),
@FileId tinyint,
@ReplaceExisting bit,
@RestoreWithNoRecovery bit
declare
@BackupInfo varchar(256),
@BackupType smallint,
@BackupStartDate datetime,
@BackupFinishDate datetime,
@SourceDatabaseName nvarchar(128),
@SourceServerName nvarchar(128)
select
@BackupFile = 'E:\DBBackups\DBA.full.bak',
@DatabaseName = null, -- if null use the original one
@FileId = 1,
@ReplaceExisting = 0,
@RestoreWithNoRecovery = 0 -- set to 1 if you want to apply diff or log after the full
/******************************************************************************
Description:
Generate restore script from flat file backup, please note that the script will not executed
you should review the script and change the name of database and paths if needed before you run
the script generated.
Return values:
1 Success
0 Failure
Change History:
Date Author Description
---------- ------------------- ------------------------------------
2014 Bouarroudj Mohamed Created
2015-02-25 Bouarroudj Mohamed Added support of Diff and Tlog backups
*********************************************************************************/
set nocount on
---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------
declare
@ExitCode tinyint,
@sqlcmd1 nvarchar(max),
@sqlcmd2 nvarchar(max),
@sqlVers numeric(4,2)
---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------
set @ExitCode = 1
set @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)
---------------------------------------------------------------------
-- Create temp tables
---------------------------------------------------------------------
-- RESTORE FILELISTONLY result set: https://msdn.microsoft.com/en-us/library/ms173778.aspx
create table #BackupFiles
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[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,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
)
if @sqlVers >= 10
begin
--print 'SQL 2008 and more, add new column : #BackupFiles.TDEThumbprint'
alter table #BackupFiles add TDEThumbprint varbinary(32)
end
-- RESTORE HEADERONLY result set: https://msdn.microsoft.com/en-us/library/ms178536.aspx
create table #BackupHeaderInfo
(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed BINARY(1), -- byte
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier
--CompressedBackupSize bigint, -- SQL Server 2008 and +
--containment tinyint -- SQL Server 2012 amd +
)
if @sqlVers >= 10
begin
--print 'SQL 2008 and more, add new column : #BackupHeaderInfo.CompressedBackupSize'
alter table #BackupHeaderInfo add CompressedBackupSize bigint
end
if @sqlVers >= 11
begin
--print 'SQL 2012 and more, add new column : #BackupHeaderInfo.containment'
alter table #BackupHeaderInfo add containment tinyint
end
---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------
-- Retrieve metadata from backup
-- print RESTORE with VERIFYONLY
set @sqlcmd1 = N'RESTORE VERIFYONLY FROM DISK=''' + @BackupFile + ''''
print '-- ' + @sqlcmd1
-- 1. from FILELISTONLY
set @sqlcmd1 = N'RESTORE FILELISTONLY FROM DISK=''' + @BackupFile + ''' WITH FILE=' + CAST(@FileId AS nvarchar(5)) + ''
print '-- ' + @sqlcmd1
insert into #BackupFiles exec sp_executesql @sqlcmd1
if @@ERROR > 0
begin
raiserror('error on running RESTORE FILELISTONLY, make sure BackupFile and FileId are valid', 16, 1)
goto ErrorHandler
end
-- 2. from HEADERONLY
set @sqlcmd1 = N'RESTORE HEADERONLY FROM DISK=''' + @BackupFile + ''' WITH FILE=' + CAST(@FileId AS nvarchar(5)) + ''
--print '-- ' + @sqlcmd1
insert into #BackupHeaderInfo exec sp_executesql @sqlcmd1
if @@ERROR > 0
begin
print '-- ' + @sqlcmd1
raiserror('error on running RESTORE HEADERONLY, make sure BackupFile and FileId are valid', 16, 1)
goto ErrorHandler
end
select
@BackupType = BackupType,
@BackupStartDate = BackupStartDate,
@BackupFinishDate = BackupFinishDate,
@SourceServerName = ServerName,
@SourceDatabaseName = DatabaseName
from #BackupHeaderInfo
if @DatabaseName is null
set @DatabaseName = @SourceDatabaseName
if @BackupType not in (1, 2, 5) -- only FULL, LOG and DIFF are supported
begin
print '-- ' + @sqlcmd1
raiserror('BackupType not supported, only FULL, LOG and DIFF are supported', 16, 1)
goto ErrorHandler
end
set @BackupInfo =
'-- Source Server/Database Name : ' + @SourceServerName + '/' + @SourceDatabaseName + '
-- BackupStartDate : ' + CONVERT(varchar(121), @BackupStartDate, 121) + '
-- Bkp Duration in min : ' + CAST(DATEDIFF(minute, @BackupStartDate, @BackupFinishDate) as varchar(8)) + '
-- Backup Type : '
+
case
when @BackupType = 1 then 'FULL'
when @BackupType = 2 then 'LOG'
else 'DIFFERENTAIL'
end
if @BackupType = 2 -- 2 = Transaction log
begin
set @sqlcmd1 = 'RESTORE LOG ' + QUOTENAME(@DatabaseName) + '
FROM DISK=''' + @BackupFile + '''
WITH FILE=' + CAST(@FileId AS nvarchar(5)) + ', STATS=15, NORECOVERY -- use RECOVERY if needed'
set @sqlcmd2 = ''
end
else if @BackupType = 5 -- 5 = Differential database
begin
set @sqlcmd1 = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + '
FROM DISK=''' + @BackupFile + '''
WITH FILE=' + CAST(@FileId AS nvarchar(5)) + ', STATS=15, NORECOVERY -- use RECOVERY if needed'
set @sqlcmd2 = ''
end
else -- 1 = full
begin
-- build the header of SQL Restore cmd
set @sqlcmd1 = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + '
FROM DISK=''' + @BackupFile + '''
WITH FILE=' + CAST(@FileId AS nvarchar(5)) + ','
-- build the move part of SQL Restore cmd
set @sqlcmd2 = ''
select @sqlcmd2 = @sqlcmd2 + 'MOVE N''' + LogicalName + ''' TO N''' + PhysicalName + ''',
'
from #BackupFiles
order by [Type]
set @sqlcmd2 = @sqlcmd2 + 'STATS=15'
if @ReplaceExisting = 1
set @sqlcmd2 = @sqlcmd2 + ', REPLACE'
if @RestoreWithNoRecovery = 1
set @sqlcmd2 = @sqlcmd2 + ', NORECOVERY'
end
print @BackupInfo
print ''
print @sqlcmd1
print @sqlcmd2
goto ExitProc
---------------------------------------------------------------------
-- Error Handler
---------------------------------------------------------------------
ErrorHandler:
select @ExitCode = 0
goto ExitProc
---------------------------------------------------------------------
-- Exit Procedure
---------------------------------------------------------------------
ExitProc:
drop table #BackupFiles
drop table #BackupHeaderInfo
--return (@ExitCode)
go
Script 3: Estimate time for current long running processes such as backups and restores
SELECT
R.session_id,
R.percent_complete,
DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time,
R.total_elapsed_time/1000 AS elapsed_secs,
R.wait_type,
R.wait_time,
R.last_wait_type,
ST.text,
SUBSTRING(ST.text, R.statement_start_offset / 2,
(
CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
ELSE R.statement_end_offset
END - R.statement_start_offset
) / 2
) AS statement_executing
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST
WHERE R.percent_complete > 0
AND R.session_id <> @@spid
OPTION(RECOMPILE);
Always on Scripts
