DB Backup and Restore History

 --DB Backup History

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,msdb.dbo.backupset.expiration_date,CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Diff'END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30) and msdb.dbo.backupset.database_name like 'DB_Name'  order by 3 desc

--DB Restore History  

SELECT    [rh].[destination_database_name],     [rh].[restore_date],     [b].[backup_start_date],     [b].[backup_finish_date],     [b].[type],     [b].[first_lsn],     [b].[last_lsn],     [b].[checkpoint_lsn],     [b].[database_backup_lsn] FROM [msdb].[dbo].[restorehistory] AS [rh] JOIN [msdb].[dbo].[backupset] AS [b] ON [rh].[backup_set_id] = [b].[backup_set_id] WHERE [rh].[destination_database_name] = 'DB_Name' ORDER BY [rh].[restore_date] DESC;

======================================================================================================================

 --DB Backup 

--Full backup of DB_Name

BACKUP DATABASE [DB_Name] TO  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_1.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_2.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_3.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_4.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_5.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_6.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_7.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_8.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_9.bak',  

DISK = N'T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_10.bak'  

WITH NOFORMAT, NOINIT,  

NAME = N'DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, Compression, STATS = 1

--Diff backup of DB_Name

BACKUP DATABASE [DB_Name] TO  
DISK = N'T:\DB_Backup\DB_Name_Diff_Backup_1.bak',  
DISK = N'T:\DB_Backup\DB_Name_Diff_Backup_2.bak',  
DISK = N'T:\DB_Backup\DB_Name_Diff_Backup_3.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT,  
NAME = N'Diff Backup', SKIP, NOREWIND, NOUNLOAD, compression, STATS = 10
GO

--T Log backup of DB_Name

BACKUP Log [DB_Name] TO  DISK = N'T:\DB_Backup\DB_Name_T_Log_Backup_1.trn' WITH NOFORMAT, NOINIT,  NAME = N'T:\DB_Backup\DB_Name-T_Log Database Backup', SKIP, NOREWIND, NOUNLOAD, compression, STATS = 1

GO

======================================================================================================================

Restarting a single Db 

use master

alter database YourDatabase

set offline

with rollback immediate

go

alter database YourDatabase

set online

go