Sequal Server.Com

Most of the databases (All) were multiple data and log files in my production server. Monthly once, I need to refresh whole databases from production to staging and testing servers. Each and every time, I will get physical files and logical name from backup files and prepare manual custom restore script. So I created a script which will generate restore script.

Restore Script for existing database with same number Physical and logical files.

use mytestdb

Declare @sSQL varchar(8000)

set @sSQL ='restore database '+ db_name () +' from disk =''\\sql_backup.prod.csc.local\sql_backup\WILSQLP14\SQL2005\ilcorpdb.bak'' ' + char(13) + 'with '

select @sSQL = @sSQL + 'move ''' + name + ''' to ''' +physical_name+''', ' + char(13) from sys.database_files where TYPE =0

select @sSQL = @sSQL + char(13) + 'move ''' + name + ''' to ''' +physical_name+''',' from sys.database_files where TYPE =1

set @sSQL = LEFT (@ssql, len(@ssql)-1)

select @sSQL

Sample Result
restore database MyTestDB from disk ='h:\MyTestDB.bak'
with move 'MyTestDB_Data' to 'F:\SQL2005\SQLData\MyTestDB.mdf',
move 'MyTestDB0' to 'F:\SQL2005\SQLData\MyTestDB_1.ndf',
move 'MyTestDB1' to 'F:\SQL2005\SQLData\MyTestDB_2.ndf',
move 'MyTestDB2' to 'F:\SQL2005\SQLData\MyTestDB_3.ndf',
move 'MyTestDB3' to 'F:\SQL2005\SQLData\MyTestDB_4.ndf',
move 'MyTestDB4' to 'F:\SQL2005\SQLData\MyTestDB_5.ndf',
move 'MyTestDB5' to 'F:\SQL2005\SQLData\MyTestDB_6.ndf',
move 'MyTestDB6' to 'F:\SQL2005\SQLData\MyTestDB_7.ndf',
move 'MyTestDBIndex0' to 'F:\SQL2005\SQLData\MyTestDB_8.ndf',
move 'MyTestDBIndex1' to 'F:\SQL2005\SQLData\MyTestDB_9.ndf',
move 'MyTestDBIndex2' to 'F:\SQL2005\SQLData\MyTestDB_10.ndf',

move 'MyTestDB_Log' to 'G:\SQL2005\SQLLog\MyTestDB_Log.ldf'

Restore Script for fresh database.

Before execute below script, "Ad Hoc Distributed Queries" should be enabled else you will get below error.

Msg 15281, Level 16, State 1, Line 10

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Msg 208, Level 16, State 1, Line 19

Invalid object name 'tempdb..tBakFileContent'.

Enabling "Ad Hoc Distributed Queries"

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure with override

Restore script

Declare @sTargetDataLocation varchar(5000)

Declare @sTargetLogLocation varchar(5000)

Declare @sSQL varchar(8000)

Declare @sDBName varchar(1000)

set @sTargetDataLocation = 'D:\SQLData\'

set @sTargetLogLocation = 'L:\SQLLog\'

set @sDBName = 'MyTestDB'

select LogicalName, right(PhysicalName, CHARINDEX ( '\', REVERSE (PhysicalName),1)-1 )PhyName , Type

into tempdb..tBakFileContent

from

openrowset('SQLNCLI', 'server=SQLServer01\inst1;Trusted_Connection=yes;',

'SET FMTONLY off

exec (''restore filelistonly from disk =''''h:\MyTestDB.bak'''''')')as a

set @sSQL ='restore database ' + @sDBName + ' from disk =''h:\MyTestDB.bak'' ' + char(13) + 'with '

select @sSQL = @sSQL + 'move ''' + LogicalName + ''' to ''' +@sTargetDataLocation+ PhyName+''', ' + char(13)

from tempdb..tBakFileContent where TYPE ='D'

select @sSQL = @sSQL + char(13) + 'move ''' + LogicalName + ''' to ''' +@sTargetLogLocation+ PhyName+''','

from tempdb..tBakFileContent where TYPE ='L'

set @sSQL = LEFT (@ssql, len(@ssql)-1)

select @sSQL

drop table tempdb..tBakFileContent