— See the logical db files info for the backup file
RESTORE FILELISTONLY FROM DISK = N’F:\DBBackup\DB_local.bak’ With file = 1
— Restore DB to different hard drive location (existing logical data/log name need to be mapped to new mdf/ldf file names in the new location)
RESTORE DATABASE MYDB1 FROM DISK = N’F:\DBBackup\DB_local.bak’
WITH MOVE N’MYDB_Data’ TO N’E:\SQL\Data\DB.mdf’,
MOVE N’MYDB_Log’ TO N’E:\SQL\Log\DB_log.ldf’,
MOVE N’MYDB_Log1′ TO N’E:\SQL\Log\DB_Log1.ldf’
GO
Following sql is helpful to see the DB restore status from time to time. Especially DB’s that take longer time to restore and when sql server management studio is not showing proper status then this query is very handy to check the status.
SELECT
percent_complete AS [PercentComplete]
,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes]
,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes]
,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes]
,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion]
,st.TEXT AS [CommandSQL]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
WHERE command LIKE ‘%RESTORE DATABASE%’
VJ
Tech Hub 