SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log Shipping
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

na2173
Starting Member

38 Posts

Posted - 02/07/2005 :  15:30:41  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 02/07/2005 :  15:48:44  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 02/07/2005 :  16:05:33  Show Profile  Reply with Quote
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 - 02/07/2005 :  16:37:22  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  16:55:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/07/2005 :  17:14:12  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  17:18:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/07/2005 :  17:55:19  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  17:58:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/07/2005 :  18:03:39  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  18:07:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/07/2005 :  18:11:24  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  18:17:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/07/2005 :  18:21:39  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  18:22:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/07/2005 :  18:27:18  Show Profile  Reply with Quote
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 - 02/07/2005 :  18:28:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 02/07/2005 :  18:35:55  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  18:36:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
35937 Posts

Posted - 02/07/2005 :  18:39:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 02/07/2005 :  18:44:51  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000