Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log Shipping

Author  Topic 

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 15:30:41
I am trying to implement log shipping using sql 2000 standard edition, Looks like Log shipping is not supported in 2000 standard edition. I tried to create backup and restore routines on production and standby server.

I am able to restore the complete backup but when i try to restore the log it gives me an error. Have any one seen this error before. If so how i can fix it.


Error message : The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.

nr
SQLTeam MVY

12543 Posts
Posted - 2005-02-07 : 15:48:44
You need to leave the database in standby mode after the restore.
see
http://www.mindsdoor.net/SQLAdmin/LogShipping.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 16:05:33
1) Take a full backup move the bak file to Standby server
2) Run a full restore on standby server following is the example
RESTORE DATABASE databasename
FROM DISK = 'd:\Data_Full_Backup.BAK'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'd:\undo_LogTesting.ldf',
MOVE 'Northwind' TO 'd:\LogTesting_Data.mdf',
MOVE 'Northwind_Log' TO 'd:\LogTesting_Log.ldf'

3) Take a log backup every 6 hours and move it to Standby server
Run a log restore on standby server following is the example

RESTORE LOG database
FROM DISK = 'd:\Log_Backup.BAK'
WITH
DBO_ONLY,
STANDBY = 'd:\undo_LogTesting.ldf'

once i execute the step i get an error. Is there some kind of configuration that needs to be set.




quote:
Originally posted by nr

You need to leave the database in standby mode after the restore.
see
http://www.mindsdoor.net/SQLAdmin/LogShipping.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 16:37:22
MICROSOFT PRODUCTS SUCKS....., NO PROPER DOCUMENTATION TO TROUBLE SHOOT ERRORS....

quote:
Originally posted by na2173

1) Take a full backup move the bak file to Standby server
2) Run a full restore on standby server following is the example
RESTORE DATABASE databasename
FROM DISK = 'd:\Data_Full_Backup.BAK'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'd:\undo_LogTesting.ldf',
MOVE 'Northwind' TO 'd:\LogTesting_Data.mdf',
MOVE 'Northwind_Log' TO 'd:\LogTesting_Log.ldf'

3) Take a log backup every 6 hours and move it to Standby server
Run a log restore on standby server following is the example

RESTORE LOG database
FROM DISK = 'd:\Log_Backup.BAK'
WITH
DBO_ONLY,
STANDBY = 'd:\undo_LogTesting.ldf'

once i execute the step i get an error. Is there some kind of configuration that needs to be set.




quote:
Originally posted by nr

You need to leave the database in standby mode after the restore.
see
http://www.mindsdoor.net/SQLAdmin/LogShipping.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 16:55:21
Perhaps the problem is with your DBO_ONLY option. BOL says that it used with the RECOVERY option which you aren't using.

quote:


RESTRICTED_USER

Restricts access for the newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In SQL Server 2000, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility.

Use with the RECOVERY option.



Tara
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 17:14:12
I am using "DBO_ONLY" OPTION.

quote:
Originally posted by tduggan

Perhaps the problem is with your DBO_ONLY option. BOL says that it used with the RECOVERY option which you aren't using.

quote:


RESTRICTED_USER

Restricts access for the newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In SQL Server 2000, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility.

Use with the RECOVERY option.



Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 17:18:18
I think you missed my point. You aren't using the RECOVERY option, so you can't use that RESTRICTED_USER option, at least according to BOL you can't (haven't ever used that option before).

Tara
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 17:55:19
I replace dbo_only with Restricted user. And still it is giving me the same error.

The database is restoring succesfully on the box where the backup occured. It restores the complete backup with standby option and also restored logs with stanby option with noproblem but when i move the backup files and log files to some other server it fails when i restore the log with standby option. do i need to setup some thing is msdb database.



quote:
Originally posted by tduggan

I think you missed my point. You aren't using the RECOVERY option, so you can't use that RESTRICTED_USER option, at least according to BOL you can't (haven't ever used that option before).

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 17:58:06
You are still missing my point. You can't use the RESTRISTED_USER or DBO_ONLY option unless you are using the WITH RECOVERY option, which you can't use for log shipping. So did you try the RESTORE LOG command without the WITH DBO_ONLY option (and also without WITH RESTRICTED_USER option)?

Nothing needs to be setup in msdb database.

Tara
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 18:03:39
Yes I tried removing the option and still throwing the same error.

Server: Msg 4330, Level 16, State 4, Line 1
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

quote:
Originally posted by tduggan

You are still missing my point. You can't use the RESTRISTED_USER or DBO_ONLY option unless you are using the WITH RECOVERY option, which you can't use for log shipping. So did you try the RESTORE LOG command without the WITH DBO_ONLY option (and also without WITH RESTRICTED_USER option)?

Nothing needs to be setup in msdb database.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 18:07:53
Instead of your RESTORE LOG command, try this:

RESTORE LOG database
FROM DISK = 'd:\Log_Backup.BAK'
WITH NORECOVERY

Does that work? If not, then are you sure that Log_Backup.BAK contains a tlog backup and not a full backup? Typically, tlog backups have an extension of TRN.

Tara
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 18:11:24
I tried restoring using the with norecovery it is still giving me the same error, yes .bak is the log, because i was performing a test so took the log backup as .bak file

quote:
Originally posted by tduggan

Instead of your RESTORE LOG command, try this:

RESTORE LOG database
FROM DISK = 'd:\Log_Backup.BAK'
WITH NORECOVERY

Does that work? If not, then are you sure that Log_Backup.BAK contains a tlog backup and not a full backup? Typically, tlog backups have an extension of TRN.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 18:17:00
Was the transaction log truncated on the source database? If so, that invalidates all future transaction log backups. A full backup must be performed after the tlog is truncated, then you can use the tlog chain.

Tara
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 18:21:39
This is what i did ,

I did a complete backup for a Northwind database , ran a update query to update lastname, Backed up the transaction log and moved it to the standby server. I am able to restore complete backup on standby server but i am getting an error when i am trying to restore log backup on standby server. Both the servers are running on service pack 3a.


quote:
Originally posted by tduggan

Was the transaction log truncated on the source database? If so, that invalidates all future transaction log backups. A full backup must be performed after the tlog is truncated, then you can use the tlog chain.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 18:22:50
Please post the backup command that you used for the tlog. While you're at it, please post both backup commands.

Tara
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-02-07 : 18:27:18
quote:
Originally posted by tduggan

Please post the backup command that you used for the tlog. While you're at it, please post both backup commands.

Tara


both restore commands while you are at it as well.



-ec
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 18:28:47
Following Commands were used

BACKUP DATABASE Northwind TO DISK = 'C:\test\PCC_Data_Full_Backup.BAK'
go
update employees
set lastname ='test'
go
BACKUP LOG northwind TO disk = 'c:\test\PCC_Log_Backup.BAK'


-- Restore database backup
RESTORE DATABASE LogTesting
FROM DISK = 'e:\test\PCC_Data_Full_Backup.BAK'
WITH
REPLACE,
STANDBY = 'e:\test\undo_testLogTesting.ldf',
MOVE 'Northwind' TO 'e:\test\LogTesting_Data.mdf',
MOVE 'Northwind_Log' TO 'e:\test\LogTesting_Log.ldf'


go
RESTORE LOG LogTesting
FROM DISK = 'e:\test\PCC_Log_Backup.BAK'
WITH NORECOVERY,
MOVE 'Northwind' TO 'e:\test\LogTesting_Data.mdf',
MOVE 'Northwind_Log' TO 'e:\test\LogTesting_Log.ldf'

quote:
Originally posted by eyechart

quote:
Originally posted by tduggan

Please post the backup command that you used for the tlog. While you're at it, please post both backup commands.

Tara


both restore commands while you are at it as well.



-ec
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts
Posted - 2005-02-07 : 18:35:55
I believe NORECOVERY and STANDBY are mutually exclusive. Your RESTORE LOG statement should also use the STANDBY argument you used in your RESTORE DATABASE command.



-ec
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 18:36:15
So did you change Northwind's recovery model to FULL then?

I'm testing your script out now (I had to change the recovery model to FULL for Northwind though as mine was SIMPLE).

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-02-07 : 18:39:11
This script worked fine for me:



ALTER DATABASE Northwind SET RECOVERY FULL

BACKUP DATABASE Northwind TO DISK = 'C:\temp\PCC_Data_Full_Backup.BAK' WITH INIT
go
BACKUP LOG northwind TO disk = 'c:\temp\PCC_Log_Backup.BAK' WITH INIT


-- Restore database backup
RESTORE DATABASE LogTesting 
FROM DISK = 'c:\temp\PCC_Data_Full_Backup.BAK'
WITH
REPLACE,
STANDBY = 'c:\temp\undo_testLogTesting.ldf',
MOVE 'Northwind' TO 'c:\temp\LogTesting_Data.mdf', 
MOVE 'Northwind_Log' TO 'c:\temp\LogTesting_Log.ldf'


go
RESTORE LOG LogTesting
FROM DISK = 'c:\temp\PCC_Log_Backup.BAK'
WITH NORECOVERY,
MOVE 'Northwind' TO 'c:\temp\LogTesting_Data.mdf', 
MOVE 'Northwind_Log' TO 'c:\temp\LogTesting_Log.ldf'



Tara
Go to Top of Page

na2173
Starting Member

38 Posts
Posted - 2005-02-07 : 18:44:51
It has some thing do with INIT, IT Worked for me now when i use INIT. Thanks your help i really appreciate your help.



quote:
Originally posted by tduggan

This script worked fine for me:



ALTER DATABASE Northwind SET RECOVERY FULL

BACKUP DATABASE Northwind TO DISK = 'C:\temp\PCC_Data_Full_Backup.BAK' WITH INIT
go
BACKUP LOG northwind TO disk = 'c:\temp\PCC_Log_Backup.BAK' WITH INIT


-- Restore database backup
RESTORE DATABASE LogTesting 
FROM DISK = 'c:\temp\PCC_Data_Full_Backup.BAK'
WITH
REPLACE,
STANDBY = 'c:\temp\undo_testLogTesting.ldf',
MOVE 'Northwind' TO 'c:\temp\LogTesting_Data.mdf', 
MOVE 'Northwind_Log' TO 'c:\temp\LogTesting_Log.ldf'


go
RESTORE LOG LogTesting
FROM DISK = 'c:\temp\PCC_Log_Backup.BAK'
WITH NORECOVERY,
MOVE 'Northwind' TO 'c:\temp\LogTesting_Data.mdf', 
MOVE 'Northwind_Log' TO 'c:\temp\LogTesting_Log.ldf'



Tara
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site