PLEASE HELP --- RESTORE PROBLEM --- I NEED HELP

19 May 2006 1:29 PM

Ina

Super Help,

I would like to restore a database and I tried everything!

use master
RESTORE DATABASE Test_prod
from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
   WITH RECOVERY

-- Apply the first transaction log backup.
RESTORE LOG Test_prod
   from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
   WITH RECOVERY

go

and

the results:

/*-----------------------------
use master
RESTORE DATABASE Test_prod
from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
-----------------------------*/
Server: Msg 3101, Level 16, State 2, 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.
Server: Msg 3101, Level 16, State 2, Line 7
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.

I already tried to alter table to single_user and to restore action but
nothing... I can do it, please how can I do it?

Can someone help me? I do not know how to do it, I have done thing with
the master admin but for this database another user is set. test_user
could be that?

in the process info I have user for Test_prod user: tester ???

19 May 2006 2:24 PM

Kalen Delaney

Hi Ina

What version are you running? This information should always be supplied if
you are asking for help.

>> I already tried to alter table to single_user and to restore action but

nothing... I can do it, please how can I do it? <<

I presume you meant that you tried to alter the database not the table.
Can you show us exactly what you did, and what messages you got back?
What does 'but nothing' mean? Did you get an error? Did the command
complete?

Also, running sp_who will tell you what users are in a db, and sp_lock will
show you what processes have a lock on the database.

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

Show quote

"Ina" <roberta.inal***@gmail.com> wrote in message
news:1148045388.026165.79160@j73g2000cwa.googlegroups.com...
> Super Help,
>
> I would like to restore a database and I tried everything!
>
> use master
> RESTORE DATABASE Test_prod
> from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
>   WITH RECOVERY
>
> -- Apply the first transaction log backup.
> RESTORE LOG Test_prod
>   from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
>   WITH RECOVERY
>
> go
>
> and
>
> the results:
>
> /*-----------------------------
> use master
> RESTORE DATABASE Test_prod
> from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
> -----------------------------*/
> Server: Msg 3101, Level 16, State 2, 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.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
>
> I already tried to alter table to single_user and to restore action but
> nothing... I can do it, please how can I do it?
>
> Can someone help me? I do not know how to do it, I have done thing with
> the master admin but for this database another user is set. test_user
> could be that?
>
> in the process info I have user for Test_prod user: tester ???
>

19 May 2006 2:35 PM

Ray Mond

First thing: if the database already exists, ensure no one else is connected
to the database. Run

sp_who2

Check the dbname column, and the ProgramName column, to give you an idea of
what application the user is running.

Once you're got everybody off the database, next restore the full backup,
but not with the RECOVERY option if you want to restore trx logs after that.
You need to restore with NORECOVERY or STANDBY = ... e.g

RESTORE DATABASE Test_prod FROM DISK =
'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK' WITH NORECOVERY

Next, restore the trx logs, again with NORECOVERY or STANDBY = ... .

When you are done with the trx logs, run

RESTORE DATABASE Test_prod WITH RECOVERY

Ray Mond

19 May 2006 4:31 PM

Kalen Delaney

Ina

You have two different threads discussing this problem in .server. Please do
not start multiple discussions on the same topic, it will waste many
people's time.

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

Show quote

"Ina" <roberta.inal***@gmail.com> wrote in message
news:1148045388.026165.79160@j73g2000cwa.googlegroups.com...
> Super Help,
>
> I would like to restore a database and I tried everything!
>
> use master
> RESTORE DATABASE Test_prod
> from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
>   WITH RECOVERY
>
> -- Apply the first transaction log backup.
> RESTORE LOG Test_prod
>   from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
>   WITH RECOVERY
>
> go
>
> and
>
> the results:
>
> /*-----------------------------
> use master
> RESTORE DATABASE Test_prod
> from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
> -----------------------------*/
> Server: Msg 3101, Level 16, State 2, 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.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
>
> I already tried to alter table to single_user and to restore action but
> nothing... I can do it, please how can I do it?
>
> Can someone help me? I do not know how to do it, I have done thing with
> the master admin but for this database another user is set. test_user
> could be that?
>
> in the process info I have user for Test_prod user: tester ???
>

22 May 2006 12:03 AM

Michael Hotek

First problem is that you are trying to restore a database while there is a
connection to it. You can not have any connection to the database.

Second problem is that you are trying to restore the database with recovery
and then restore a tran log. You have to restore the database with
NORECOVERY in order to restore any subsequent transaction logs. The instant
you issue a restore command with the RECOVERY option, you can no longer
apply transaction logs.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

Show quote

"Ina" <roberta.inal***@gmail.com> wrote in message
news:1148045388.026165.79160@j73g2000cwa.googlegroups.com...
> Super Help,
>
> I would like to restore a database and I tried everything!
>
> use master
> RESTORE DATABASE Test_prod
> from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
>   WITH RECOVERY
>
> -- Apply the first transaction log backup.
> RESTORE LOG Test_prod
>   from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
>   WITH RECOVERY
>
> go
>
> and
>
> the results:
>
> /*-----------------------------
> use master
> RESTORE DATABASE Test_prod
> from disk= 'e:\MSSQL\BACKUP\TESTSANDBOXBACKUP.BAK'
> -----------------------------*/
> Server: Msg 3101, Level 16, State 2, 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.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
>
> I already tried to alter table to single_user and to restore action but
> nothing... I can do it, please how can I do it?
>
> Can someone help me? I do not know how to do it, I have done thing with
> the master admin but for this database another user is set. test_user
> could be that?
>
> in the process info I have user for Test_prod user: tester ???
>