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

[Jonathan Roberts]

blueboy wrote:

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

This should point you in the right direction:

http://support.microsoft.com/default...b;en-us;314546

If you restore the database to a different file location than the source
database, you must specify the WITH MOVE option. For example, on the
source server the database is in the D:\Mssql\Data folder. The
destination server does not have a D drive, and you want to restore the
database to the C:\Mssql\Data folder.

Good luck

[blueboy]

Many thanks for that it seems to be what i was after however i keep
getting an error -

The job failed. The Job was invoked by User domainname\user . The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

here is the scripting i have;

kill connections -
ALTER DATABASE {db name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Restore -

RESTORE DATABASE {db name}
FROM DISK =
'E:\folde\{db name} .bak'
WITH MOVE 'Logical_Name_D ata' TO 'G:\SQLDATA\MSS QL\data\{db name}
_Data.MDF',
MOVE 'Logical_Data_L og' TO 'G:\SQLDATA\MSS QL\data\{db name}
_Log.LDF',
STATS = 1, REPLACE
GO

It stops at step 2 i also notice when i go back into the steps they
are defaulting back to the master database??

Any help much appreciated

[Erland Sommarskog]

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

Many thanks for that it seems to be what i was after however i keep
getting an error -

The job failed. The Job was invoked by User domainname\user . The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

Did you look under Job history to see what failed? Up to the right
(in Enterprise Manager in SQL 2000), there is a checkbox which says "View
step history". There should be an error message.
--
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]

yes the error is

The job failed. The Job was invoked by User domainname\user . The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

cheers

[Erland Sommarskog]

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

yes the error is

>The job failed. The Job was invoked by User domainname\user . The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).

That's the error for the job as such. That's not the output from the job
step. Please check "Show step details".
--
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]

Arrr Apologies

here is the info

Executed as user: User domainname\user . Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.

Step one runs fine which disconnects users so not sure what the prob
is? any ideas??

Many thanks

[Greg D. Moore \(Strider\)]

"blueboy" <ma********@hot mail.comwrote in message
news:11******** **************@ q2g2000cwa.goog legroups.com...

Arrr Apologies

here is the info

Executed as user: User domainname\user . Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.

Step one runs fine which disconnects users so not sure what the prob
is? any ideas??

Are you sure the job isn't trying to run while in that DB?

Many thanks

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

[blueboy]

Sorry what do you mean by

Are you sure the job isn't trying to run while in that DB?

appologies for sounding daft its been a long day!!

cheers

[Erland Sommarskog]

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

here is the info

Executed as user: User domainname\user . Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.

Step one runs fine which disconnects users so not sure what the prob
is? any ideas??

Seems like you set the database for that job step to be the database
you want to restore. Change to master, and you should be fine.

Or someone manages to sneak in betnween the job steps. Make it one
single step to avoid this risk.

(But put SET MULTI_USER in step 2, and on the Advanced tab for step 1,
configure the job to continue with step 2, even if step 1 fails.)

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