restore master database: logical

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

Hi.
I've following problem:
- Customer played with his logical drives in a CPQ smart array
- He played too much: he lost the drive with the LDF-File
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
In fact the mode is not wired: what shall he do other when
most important file is missing... :o
- The problem for me now is:

> How can I get the Enterprise Manager or Query Analyzer
up and running again to start a restore?

PS: The restore is to be done from a tape-library running
Veritas Backup Exec + SQL-Agent

PPS: Error-log says (what I already know):
2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:\DEVLOG1\DEVLOG1.ldf' may be incorrect.

Thanks in advance for assistance.

Regards,
Carsten Loefflerdid you try to connect with osql?|||Sounds like someone played with the default database of sa and BUILTIN\ADMINISTRATORS. That's bad. You can see if any of the local administrators can connect to the SQL Server with WndowsNT authentication, but it sounds like you have already tried that.|||If nobody can connect to SQL Server, maybe you want to run rebuildm, then restore the last good master database and other databases as needed.|||Hi Everyone,
thanks for the assistance.

Brett:
No, I didnt try it with OSQL. But thats a good idea. Any hints so far
before I go and investigate myself too much??

MCrowley:
Yip. You're right and I didnt mention it. Yes - I tried it with "sa" and
I tried it with all the others I knew that they were there.
The thing that confuses me: master-DB is still there and running
and didnt get touched...

Tomh:
Mmmh. Do you think I have to do it anyway. I think that master-DB
is up and running.

Regards,
Carsten Loeffler|||The thing that confuses me: master-DB is still there and running
and didnt get touched...

When you log into SQL Server, you always go to your default database first thing. If it is not there, then you get the error "Can not open default database". Has the same thing happened to BUITLIN\ADMINISTRATORS, or can a local Windows admin log in with Windows Authentication?|||Hi.
I've following problem:
<snip>
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
</snip>

Since we know MCrowley is right, and if the statement above is true, then **all** users were defaulted to the database where the ldf is missing.

If I'm reading the quoted part right, not a single user can log in. If that is the case, then master will have to be restored. And since you have to be able to log in to rebuild master, rebuildm will be needed to get back to a place where a system admin can login. So maybe rebuildm, restore database where .ldf is missing, them restore master.|||And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.|||And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.
I am gonna bet all the whisky lying in my almirah it will be possible to login using BUILTIN\ADMINISTRATORS. It just seems that the guy is not aware of the account.

Suggestion : Try logging in with the radio button set to Windows Authentictation while you are logged in with a Windows System Administrator account ...

Wednesday, March 21, 2012

Restore SQL DB with correct logical file names

Wednesday, March 7, 2012

Restore MSSQL Database, lost LDF-File

restore master database

Blog Archive

About Me