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

This time i had face another one issue. When I was trying to install SQL 2008 R2 enterprise edition on my laptop i got below error.

The following error has occured:
'' is not a valid login or you do not have permission.

This error occurs when system name and login names are same. My laptop and login names are same.
This error occurs after adding my login accounts in "Specify SQL Server administrators" on "Database Engine Configuration" tab.


How to get rid this error?

We can achieve this goal using 2 methods.
1. Login in two another windows login account and install.
2. Do not add your account and add another account which name is differ from system. I have used VM.

I have tried both methods and succeeded.