Wednesday, August 25, 2010

Restore multiple data and log file script

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\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


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


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


  1. A personal message to Mr.Ramesh.Your inbox is full in komicslive friend.That's why you aren't able to get a msg.

  2. These programs parse a log file from a web server using the data contained in the log file and provide information regarding website traffic and visitors.seo log analyzer