Restore DB**

Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:

quote:

---

> This link should help you
> http://support.microsoft.com/defaul...kb;en-us;246133
> HTH
>

---

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:

quote:

---

> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>

---

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/sql

Restore db in cluster environment

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Your T: drive have not been made a dependency of SQL Server. You need to do
the following:
1) Make the SQL Server resource offline.
2) Move the T: drive resource into the SQL group if it is not already
there.
3) Bring up the properties of the SQL Server resource.
4) Make the T: drive a dependency of SQL Server.
5) Bring the SQL group online.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
|||Got it, Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23A3R85luHHA.536@.TK2MSFTNGP06.phx.gbl...
> Your T: drive have not been made a dependency of SQL Server. You need to
> do
> the following:
> 1) Make the SQL Server resource offline.
> 2) Move the T: drive resource into the SQL group if it is not already
> there.
> 3) Bring up the properties of the SQL Server resource.
> 4) Make the T: drive a dependency of SQL Server.
> 5) Bring the SQL group online.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I got this error when I move the log file to a diff location in the
> cluster
> environment.
> Any ideas and solution?
> Thanks
>
> Msg 5184, Level 16, State 2, Line 1
> Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
> formatted files on which the cluster resource of the server has a
> dependency
> can be used.
>
>

Restore DB automatically

I created script that would restore db automatically, but i have problem
running ...
i get error
Server: Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
HOW do i modify this script to kill all users who use EAGLE database before
restoring database ?
USE EAGLE
GO
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
WITH
MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
,REPLACE
GOMikus
Lookup SINGLE USER MODE in the BOL.
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>I created script that would restore db automatically, but i have problem
> running ...
> i get error
> Server: Msg 3101, Level 16, State 1, Line 2
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
> HOW do i modify this script to kill all users who use EAGLE database
> before
> restoring database ?
> USE EAGLE
> GO
> RESTORE DATABASE EAGLE
> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> WITH
> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> ,REPLACE
> GO|||> USE EAGLE
> GO
> RESTORE DATABASE EAGLE
Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
having the ROLLBACK option to get rid of other users.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>I created script that would restore db automatically, but i have problem
> running ...
> i get error
> Server: Msg 3101, Level 16, State 1, Line 2
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
> HOW do i modify this script to kill all users who use EAGLE database before
> restoring database ?
> USE EAGLE
> GO
> RESTORE DATABASE EAGLE
> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> WITH
> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> ,REPLACE
> GO|||WHERE do i put ALTER DATABASE clause ?
this way ?
USE MASTER
GO
ALTER DATABASE
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
WITH
MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
,REPLACE
GO
"Tibor Karaszi" wrote:
> > USE EAGLE
> > GO
> >
> > RESTORE DATABASE EAGLE
> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
> having the ROLLBACK option to get rid of other users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >I created script that would restore db automatically, but i have problem
> > running ...
> > i get error
> >
> > Server: Msg 3101, Level 16, State 1, Line 2
> > Exclusive access could not be obtained because the database is in use.
> > Server: Msg 3013, Level 16, State 1, Line 2
> > RESTORE DATABASE is terminating abnormally.
> >
> > HOW do i modify this script to kill all users who use EAGLE database before
> > restoring database ?
> >
> > USE EAGLE
> > GO
> >
> > RESTORE DATABASE EAGLE
> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> > WITH
> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> > ,REPLACE
> > GO
>
>|||You need to read Books Online to see how to execute ALTER DATABASE (SINGLE USER, ROLBACK etc). But,
yes, you put it before your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> WHERE do i put ALTER DATABASE clause ?
> this way ?
> USE MASTER
> GO
> ALTER DATABASE
> RESTORE DATABASE EAGLE
> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> WITH
> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> ,REPLACE
> GO
> "Tibor Karaszi" wrote:
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
>> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
>> having the ROLLBACK option to get rid of other users.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
>> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>> >I created script that would restore db automatically, but i have problem
>> > running ...
>> > i get error
>> >
>> > Server: Msg 3101, Level 16, State 1, Line 2
>> > Exclusive access could not be obtained because the database is in use.
>> > Server: Msg 3013, Level 16, State 1, Line 2
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> > HOW do i modify this script to kill all users who use EAGLE database before
>> > restoring database ?
>> >
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
>> > WITH
>> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
>> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
>> > ,REPLACE
>> > GO
>>|||To automate the restore, I usually kill connected users then run the restore.
I have a stored procedure KillConn in master database which I always call
whenever I need to automate database restore. KillConn accepts a parameter
which is database name.
EXEC Master.dbo.KillConn 'Eagle'
go
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
...................................
Here is the extract of KillConn stored procedure:
CREATE PROCEDURE KillConn (@.db varchar(30))
AS
DECLARE @.ps VARCHAR(255)
DECLARE @.id smallint
DECLARE c CURSOR FOR
SELECT
DISTINCT
spid
FROM
master..sysprocesses
WHERE
dbid
IN
(SELECT
dbid
FROM
master..sysdatabases
WHERE
name
IN
(@.db))
OPEN c
FETCH NEXT FROM c INTO @.id
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT @.ps = 'KILL ' + CONVERT(VARCHAR(8), @.id)
EXEC(@.ps)
FETCH NEXT FROM c INTO @.id
END
CLOSE c
DEALLOCATE c
GO
"Tibor Karaszi" wrote:
> You need to read Books Online to see how to execute ALTER DATABASE (SINGLE USER, ROLBACK etc). But,
> yes, you put it before your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> > WHERE do i put ALTER DATABASE clause ?
> >
> > this way ?
> >
> > USE MASTER
> > GO
> >
> > ALTER DATABASE
> > RESTORE DATABASE EAGLE
> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> > WITH
> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> > ,REPLACE
> > GO
> >
> > "Tibor Karaszi" wrote:
> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >>
> >> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
> >> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
> >> having the ROLLBACK option to get rid of other users.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> >> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >> >I created script that would restore db automatically, but i have problem
> >> > running ...
> >> > i get error
> >> >
> >> > Server: Msg 3101, Level 16, State 1, Line 2
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 2
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > HOW do i modify this script to kill all users who use EAGLE database before
> >> > restoring database ?
> >> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> > WITH
> >> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> > ,REPLACE
> >> > GO
> >>
> >>
> >>
>
>|||This was not working ...
USE MASTER
GO
ALTER DATABASE EAGLE
RESTORE DATABASE EAGLE
FROM DISK = 'E:\DBbackup\dailybuild.bck'
WITH
MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
,REPLACE
GO
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'RESTORE'.
i couldn't really find any info in server books online regarding SINGLE USER
MODE ?
"Tibor Karaszi" wrote:
> You need to read Books Online to see how to execute ALTER DATABASE (SINGLE USER, ROLBACK etc). But,
> yes, you put it before your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> > WHERE do i put ALTER DATABASE clause ?
> >
> > this way ?
> >
> > USE MASTER
> > GO
> >
> > ALTER DATABASE
> > RESTORE DATABASE EAGLE
> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> > WITH
> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> > ,REPLACE
> > GO
> >
> > "Tibor Karaszi" wrote:
> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >>
> >> Above will *not* work. You enter the eagle database yourself, and block the restore yourself. USE
> >> master and then restore instead. Also, use ALTER DATABASE to se the database in single user mode,
> >> having the ROLLBACK option to get rid of other users.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> >> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >> >I created script that would restore db automatically, but i have problem
> >> > running ...
> >> > i get error
> >> >
> >> > Server: Msg 3101, Level 16, State 1, Line 2
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 2
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > HOW do i modify this script to kill all users who use EAGLE database before
> >> > restoring database ?
> >> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> > WITH
> >> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> > ,REPLACE
> >> > GO
> >>
> >>
> >>
>
>|||Hi,
The exact command is:-
ALTER DATABASE EAGLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Now issue the RESTORE database command. After the restore issue the below
command to make database multi user.
go
ALTER DATABASE EAGLE SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:er0BvN6sFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You need to read Books Online to see how to execute ALTER DATABASE (SINGLE
> USER, ROLBACK etc). But, yes, you put it before your RESTORE command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
>> WHERE do i put ALTER DATABASE clause ?
>> this way ?
>> USE MASTER
>> GO
>> ALTER DATABASE
>> RESTORE DATABASE EAGLE
>> FROM DISK = 'E:\DBbackup\dailybuild.bck'
>> WITH
>> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
>> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
>> ,REPLACE
>> GO
>> "Tibor Karaszi" wrote:
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> Above will *not* work. You enter the eagle database yourself, and block
>> the restore yourself. USE
>> master and then restore instead. Also, use ALTER DATABASE to se the
>> database in single user mode,
>> having the ROLLBACK option to get rid of other users.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
>> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
>> >I created script that would restore db automatically, but i have
>> >problem
>> > running ...
>> > i get error
>> >
>> > Server: Msg 3101, Level 16, State 1, Line 2
>> > Exclusive access could not be obtained because the database is in use.
>> > Server: Msg 3013, Level 16, State 1, Line 2
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> > HOW do i modify this script to kill all users who use EAGLE database
>> > before
>> > restoring database ?
>> >
>> > USE EAGLE
>> > GO
>> >
>> > RESTORE DATABASE EAGLE
>> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
>> > WITH
>> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
>> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
>> > ,REPLACE
>> > GO
>>
>|||Thanks a lot HAri
"Hari Prasad" wrote:
> Hi,
> The exact command is:-
> ALTER DATABASE EAGLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> Now issue the RESTORE database command. After the restore issue the below
> command to make database multi user.
> go
> ALTER DATABASE EAGLE SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:er0BvN6sFHA.2008@.TK2MSFTNGP10.phx.gbl...
> > You need to read Books Online to see how to execute ALTER DATABASE (SINGLE
> > USER, ROLBACK etc). But, yes, you put it before your RESTORE command.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> > news:D1E93E36-8892-4BBF-92AA-71AEA28489AE@.microsoft.com...
> >> WHERE do i put ALTER DATABASE clause ?
> >>
> >> this way ?
> >>
> >> USE MASTER
> >> GO
> >>
> >> ALTER DATABASE
> >> RESTORE DATABASE EAGLE
> >> FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> WITH
> >> MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> ,REPLACE
> >> GO
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >>
> >> Above will *not* work. You enter the eagle database yourself, and block
> >> the restore yourself. USE
> >> master and then restore instead. Also, use ALTER DATABASE to se the
> >> database in single user mode,
> >> having the ROLLBACK option to get rid of other users.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Mikus" <Mikus@.discussions.microsoft.com> wrote in message
> >> news:0DEE2C96-ADC1-4DEA-96EB-203E563D9028@.microsoft.com...
> >> >I created script that would restore db automatically, but i have
> >> >problem
> >> > running ...
> >> > i get error
> >> >
> >> > Server: Msg 3101, Level 16, State 1, Line 2
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 2
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > HOW do i modify this script to kill all users who use EAGLE database
> >> > before
> >> > restoring database ?
> >> >
> >> > USE EAGLE
> >> > GO
> >> >
> >> > RESTORE DATABASE EAGLE
> >> > FROM DISK = 'E:\DBbackup\dailybuild.bck'
> >> > WITH
> >> > MOVE 'EAGLE_Data' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Data.MDF'
> >> > ,MOVE 'EAGLE_Log' TO 'C:\Program Files\Microsoft SQL
> >> > Server\MSSQL$TEST\Data\EAGLE_Log.LDF'
> >> > ,REPLACE
> >> > GO
> >>
> >>
> >>
> >
> >
>
>

restore db

Hi, I'm trying to restore tran log and the server returns error nb. 4330:
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Please, dont you know, where the problem is?
thanks for any advice...Probably the logs have to applied in order that they were dumped to the last full backup...

Or are you trying to last full back up and have the wrong file?|||Of course I did it with right backup and in a right order...|||Originally posted by Kubas
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.

Sorry, I didn't know...you need to supply for information...

My mind reader machine is broken at the moment...

Can you show us your restore code?

Are the file located on different drives? How many logs did you have to apply?

Is the process automated?

Do you map and unmap the drives after the restore?|||Nevermind :)
I found out new fact that could cause the problem:

I made komplet backup of database before reinstalation SQL server. After that reinstalation a made backup of tran log..
Now I'm not able to load the tran log on the komplet database.

I loaded complet backup in EM with norecovery option.
Then in QA I tried to start this code:

use master
restore log Backup
from disk = 'F:\MSSQL\BACKUP\CallCentrum\CallCentrum_db_200310 100030.BAK'
with file = 2,
stopat ='3.11.2003 12:55 AM'
(tran log is in the same file as complete backup, therefore file =2)
In that moment I get back the error msg. nb. 4330

I think the problem is in reinstalation, although the pathes didnt change. The complete backup is made on another instalation of SQL Server then the tran log...

I hope I descibed it understandable..

Originally posted by Brett Kaiser
Sorry, I didn't know...you need to supply for information...

My mind reader machine is broken at the moment...

Can you show us your restore code?

Are the file located on different drives? How many logs did you have to apply?

Is the process automated?

Do you map and unmap the drives after the restore?

RESTORE DATABASE question

Hi,
In my SQL Server 2000,
RESTORE DATABASE ETCService
FROM TAPE = '\\.\Tape0'
it gave me an error saying I have no right to do the Restore.
What kind of privilege do I need for doing the Restore Database?
Thanks for help.
JasonIt depends on whether the database you want to restore already exist or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> Hi,
> In my SQL Server 2000,
> RESTORE DATABASE ETCService
> FROM TAPE = '\\.\Tape0'
> it gave me an error saying I have no right to do the Restore.
> What kind of privilege do I need for doing the Restore Database?
> Thanks for help.
>
> Jason
>|||Thanks Tibor.
It's already existed!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ¼¶¼g©ó¶l¥ó·s»D:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.gbl...
> It depends on whether the database you want to restore already exist or
> not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> In my SQL Server 2000,
>> RESTORE DATABASE ETCService
>> FROM TAPE = '\\.\Tape0'
>> it gave me an error saying I have no right to do the Restore.
>> What kind of privilege do I need for doing the Restore Database?
>> Thanks for help.
>>
>> Jason
>|||Jason
Are you a member of sysadmin server role dbcreator fixed server role?
Probably not, am I right?
db_owner fixed database role do not have RESTORE permissions.
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ez1JmU5EHHA.3540@.TK2MSFTNGP02.phx.gbl...
> Thanks Tibor.
> It's already existed!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> ¼¶¼g©ó¶l¥ó·s»D:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.gbl...
>> It depends on whether the database you want to restore already exist or
>> not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
>> news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> In my SQL Server 2000,
>> RESTORE DATABASE ETCService
>> FROM TAPE = '\\.\Tape0'
>> it gave me an error saying I have no right to do the Restore.
>> What kind of privilege do I need for doing the Restore Database?
>> Thanks for help.
>>
>> Jason
>>
>|||Here's a section from Books Online, RESTORE DATABASE, Permissions:
"If the database being restored does not exist, the user must have CREATE DATABASE permissions to be
able to execute RESTORE. If the database exists, RESTORE permissions default to members of the
sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is always readily available
to the server. Because fixed database role membership can be checked only when the database is
accessible and undamaged, which is not always the case when RESTORE is executed, members of the
db_owner fixed database role do not have RESTORE permissions."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ez1JmU5EHHA.3540@.TK2MSFTNGP02.phx.gbl...
> Thanks Tibor.
> It's already existed!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> ¼¶¼g©ó¶l¥ó·s»D:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.gbl...
>> It depends on whether the database you want to restore already exist or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
>> news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> In my SQL Server 2000,
>> RESTORE DATABASE ETCService
>> FROM TAPE = '\\.\Tape0'
>> it gave me an error saying I have no right to do the Restore.
>> What kind of privilege do I need for doing the Restore Database?
>> Thanks for help.
>>
>> Jason
>>
>

RESTORE DATABASE question

Hi,
In my SQL Server 2000,
RESTORE DATABASE ETCService
FROM TAPE = '\\.\Tape0'
it gave me an error saying I have no right to do the Restore.
What kind of privilege do I need for doing the Restore Database?
Thanks for help.
Jason
Thanks Tibor.
It's already existed!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> glsD:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.g bl...
> It depends on whether the database you want to restore already exist or
> not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
|||Jason
Are you a member of sysadmin server role dbcreator fixed server role?
Probably not, am I right?
db_owner fixed database role do not have RESTORE permissions.
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ez1JmU5EHHA.3540@.TK2MSFTNGP02.phx.gbl...
> Thanks Tibor.
> It's already existed!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> glsD:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.g bl...
>

RESTORE DATABASE question

Hi,
In my SQL Server 2000,
RESTORE DATABASE ETCService
FROM TAPE = '\\.\Tape0'
it gave me an error saying I have no right to do the Restore.
What kind of privilege do I need for doing the Restore Database?
Thanks for help.
JasonIt depends on whether the database you want to restore already exist or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> Hi,
> In my SQL Server 2000,
> RESTORE DATABASE ETCService
> FROM TAPE = '\\.\Tape0'
> it gave me an error saying I have no right to do the Restore.
> What kind of privilege do I need for doing the Restore Database?
> Thanks for help.
>
> Jason
>|||Thanks Tibor.
It's already existed!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> glsD:uRmHKJ5EHH
A.3932@.TK2MSFTNGP02.phx.gbl...
> It depends on whether the database you want to restore already exist or
> not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:OWpIs%234EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>|||Jason
Are you a member of sysadmin server role dbcreator fixed server role?
Probably not, am I right?
db_owner fixed database role do not have RESTORE permissions.
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ez1JmU5EHHA.3540@.TK2MSFTNGP02.phx.gbl...
> Thanks Tibor.
> It's already existed!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> glsD:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.gbl...
>|||Here's a section from Books Online, RESTORE DATABASE, Permissions:
"If the database being restored does not exist, the user must have CREATE DA
TABASE permissions to be
able to execute RESTORE. If the database exists, RESTORE permissions default
to members of the
sysadmin and dbcreator fixed server roles and the owner (dbo) of the databas
e (for the FROM
DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is al
ways readily available
to the server. Because fixed database role membership can be checked only wh
en the database is
accessible and undamaged, which is not always the case when RESTORE is execu
ted, members of the
db_owner fixed database role do not have RESTORE permissions."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:ez1JmU5EHHA.3540@.TK2MSFTNGP02.phx.gbl...
> Thanks Tibor.
> It's already existed!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> glsD:uRmHKJ5EHHA.3932@.TK2MSFTNGP02.phx.gbl...
>sql

RESTORE DATABASE is terminating abnormally.

Hello,

I am getting the rather odd error below; hope you can help.

RESTORE DATABASE is terminating abnormally. File 'C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed by
'sysft_KB_SearchAssetsCatalog'(65540) and 'sysft_KB_SearchCatalog'(65537).
The WITH MOVE clause can be used to relocate one or more files.

I get this from an app our company uses to programatically restore
databases. Below are the queries we are using:

Create database trial_44testingc
select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
RESTORE FILELISTONLY FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
RESTORE DATABASE trial_44testingc FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY , MOVE
'43_trialmaster' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc'

This had been working great until I made some updates to the table structure
of the db and created a new backup.

Any ideas?

TIA
JakeGitarJake (spamaintme@.oz.net) writes:

Quote:

Originally Posted by

I am getting the rather odd error below; hope you can help.
>
RESTORE DATABASE is terminating abnormally. File 'C:\Program
Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed by
'sysft_KB_SearchAssetsCatalog'(65540) and 'sysft_KB_SearchCatalog'(65537).
The WITH MOVE clause can be used to relocate one or more files.

I would not say that this is particularly odd, given:

Quote:

Originally Posted by

'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc'

You are trying to place three logical files on the same physical file. That
cannot work out well.

Seems like you need to talk with the person/vendor who wrote the app
that runs your RESTORE commands.

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

RESTORE DATABASE is terminating abnormally

Hello,
I am getting the rather odd error below; hope you can help.
RESTORE DATABASE is terminating abnormally. File 'C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed by
'sysft_KB_SearchAssetsCatalog'(65540) and 'sysft_KB_SearchCatalog'(65537).
The WITH MOVE clause can be used to relocate one or more files.
I get this from an app our company uses to programatically restore
databases. Below are the queries we are using:
Create database trial_44testingc
select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
RESTORE FILELISTONLY FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
RESTORE DATABASE trial_44testingc FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY , MOVE
'43_trialmaster' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc'
This had been working great until I made some updates to the table structure
of the db and creeated a new backup.
Any ideas?
TIA
Jake
Can you try this?
Create database trial_44testingc
select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
RESTORE FILELISTONLY FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
RESTORE DATABASE trial_44testingc FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY , MOVE
'43_trialmaster' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc1', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc2', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc3', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc4'
"GitarJake" <spamaintme@.oz.net> wrote in message
news:j0zVh.17257$OT4.12355@.newsfe19.lga...
> Hello,
> I am getting the rather odd error below; hope you can help.
> RESTORE DATABASE is terminating abnormally. File 'C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed
> by 'sysft_KB_SearchAssetsCatalog'(65540) and
> 'sysft_KB_SearchCatalog'(65537). The WITH MOVE clause can be used to
> relocate one or more files.
> I get this from an app our company uses to programatically restore
> databases. Below are the queries we are using:
> Create database trial_44testingc
> select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
> RESTORE FILELISTONLY FROM disk =
> 'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
> RESTORE DATABASE trial_44testingc FROM disk =
> 'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY ,
> MOVE '43_trialmaster' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
> TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
> 'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
> 'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
> 'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
> 'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc'
> This had been working great until I made some updates to the table
> structure of the db and creeated a new backup.
> Any ideas?
> TIA
> Jake
>
>

Restore database goes to error

Hello everyone,
I'm just restoring database with the ordinary way. But one of my database
reports an error:
"Location: execsql.cpp:230
Expression: exception != se_OK
SPID: 52
Process ID: 572"
SQL Command looks like this:
"USE MASTER RESTORE DATABASE [SourceDB] FROM DISK = 'C:\Backup.bak' WITH
REPLACE
,MOVE 'OldName' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\MDFFileName.mdf'
,MOVE 'OldName_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\MDFFileName_log.LDF'"
Any ideas?
Ilia
Hello,
Is this SQL 2000? Can you try restoring the database into a different SQL
Server machine and try if it is working. Just see the size of the database
also. MSDE can support
only a max of 2 GB MDF size.
Thanks
Hari
"ilia" <ilia@.discussions.microsoft.com> wrote in message
news:70D680CF-C9B9-44BA-BC39-93C2724FEBB7@.microsoft.com...
> Hello everyone,
> I'm just restoring database with the ordinary way. But one of my database
> reports an error:
> "Location: execsql.cpp:230
> Expression: exception != se_OK
> SPID: 52
> Process ID: 572"
> SQL Command looks like this:
> "USE MASTER RESTORE DATABASE [SourceDB] FROM DISK = 'C:\Backup.bak' WITH
> REPLACE
> ,MOVE 'OldName' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\MDFFileName.mdf'
> ,MOVE 'OldName_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\MDFFileName_log.LDF'"
> Any ideas?
> Ilia

Restore database from SQL 2000 to 2005 gives me a permission error

I am creating a backup of a database using Enterprise Manager. This creates
a
file (a rather large file). I transfer this file to a machine where SQL 2005
is installed. I choose to restore the database. I type in the destaination
database name and browse to find the "device"/file where my backup resides.
I
change the path for the transaction logs and data file because the paths on
the backup copy do not match the path to where SQL 2005 has the data stored.
Besides the backup copy references a drive letter that does not exist on the
SQL2005 machine. Anyway once all of that is done I click on OK then I get an
error indicating access deniied. No file name or path is given the error jus
t
indicates a permission problem. I did not have this problem with backup and
restore from SQL 2000 to another SQL 2000 machine. Any ideas?
Thank you.
Kevin BurtonDoes your SQL Server service account has permissions to write to that
location?
For example, if you are restoring to K:\Data then make sure the service
account has write permissions on the K: drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:

> I am creating a backup of a database using Enterprise Manager. This create
s a
> file (a rather large file). I transfer this file to a machine where SQL 20
05
> is installed. I choose to restore the database. I type in the destaination
> database name and browse to find the "device"/file where my backup resides
. I
> change the path for the transaction logs and data file because the paths o
n
> the backup copy do not match the path to where SQL 2005 has the data store
d.
> Besides the backup copy references a drive letter that does not exist on t
he
> SQL2005 machine. Anyway once all of that is done I click on OK then I get
an
> error indicating access deniied. No file name or path is given the error j
ust
> indicates a permission problem. I did not have this problem with backup an
d
> restore from SQL 2000 to another SQL 2000 machine. Any ideas?
> Thank you.
> Kevin Burton|||I am guessing NTFS permissions with the agent
"Kevin Burton" wrote:

> I am creating a backup of a database using Enterprise Manager. This create
s a
> file (a rather large file). I transfer this file to a machine where SQL 20
05
> is installed. I choose to restore the database. I type in the destaination
> database name and browse to find the "device"/file where my backup resides
. I
> change the path for the transaction logs and data file because the paths o
n
> the backup copy do not match the path to where SQL 2005 has the data store
d.
> Besides the backup copy references a drive letter that does not exist on t
he
> SQL2005 machine. Anyway once all of that is done I click on OK then I get
an
> error indicating access deniied. No file name or path is given the error j
ust
> indicates a permission problem. I did not have this problem with backup an
d
> restore from SQL 2000 to another SQL 2000 machine. Any ideas?
> Thank you.
> Kevin Burton

Restore database from SQL 2000 to 2005 gives me a permission error

I am creating a backup of a database using Enterprise Manager. This creates a
file (a rather large file). I transfer this file to a machine where SQL 2005
is installed. I choose to restore the database. I type in the destaination
database name and browse to find the "device"/file where my backup resides. I
change the path for the transaction logs and data file because the paths on
the backup copy do not match the path to where SQL 2005 has the data stored.
Besides the backup copy references a drive letter that does not exist on the
SQL2005 machine. Anyway once all of that is done I click on OK then I get an
error indicating access deniied. No file name or path is given the error just
indicates a permission problem. I did not have this problem with backup and
restore from SQL 2000 to another SQL 2000 machine. Any ideas?
Thank you.
Kevin BurtonDoes your SQL Server service account has permissions to write to that
location?
For example, if you are restoring to K:\Data then make sure the service
account has write permissions on the K: drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Kevin Burton" wrote:
> I am creating a backup of a database using Enterprise Manager. This creates a
> file (a rather large file). I transfer this file to a machine where SQL 2005
> is installed. I choose to restore the database. I type in the destaination
> database name and browse to find the "device"/file where my backup resides. I
> change the path for the transaction logs and data file because the paths on
> the backup copy do not match the path to where SQL 2005 has the data stored.
> Besides the backup copy references a drive letter that does not exist on the
> SQL2005 machine. Anyway once all of that is done I click on OK then I get an
> error indicating access deniied. No file name or path is given the error just
> indicates a permission problem. I did not have this problem with backup and
> restore from SQL 2000 to another SQL 2000 machine. Any ideas?
> Thank you.
> Kevin Burton|||I am guessing NTFS permissions with the agent
"Kevin Burton" wrote:
> I am creating a backup of a database using Enterprise Manager. This creates a
> file (a rather large file). I transfer this file to a machine where SQL 2005
> is installed. I choose to restore the database. I type in the destaination
> database name and browse to find the "device"/file where my backup resides. I
> change the path for the transaction logs and data file because the paths on
> the backup copy do not match the path to where SQL 2005 has the data stored.
> Besides the backup copy references a drive letter that does not exist on the
> SQL2005 machine. Anyway once all of that is done I click on OK then I get an
> error indicating access deniied. No file name or path is given the error just
> indicates a permission problem. I did not have this problem with backup and
> restore from SQL 2000 to another SQL 2000 machine. Any ideas?
> Thank you.
> Kevin Burtonsql

Restore database fails because database is in use.

I'm a newbie so please be gentle.

In attempting to run a restore I get the following error message:
"Exclusive access could not be obtained because the database is in
use." However, it doesn't appear that there are any connections to the
database. At the suggestion of another thread, I ran sp_who2 and there
are no connections to the database in question. I've been running the
same restore for months and all has been well, until yesterday...

I wrote an .asp page (below) to test connectivity to this database.
The vb code ran successfully and soon thereafter, my nightly restore
failed. Task manager does no show that the asp page is active.

Lastly, I did add a user ("jbtest") as well as change the option to use
either Windows authentication or SQL Server authentication in
Enterprise Manager.

Any ideas would be greatly appreciated.

Thanks.

<%@. Language=VBScript %
<%
companyName = "agemni"

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER=SQL
Server;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=Microsoft
Development Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &
";Trusted_Connection=No"

cnn.ConnectionTimeout = 300
cnn.CommandTimeout = 300
cnn.Open

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Preferences"
RS.Open strSQL, cnn

Response.Write "connection established with the <b>" & RS("Company
Name") & "</b> database. They are a <b>" & RS("street") & "</b>
customer. "

RS.Close
Set RS = Nothing
%<jbmccluskey@.satcountry.com> wrote in message
news:1112117455.317127.10740@.g14g2000cwa.googlegro ups.com...
> I'm a newbie so please be gentle.
> In attempting to run a restore I get the following error message:
> "Exclusive access could not be obtained because the database is in
> use." However, it doesn't appear that there are any connections to the
> database. At the suggestion of another thread, I ran sp_who2 and there
> are no connections to the database in question. I've been running the
> same restore for months and all has been well, until yesterday...
> I wrote an .asp page (below) to test connectivity to this database.
> The vb code ran successfully and soon thereafter, my nightly restore
> failed. Task manager does no show that the asp page is active.
> Lastly, I did add a user ("jbtest") as well as change the option to use
> either Windows authentication or SQL Server authentication in
> Enterprise Manager.
> Any ideas would be greatly appreciated.
> Thanks.

<snip
Probably the most reliable solution is to use ALTER DATABASE to get rid of
any open connections, wherever they're from:

alter database MyDB set restricted_user with rollback immediate

See Books Online for the other state options (single_user, offline etc.) -
one of them might suit your needs better.

You don't mention how you're running the restore, but if it's via a
scheduled job, you should also check that the selected database for the
restore step is set to master, not to the database you're restoring,
otherwise the job can block itself.

Simon|||Thanks for the reply Simon. The problem was that I was trying to run
the RESTORE in the database itself as opposed to running it under
MASTER.

JB

Restore database error: Microsoft SQL-DMO (ODBC SQLState: 42000)

I created a complete backup of a SQL Server 7 database so that I can
recreate it on a SQL Server 2000 database.
I tried 2 options but got the following errors.
1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
of the database and tried restoring it. I selected the backup
BaseFinal_Backup.BAK as the device.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
'BaseFinal_Data' cannot be restored to
'E:\Mssql\Data\BaseFinal_Data.mdf'.
Use WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
2. Using EM, I created an empty BaseFinal database. Using Tools |
Restore Database, selected BaseFinal and tried restoring it selecting
the device from my C drive.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The backup set holds a backup of a database other than the existing
'BaseFinal' database.
RESTORE DATABASE is terminating abnormally.
Thanks.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!When you are restoring with EM, check the Options tab, part "Restore
database files as:" in the middle. There you can change the path to the
files, so it points to a valid drive and folder.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I created a complete backup of a SQL Server 7 database so that I can
> recreate it on a SQL Server 2000 database.
> I tried 2 options but got the following errors.
> 1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
> of the database and tried restoring it. I selected the backup
> BaseFinal_Backup.BAK as the device.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
> 'BaseFinal_Data' cannot be restored to
> 'E:\Mssql\Data\BaseFinal_Data.mdf'.
> Use WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
> cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> 2. Using EM, I created an empty BaseFinal database. Using Tools |
> Restore Database, selected BaseFinal and tried restoring it selecting
> the device from my C drive.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The backup set holds a backup of a database other than the existing
> 'BaseFinal' database.
> RESTORE DATABASE is terminating abnormally.
> Thanks.
> PLEASE REPLY TO THE GROUP. THANKS.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.ldf'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:unJyQaFiEHA.4064@.TK2MSFTNGP12.phx.gbl...
> When you are restoring with EM, check the Options tab, part "Restore
> database files as:" in the middle. There you can change the path to the
> files, so it points to a valid drive and folder.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
> news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
>|||The following code did the trick.
RESTORE FILELISTONLY
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
RESTORE DATABASE DB1639
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
WITH MOVE 'DBFinal_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Data.mdf',
MOVE 'DBFinal_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Log.ldf'
GO
Thanks for the help.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restore database error: Microsoft SQL-DMO (ODBC SQLState: 42000)

I created a complete backup of a SQL Server 7 database so that I can
recreate it on a SQL Server 2000 database.
I tried 2 options but got the following errors.
1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
of the database and tried restoring it. I selected the backup
BaseFinal_Backup.BAK as the device.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
'BaseFinal_Data' cannot be restored to
'E:\Mssql\Data\BaseFinal_Data.mdf'.
Use WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
2. Using EM, I created an empty BaseFinal database. Using Tools |
Restore Database, selected BaseFinal and tried restoring it selecting
the device from my C drive.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The backup set holds a backup of a database other than the existing
'BaseFinal' database.
RESTORE DATABASE is terminating abnormally.
Thanks.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
When you are restoring with EM, check the Options tab, part "Restore
database files as:" in the middle. There you can change the path to the
files, so it points to a valid drive and folder.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I created a complete backup of a SQL Server 7 database so that I can
> recreate it on a SQL Server 2000 database.
> I tried 2 options but got the following errors.
> 1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
> of the database and tried restoring it. I selected the backup
> BaseFinal_Backup.BAK as the device.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
> 'BaseFinal_Data' cannot be restored to
> 'E:\Mssql\Data\BaseFinal_Data.mdf'.
> Use WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
> cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> 2. Using EM, I created an empty BaseFinal database. Using Tools |
> Restore Database, selected BaseFinal and tried restoring it selecting
> the device from my C drive.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The backup set holds a backup of a database other than the existing
> 'BaseFinal' database.
> RESTORE DATABASE is terminating abnormally.
> Thanks.
> PLEASE REPLY TO THE GROUP. THANKS.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.ldf'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:unJyQaFiEHA.4064@.TK2MSFTNGP12.phx.gbl...
> When you are restoring with EM, check the Options tab, part "Restore
> database files as:" in the middle. There you can change the path to the
> files, so it points to a valid drive and folder.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
> news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
>
|||The following code did the trick.
RESTORE FILELISTONLY
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
RESTORE DATABASE DB1639
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
WITH MOVE 'DBFinal_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Data.mdf',
MOVE 'DBFinal_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Log.ldf'
GO
Thanks for the help.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sql

Restore database error: Microsoft SQL-DMO (ODBC SQLState: 42000)

I created a complete backup of a SQL Server 7 database so that I can
recreate it on a SQL Server 2000 database.
I tried 2 options but got the following errors.
1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
of the database and tried restoring it. I selected the backup
BaseFinal_Backup.BAK as the device.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
'BaseFinal_Data' cannot be restored to
'E:\Mssql\Data\BaseFinal_Data.mdf'.
Use WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
2. Using EM, I created an empty BaseFinal database. Using Tools |
Restore Database, selected BaseFinal and tried restoring it selecting
the device from my C drive.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The backup set holds a backup of a database other than the existing
'BaseFinal' database.
RESTORE DATABASE is terminating abnormally.
Thanks.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!When you are restoring with EM, check the Options tab, part "Restore
database files as:" in the middle. There you can change the path to the
files, so it points to a valid drive and folder.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I created a complete backup of a SQL Server 7 database so that I can
> recreate it on a SQL Server 2000 database.
> I tried 2 options but got the following errors.
> 1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
> of the database and tried restoring it. I selected the backup
> BaseFinal_Backup.BAK as the device.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
> 'BaseFinal_Data' cannot be restored to
> 'E:\Mssql\Data\BaseFinal_Data.mdf'.
> Use WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
> cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> 2. Using EM, I created an empty BaseFinal database. Using Tools |
> Restore Database, selected BaseFinal and tried restoring it selecting
> the device from my C drive.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The backup set holds a backup of a database other than the existing
> 'BaseFinal' database.
> RESTORE DATABASE is terminating abnormally.
> Thanks.
> PLEASE REPLY TO THE GROUP. THANKS.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.ldf'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:unJyQaFiEHA.4064@.TK2MSFTNGP12.phx.gbl...
> When you are restoring with EM, check the Options tab, part "Restore
> database files as:" in the middle. There you can change the path to the
> files, so it points to a valid drive and folder.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
> news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
> > I created a complete backup of a SQL Server 7 database so that I can
> > recreate it on a SQL Server 2000 database.
> >
> > I tried 2 options but got the following errors.
> >
> > 1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
> > of the database and tried restoring it. I selected the backup
> > BaseFinal_Backup.BAK as the device.
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)
> >
> > Device activation error. The physical file name
> > 'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
> > 'BaseFinal_Data' cannot be restored to
> > 'E:\Mssql\Data\BaseFinal_Data.mdf'.
> >
> > Use WITH MOVE to identify a valid location for the file.
> > Device activation error. The physical file name
> > 'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
> > cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
> >
> > WITH MOVE to identify a valid location for the file.
> > RESTORE DATABASE is terminating abnormally.
> >
> > 2. Using EM, I created an empty BaseFinal database. Using Tools |
> > Restore Database, selected BaseFinal and tried restoring it selecting
> > the device from my C drive.
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)
> >
> > The backup set holds a backup of a database other than the existing
> > 'BaseFinal' database.
> >
> > RESTORE DATABASE is terminating abnormally.
> >
> > Thanks.
> >
> > PLEASE REPLY TO THE GROUP. THANKS.
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>|||The following code did the trick.
RESTORE FILELISTONLY
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
RESTORE DATABASE DB1639
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
WITH MOVE 'DBFinal_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Data.mdf',
MOVE 'DBFinal_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Log.ldf'
GO
Thanks for the help.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||There is nothing to panic. Just change the paths in the options tab giving the same directory where SQL server is present after selecting the backup file you needed to restore the database from.
No need to apply any queries in SQL analyzer,its just an easy funda i applied myself
From http://www.developmentnow.com/g/118_2004_8_0_0_476198/Restore-database-error-Microsoft-SQL-DMO-ODBC-SQLState-42000.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACE
You are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
--
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

Restore database error

I'm trying to restore a database and am getting the following error:
"MODIFY FILE encountered operating system error 112(There is not enough spac
e
on the disk.) while attempting to expand the physical file. Could not adjust
the space allocation for file 'MyFile_Log'. RESTORE database is terminating
abnormally."
What do I need to do to solve this issue?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Hi
You don't have enough disk space for the DB. Full stop.
Don't forget, the size of the backup does not indicate the size of the DB.
Look at RESTORE FILELISTONLY and RESTORE HEADERONLY to get the information
as to how much space you need on disk.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ninel g via webservertalk.com" <u9125@.uwe> wrote in message
news:5895a870b9427@.uwe...
> I'm trying to restore a database and am getting the following error:
> "MODIFY FILE encountered operating system error 112(There is not enough
> space
> on the disk.) while attempting to expand the physical file. Could not
> adjust
> the space allocation for file 'MyFile_Log'. RESTORE database is
> terminating
> abnormally."
> What do I need to do to solve this issue?
> Thanks,
> Ninel
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1sql