COOL T-SQL BACKUP/RESTORE QUERIES

How to Restore Database from a Shared Network Location

--Restore Database from a shared network location
--Change N' as needed and database name

USE [master]
RESTORE DATABASE [Db_Test] FROM DISK = 
N'\\Server_Name\Folder_Name\db_test.bak' WITH FILE = 1, 
MOVE N'DB_Test' TO 
N'F:\SQLData\Folder_Name\db_test.mdf', 
MOVE N'DB_Test_log' TO 
N'F:\SQLLogs\Folder_Name\db_test_log.LDF', 
NOUNLOAD, STATS = 5
GO

How to get Database Restore History

--Check the restore history
use msdb
select top 100 
r.destination_database_name
,r.restore_history_id 
,r.restore_date 
, b.backup_finish_date
,case when r.restore_type = 'D' then 'Restore from Full Backup' 
 when r.restore_type = 'I' then 'Restore from Diff Backup'
 when r.restore_type = 'L' then 'Restore from Log Backup'
 end as 'Restore Type'
,case when r.replace = 0 then 'no' else 'yes' end as 'Replace Option'
,case when r.recovery = 0 then 'no' else 'yes' end as 'Recovery Option'
, b.database_backup_lsn
, b.first_lsn, b.last_lsn
, b.differential_base_lsn
from restorehistory r
join backupset b on b.backup_set_id = r.backup_set_id
--where 
--destination_database_name = 'Database Name' and -- *for a specific database*
--b.backup_finish_date > getdate()-3 -- *in the past 3 days*
order by r.restore_date desc


use msdb
select top 100 r.restore_date, r.destination_database_name, 
r.user_name, r.restore_type, r.replace, r.recovery
from dbo.restorehistory r 
order by 1 desc

use msdb
select top 100 r.restore_date, r.destination_database_name, 
r.user_name, f.physical_device_name as 'backup_location', r.restore_type, 
r.replace, r.recovery from dbo.restorehistory r 
join backupmediafamily f on r.backup_set_id = f.media_set_id
order by 1 desc

How to get backup History (Detailed) for a specific database

-- Recent backup history for the current DB
SELECT s.database_name 'Database',
s.recovery_model 'Recovery Model',
s.compatibility_level,
s.USER_NAME 'Backup by Username',
CASE s.TYPE
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END 'Backup Type',
CONVERT(VARCHAR(20), s.backup_finish_date, 13) 'Backup Completed',
CAST(mf.physical_device_name AS VARCHAR(100)) 'Physical device name',
DATEDIFF(minute, s.backup_start_date, s.backup_finish_date) 'Duration Min',
CAST(ROUND(s.backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 
'Size in MB',
CAST(ROUND(s.compressed_backup_size * 1.0 / ( 1024 * 1024 ), 2) 
AS NUMERIC(10, 2)) 'Compressed Size in MB',
CASE WHEN LEFT(mf.physical_device_name, 1) = '{' THEN 'SQL VSS Writer'
WHEN LEFT(mf.physical_device_name, 3) LIKE '[A-Za-z]:\%' THEN 'SQL Backup'
WHEN LEFT(mf.physical_device_name, 2) LIKE '\\' THEN 'SQL Backup'
ELSE mf.physical_device_name
END 'Backup tool',
s.is_copy_only,
s.is_password_protected,
s.is_force_offline /* for WITH NORECOVERY option */
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name = DB_NAME() AND -- remove this condition if you want all DBs
s.backup_finish_date > DATEADD(MONTH, -3, GETDATE()) -- Get data for past 3 months
ORDER BY s.backup_finish_date DESC;

How to backup all your databases to a specific directory

--Query Backups all databases including system databases (excluding tempdb)

DECLARE @dbname varchar(255)
declare @BackupFile varchar(4000), @BackupDir varchar(4000), @cmd varchar(4000), 
@CreateSub bit

set @BackupDir = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' 
-- Set to backup directory
SET @CreateSub = 0 -- Set to 1 if you want the databases backed up to their 
                   --own sub-directory

declare dbcursor CURSOR for SELECT name FROM master..sysdatabases 
where name not in ('tempdb')
Open dbcursor

Fetch next from dbcursor
into @dbname

WHILE @@FETCH_STATUS = 0
 BEGIN 
 SET @BackupFile = ''
 If @CreateSub = 1
 BEGIN
 Set @BackupFile = @dbname+'\'
 END
 set @BackupFile = @BackupFile+@dbname+'_db_'
 set @BackupFile = @BackupFile+(select substring(replace(replace(replace(convert
(varchar(100),getdate(),120),'-',''),' ',''),':',''),1,len(replace(replace
(replace(convert(varchar(100),getdate(),120),'-',''),' ',''),':',''))-2))
 set @BackupFile = @BackupFile+'.bak'
 set @BackupFile = @BackupDir+@BackupFile
 --select @BackupFile --debug

Set @cmd ='backup database ['+@dbname+'] to disk = '''+@BackupFile+''' with 
stats = 10'
 --select @cmd --debug
 --exec(@cmd) --execute backup commands
 Fetch next from dbcursor
 into @dbname
 END
CLOSE dbcursor
DEALLOCATE dbcursor
go

How to Check the ETA for a Database Backup/Restore

--Query to determine percentage of completion for backups:
Select session_id,
db_name(database_id) as 'Database', 
cast(percent_complete as varchar) + '%' as 'percent_complete', 
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
 + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
 + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as 
est_time_to_go, wait_type, last_wait_type, blocking_session_id
from sys.dm_exec_requests 
where command like 'Backup%' 
or command like 'Restore%'
--or command like 'DB STARTUP'
--or command like 'DBCC%'

DISCLAIMER:

The sample scripts are provided AS IS without warranty of any kind. SAMOSQL further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall SAMOSQL, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if SAMOSQL has been advised of the possibility of such damages.