SQL Server Backup Scripts
SQL Server Backup Scripts are the ones which every DBA needs often. We will have a look on few Backup Scripts today which will be very useful for your day to day tasks-
1.1. Please use the below script if you want to get Backup Details including Backup path and most recent backup date
DECLARE @type char(1)
SET @TYPE='D' --FULL
Backup 'D' , 'L' for Log Backup
Select ServerName=@@servername ,
Select ServerName=@@servername ,
Edition=Serverproperty('Edition'),
ProductLevel=Serverproperty('ProductLevel'),
ServerVersion=Serverproperty('Productversion'),DatabaseName =sd.name,
DBStatus=Databasepropertyex(sd.name,'Status'),
RecoveryModel =Databasepropertyex(sd.name,'Recovery'),
IsBackupDone =Case when Bkp.database_name is Null then 'No' Else 'Yes' End,
Bkp.physical_device_name,Bkp.backup_finish_date
from sysdatabases
sd
Left Join
(
select
x.database_name,
z.physical_device_name, --CONVERT(char(20),
x.backup_finish_date, 108) FinishTime,
x.backup_finish_date from msdb.dbo.backupset x
JOIN
( SELECT
a.database_name,
max(a.backup_finish_date)
backup_finish_date
FROM
msdb.dbo.backupset
a WHERE
type = @TYPE
GROUP
BY a.database_name
) y
ON x.database_name
= y.database_name
AND x.backup_finish_date
= y.backup_finish_date
JOIN msdb.dbo.backupmediafamily
z
ON x.media_set_id
= z.media_set_id
)BKP
ON sd.name=Bkp.database_name
where sd.name <> 'tempdb'
1.2. Below script can be used if you want to get Full Database Backup history
SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date,
108) FinishTime,
x.backup_finish_date
from msdb.dbo.backupset x
join ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
WHERE type = 'D'
GROUP BY a.database_name ) y on x.database_name = y.database_name
and x.backup_finish_date
= y.backup_finish_date
JOIN msdb.dbo.backupmediafamily
z ON x.media_set_id
= z.media_set_id
1.3.
Log Backup history can be seen with the help of below Query
SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date,
108) FinishTime,
x.backup_finish_date
from msdb.dbo.backupset x
join ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
WHERE type = 'L'
GROUP BY a.database_name ) y on x.database_name = y.database_name
and x.backup_finish_date
= y.backup_finish_date
JOIN msdb.dbo.backupmediafamily
z ON x.media_set_id
= z.media_set_id
1.4. Backup all User Databases will be displayed if you execute below query
DECLARE @DB VARCHAR(300), @Dir VARCHAR(300), @Stmt VARCHAR(1000), @RM sql_variant,
@Status INT, @DT DateTime, @BakTime varchar(20)
SELECT @DB = '', @Dir = '\\NetworkPath'
SET @DT = GETDATE()
SET @BakTime = dbo.fn_StringFormatDate(@DT)
IF OBJECT_ID('tempdb.dbo.#Databases') IS NOT NULL DROP TABLE
#Databases
IF OBJECT_ID('tempdb.dbo.BackUpStatus') IS NULL
CREATE TABLE tempdb.dbo.BackUpStatus
(Row_Id int IDENTITY NOT NULL PRIMARY KEY,
dbName varchar (50) NULL,
StepName varchar (100) NOT NULL,
Status int NULL,
ActionDateTime datetime
NOT NULL)
ELSE
DELETE FROM tempdb.dbo.BackUpStatus WITH (ROWLOCK)
WHERE
ActionDateTime < DATEADD(d, -90, GETDATE())
--SELECT @Stmt
= 'MKDIR ' + @Dir
--EXEC @Status
= master..xp_cmdshell @Stmt, NO_OUTPUT
SELECT @Stmt = @Dir + '\' + @DB + ''
--EXEC @Status
= master.dbo.xp_create_subdir @Stmt
INSERT tempdb.dbo.BackUpStatus (dbName, StepName, Status, ActionDateTime)
SELECT 'Job Requirement', 'Check Backup Directory'
+ @Dir, @Status, GETDATE()
SELECT Name as dbName, DATABASEPROPERTYEX(name, 'Recovery')
RecoveryModel
INTO #Databases
FROM master..SysDatabases WITH (NOLOCK)
WHERE DATABASEPROPERTYEX(name, 'Status') <> 'OFFLINE' AND
DATABASEPROPERTYEX(name, 'Status') <> 'LOADING' AND
DATABASEPROPERTYEX(name, 'Status') <> 'Restoring' AND
DATABASEPROPERTYEX(name, 'Recovery') = 'FULL' AND
Name NOT IN ('master', 'distribution', 'msdb', 'model') AND
(Name NOT LIKE 'a%' AND
Name NOT LIKE 'HMXMeta%' AND
Name NOT LIKE 'xxx%' AND
Name NOT LIKE 'g%' AND
Name NOT
LIKE '%temp%' AND
Name NOT LIKE '%tmp%' AND
Name NOT LIKE '%test%' AND
Name NOT LIKE '%train%' AND
Name NOT LIKE '%Secure%' AND
Name NOT LIKE '%LiteSpeedLocal%'
OR
Name LIKE '%WCMeta%')
-- AND NAME LIKE @DBname
WHILE 1 = 1 BEGIN
SELECT TOP 1 @DB = ltrim(rtrim(dbName)), @RM =
RecoveryModel
FROM
#Databases
WHERE dbName > @DB
ORDER BY dbName
IF @@ROWCOUNT <>
1 OR @DB IS NULL BREAK
SET @Stmt = 'master..xp_cmdshell '''
+ 'MKDIR ' + @Dir + '\' + @DB + ''', NO_OUTPUT'
EXEC(@Stmt)
--SELECT @Stmt
= @Dir + '\' + @DB + ''
--EXEC @Status
= master.dbo.xp_create_subdir @Stmt
SET @Stmt = 'BACKUP DATABASE ['+
@DB + '] ' +
'TO DISK=''' + @Dir + '\' + @DB + '\' + @DB + @BakTime + '_FULL.BAK'',' +
'@INIT=0'
PRINT @Stmt
--EXEC
(@Stmt)
SELECT @Status
= @@ERROR
INSERT tempdb.dbo.BackUpStatus (dbName, StepName, Status,
ActionDateTime)
SELECT @DB, 'Backup Log ', @Status, GETDATE()
END
1.5. Missing Details is easier to find with the help of below Query
DECLARE @threshold INT
SET @threshold =
32 -- Hours
Select @@servername AS
InstanceName
, d.name AS Database_Name
, 'Backup
did not occurd within ' + CAST(@threshold as VARCHAR) + ' hours.' AS Message
, a.backup_start_date
AS Last_Backup
, a.backup_size
, CASE b.device_type
when 2 then 'Disk - Temporary'
when 102 then 'Disk -
Permanent'
when 5 then 'Tape - Temporary'
when 105 then 'Tape -
Permanent'
when 7 then 'Network'
else 'Unknown' END AS
Device_Type
, b.physical_device_name
AS Physical_Path
from(
select a.media_set_id, a.database_name, a.backup_start_date, a.backup_size,b.type
from msdb.dbo.backupset a
JOIN (
Select
database_name, max(backup_finish_date)
backup_finish_date, type
from msdb.dbo.backupset
where type='D'
group by database_name,type
) b
on a.database_name
= b.database_name
and a.backup_finish_date
= b.backup_finish_date
) a
JOIN msdb..backupmediafamily
b ON a.media_set_id
= b.media_set_id
right join master..sysdatabases d on a.database_name=d.name
where d.name<>'tempdb'
and (DATEDIFF(hh, a.backup_start_date, getdate()) > @threshold OR a.backup_start_date
IS NULL)
order by
d.name
1.6.
Script to analyze space required for
database if backups are failing due to space issue.
Select sum( cast((a.size * 8.00) / 1024 as numeric(12,2))) as DB_Size_in_MB
from sysaltfiles a
join sysdatabases b on a.dbid = b.dbid
where DATABASEPROPERTYEX(b.name, 'status') = 'ONLINE'
and b.name in (SELECT a.name
FROM master..sysdatabases
a
LEFT JOIN (SELECT database_name, MAX(backup_finish_date)
backup_finish_date
FROM msdb..backupset
WHERE Type = 'D'
GROUP BY
database_name) b ON
a.name = b.database_name
WHERE (DATEDIFF(hh, b.backup_finish_date, GETDATE()) > 10 or b.database_name IS NULL) AND
(DATABASEPROPERTYEX(a.name, 'Status') = 'ONLINE' AND
a.Name NOT LIKE '%temp%' AND a.Name NOT LIKE '%tmp%' AND a.Name NOT LIKE '%test%' AND
a.Name NOT LIKE '%train%' AND a.Name NOT LIKE '%LiteSpeedLocal%')
)
Group By b.name
Also if native backup is done, you may get the result using
backupset table,but for compressed backup files result may differ.
SELECT a.name ,b.backup_size
FROM master..sysdatabases
a
LEFT JOIN (SELECT database_name,max(backup_size)/1024/1024 as backup_size,
MAX(backup_finish_date)
backup_finish_date
FROM msdb..backupset
WHERE Type = 'D'
GROUP BY
database_name) b ON
a.name = b.database_name
WHERE (DATEDIFF(hh, b.backup_finish_date, GETDATE()) > 10 or b.database_name IS NULL) AND
(DATABASEPROPERTYEX(a.name, 'Status') = 'ONLINE' AND
--a.Name NOT IN ('master', 'distribution', 'msdb', 'model')
AND
a.Name NOT LIKE '%temp%' AND a.Name NOT LIKE '%tmp%' AND a.Name NOT LIKE '%test%' AND
a.Name NOT LIKE '%train%' AND a.Name NOT LIKE '%LiteSpeedLocal%')
