restore master database: Restore SQL DB with correct logical file names

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 advanceblueboy wrote:

Quote:

Originally Posted by

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/defaul...kb;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|||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_Data' TO 'G:\SQLDATA\MSSQL\data\{db name}
_Data.MDF',
MOVE 'Logical_Data_Log' TO 'G:\SQLDATA\MSSQL\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|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes the error is

Quote:

Originally Posted by

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|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

yes the error is
>

Quote:

Originally Posted by

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

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|||"blueboy" <matt_meech@.hotmail.comwrote in message
news:1172237955.311495.294700@.q2g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

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?

Quote:

Originally Posted by

Many thanks
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||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|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

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

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 rentsmartukreports
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSSQL\data\UK _Data' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSSQL\data\UK _Log' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO

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

Any ideas??|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

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 rentsmartukreports
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSSQL\data\UK _Data' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSSQL\data\UK _Log' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO

Indeed, 'G:\SQLDATA\MSSQL\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.

RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSSQL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSSQL\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|||blueboy (matt_meech@.hotmail.com) writes:

Quote:

Originally Posted by

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\MSSQL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSSQL\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.

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\databasename.bak'
WITH MOVE 'databasename_Data' TO 'F:\MSSQL\MSSQL\Data\databasename
MDF',
MOVE 'databasename_Log' TO 'F:\MSSQL\MSSQL\databasename.LDF',
STATS = 1, REPLACE
GO

step 3 allow connections

ALTER DATABASE databasename SET MULTI_USER

Wednesday, March 21, 2012

Restore SQL DB with correct logical file names

No comments:

Post a Comment

restore master database

Blog Archive

About Me