Restore SQL DB with correct logical file names

[blueboy]

Hi,

I am planning to automate a nighty restore of a DB on another server
can someone point me in the right direction with the SQL script to
modify the logical file names to the correct path and not the ones
carried over with the DB??

i.e the database is to be renamed on the new server

any help much appreciated

Many thanks in advance

[blueboy]

Hi still having probs get the following error

Executed as user: domain\user. Logical file 'UKReports _Data.MDF' is
not part of database 'ukreports'. Use RESTORE FILELISTONLY to list the
logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.

RESTORE DATABASE rentsmartukrepo rts
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSS QL\data\UK _Data' TO 'G:\SQLDATA\MSS QL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSS QL\data\UK _Log' TO 'G:\SQLDATA\MSS QL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO

Where uk is old DB name and UKReports is new DB name

Any ideas??

[Erland Sommarskog]

blueboy (ma********@hot mail.com) writes:

Hi still having probs get the following error

Executed as user: domain\user. Logical file 'UKReports _Data.MDF' is
not part of database 'ukreports'. Use RESTORE FILELISTONLY to list the
logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.

RESTORE DATABASE rentsmartukrepo rts
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSS QL\data\UK _Data' TO 'G:\SQLDATA\MSS QL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSS QL\data\UK _Log' TO 'G:\SQLDATA\MSS QL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO

Indeed, 'G:\SQLDATA\MSS QL\data\UK _Data' looks like a very unusual
logical name. Usually the logical file name of the data file is the
same as the database name, and the log file has "_log" tacked on it.
Sometimes the MDF has "_Data" in the logical name.

You can use sp_helpdb to find out the logical names of a dataase.
Or, if all you have is a backup, RESTORE FILELISTONLY.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[blueboy]

This is so frustrating, i have tried numerous variations and always
get the same error can any one help??

RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSS QL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSS QL\data\[new DB
name]_Log.LDF',
STATS = 1, REPLACE
GO

i get above error as stated, any help much appreciated does anyone
actaully have this working?? if so can i see your script??

cheers

[Erland Sommarskog]

blueboy (ma********@hot mail.com) writes:

This is so frustrating, i have tried numerous variations and always
get the same error can any one help??

RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSS QL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSS QL\data\[new DB
name]_Log.LDF',
STATS = 1, REPLACE
GO

i get above error as stated, any help much appreciated does anyone
actaully have this working?? if so can i see your script??

You have the syntax right, but how could I write a script for you
when I don't know the logical names of your database files? All I can
say is that

'[Old DB name] _Data'

looks funny. There are brackets in the name, and there is a blank in
the middle.

This will remain frustrating, if you just take chances on the name.
You need to find out what the names are, and there are two ways to
do it:

1) sp_helpdb on the source database. It's the first column in the
second result set, just copy and paste into the single quotes.
2) RESTIRE FILELISTONLY on the back-up file. Again, it's the first
column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[blueboy]

Hi

I finally got this working so thanks for anyone else here is the
script i used

databasename should be the Database Name

step 1 kill conections

ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

step 2 restore DB

RESTORE DATABASE datbasename
FROM DISK =
'F:\backup\data basename.bak'
WITH MOVE 'databasename_D ata' TO 'F:\MSSQL\MSSQL \Data\databasen ame
MDF',
MOVE 'databasename_L og' TO 'F:\MSSQL\MSSQL \databasename.L DF',
STATS = 1, REPLACE
GO

step 3 allow connections

ALTER DATABASE databasename SET MULTI_USER