SQL Server Back Up and History

SQL Server Back Up and History

-- BACKUP SERVICE MASTER KEY
BACKUP SERVICE MASTER KEY TO FILE = 'C:\backup\servicemasterkey_backup.key'
ENCRYPTION BY PASSWORD = 'password@5'

-- RESTORE SERVICE MASTER KEY
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\backup\servicemasterkey_backup.key'
DECRYPTION BY PASSWORD = 'password@5'
FORCE

-- BACKUP MASTER KEY
BACKUP MASTER KEY TO FILE = 'C:\backup\masterkey_backup.key'
ENCRYPTION BY PASSWORD = 'password@5'

-- RESTORE MASTER KEY
RESTORE MASTER KEY FROM FILE = 'C:\backup\masterkey_backup.key'
DECRYPTION BY PASSWORD = 'password@5'
ENCRYPTION BY PASSWORD = 'password@5'
FORCE

-- To Back up Database
BACKUP DATABASE [Employee] TO DISK = N'C:\Backup\Employee.bak'
WITH NOFORMAT, NOINIT, NAME = N'Employee-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- To Restore Databse
RESTORE DATABASE [Employee] FROM DISK = N'C:\Backup\Employee.bak'
WITH FILE = 1,
MOVE N'Employee_log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Employee_log.LDF',
NOUNLOAD, REPLACE, STATS = 10
GO

--- SQL Server Script to Retrievw Job History
select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
order by job_name, run_datetime

-- or

SELECT sj.name,
sja.run_requested_date,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc


-- To Retrievw Path and Size of the Databas Backup
SELECT top 10 s.database_name,
m.physical_device_name,
s.backup_size,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
-- Optional Condition
-- WHERE s.database_name = 'BackupReport'
-- ORDER BY database_name, backup_start_date, backup_finish_date

-- To check SQL Server FULL database backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server Transaction Log backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -344, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server Differential Backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO

-- To check SQL Server File\File Group Backups are successful or not
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO