restore transaction log

[joe]

I did a test on DATABASE1

I did a complete backup,
then I added a table called "TABLE1" with no data in it.

Now, I restored my backup,
of course I didn't see "TABLE1" there,

so I tried to restore transaction log,

RESTORE LOG DATABASE1
FROM DATABASE1_log
WITH RECOVERY

Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'DATABASE1_Log'. Update sysdevices
and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

what is the problem?
what did I do wrong?

[TLV]

Did you backup the log of database1 ?

"joe" <pear...@hotmail.com> wrote in message
news:#IfN0qEY...@TK2MSFTNGP09.phx.gbl...

[Tibor Karaszi]

The error was because you specified a logical backup device name which doesn't exist. Where did you put the
log backup? Can you show that backup command you executed?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"joe" <pear...@hotmail.com> wrote in message news:%23IfN0qE...@TK2MSFTNGP09.phx.gbl...

[joe]

well, whenever you created a database,
it always comes with LDF and MDF file.
.LDF is primary log file, I just recover that one right?
I'm a little confused because I have never done with transaction log backup
and recover it before.

"TLV" <t...@prontomail.com> wrote in message
news:eOKj$vEYEH...@TK2MSFTNGP10.phx.gbl...

[joe]

Hi,
I actually tried to use Create Database Backup Wizard.
but when comes to select one of three choices, complete backup, differential
backup and transaction log backup,
transaction log backup button is disabled, I don't know why so I can't
really do transaction log backup.

"Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote in
message news:uQ0hqxE...@tk2msftngp13.phx.gbl...

[Tibor Karaszi]

Log backup is probably disables because you are in simple recovery mode.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"joe" <pear...@hotmail.com> wrote in message news:uqzoI4EY...@TK2MSFTNGP10.phx.gbl...

[joe]

Okay, that's probably it.
now when I right-clicked on DATABASE1 --> go to property
go to option tab , I just changed recovery model to FULL.

On same page, maybe this is not a related question but
do you know what is the compatibility level number mean,
right now, it's setting to 80. what does 65 or 60 mean?

"Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote in

message news:OKZtu6EY...@TK2MSFTNGP11.phx.gbl...

[joe]

okay.. I am abled to backup my transaction log.
however, when i tried to restore it using
right-clicked DATABASE1 --> all tasks--> restore database

I checked restore checkbox for transaction backup, when I clicked ok, I got
error.

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4330: [Microsoft][ODBC SQL
Server Driver][SQL Server]The log in this backup set cannot be applied
because it is on a recovery path inconsistent with the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.

"joe" <pear...@hotmail.com> wrote in message

news:uqzoI4EY...@TK2MSFTNGP10.phx.gbl...

[Hari Prasad]

Hi,

Compatibily Level 80 is SQL 2000. In SQL2000 you have the option to run the
database in older versions. if you select either
60, 65 , 70 for you database then automatically your database behaviors to
be compatible with the specified earlier version of SQL Server.

60 -for SQL 6.0
65 for SQL 6.5
70 for SQL 7.0

So in your case compatibily 80 is perfect.

Now back to FULL Recovery model. After setting the FULL recovery model do
below steps:-

1. Perform a FULL database backup

2. After that schedule a Trasnaction log backup every 1 hour. THis will
backup your trasnaction log backup to hard disk and clears the LDF file.

Thanks
Hari
MCDBA

"joe" <pear...@hotmail.com> wrote in message

news:#FBvT$EYEHA...@TK2MSFTNGP09.phx.gbl...

[Hari Prasad]

Hi,

Take a FULL database backup and then doa Transaction log backup after the
recovery model change.
This will create back the backup chain.

After that restore the FULL database backup with NORECOVERY and load the
trasnaction log backup with RECOVERY.

See RESTORE DATABASE in books online.

Thanks
Hari
MCDBA

"joe" <pear...@hotmail.com> wrote in message

news:ujmWhEFY...@TK2MSFTNGP10.phx.gbl...

[joe]

yes, on the first step,
I did restore backup of DATABASE1 using following syntax.

RESTORE DATABASE DATABASE1 FROM FILE = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DATABASE1 backup.BAK' WITH NORECOVERY

RESTORE LOG DATABASE1 WITH RECOVERY

doesn't it look correct?
but it's weird sometimes that I got error like following, so I have to kill
spid before running step 1 (restore DATABASE)

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

"Hari Prasad" <hari_p...@hotmail.com> wrote in message
news:e3xhQLFY...@TK2MSFTNGP09.phx.gbl...

[Hari Prasad]

Hi,

The usage of RESTORE LOG command is incorrect. you have to specify the
transaction log file to restore

RESTORE LOG database1 from disk='c:\database_trans_backup.bak' WITH RECOVERY

--------------------------------------------
Error:- Exclusive access could not be obtained because the database is in
use.

Before doing a restore always set the database to sinle use mode by doing
this:-

ALTER DATABASE <dbname> set single_user with rollback immediate
go
RESTORE DATABASE command with Norecovery -- Follow the old syntax
go
RESTORE LOG command with Recovery -- Follow the above syntax
go
ALTER DATABASE <dbname> set multi_user

Thanks
Hari
MCDBA

"joe" <pear...@hotmail.com> wrote in message

news:#$Io6xFYE...@tk2msftngp13.phx.gbl...