Dynamic Restore Script

Hi Folks! Few days back i was working on a project, I was trying to automate few things, I will share most of my work in next blogs, so i have also automate restore process for database having multiple files. It will work well with any number of files. You need to provide database name and backup location.

This script is for Sql Server. 

SET NOCOUNT ON

DECLARE @restoreTable TABLE (
 [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
 ,[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
 )

INSERT INTO @restoreTable
EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @backuppath + '''')

--SELECT * FROM @fileListTable
SET @Query = 'RESTORE DATABASE [' + @Dbname + '] FROM  DISK = N''' + @backuppath + ''' 
WITH  FILE = 1,'

SELECT @Query = @Query + ' 
MOVE N''' + LogicalName + ''' TO N''' + PhysicalName + ''','
FROM @restoreTable

SET @Query = @Query + ' 
NOUNLOAD,  REPLACE,  STATS = 5'

PRINT @Query
--Exec(Query)

Get time for restore or backup a database

SELECT 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,

       command,

       s.text,

       start_time,

       CAST(((DATEDIFF(s,start_time,GetDate()))/3600) AS varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 AS varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) AS varchar) + ' sec' AS running_time,

       dateadd(SECOND,estimated_completion_time/1000, getdate()) AS est_completion_time

FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command IN ('RESTORE DATABASE',

                    'BACKUP DATABASE',

                    'RESTORE LOG',

                    'BACKUP LOG',

                    'RESTORE HEADERON')

Sql Logs :

Backup Log Code :

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'
   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() - 7)
--where database_name like '<dbname>'
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date asc