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.seehttp://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. |
|
|
na2173
Starting Member
38 Posts |
Posted - 2005-02-07 : 16:05:33
|
1) Take a full backup move the bak file to Standby server2) Run a full restore on standby server following is the exampleRESTORE DATABASE databasenameFROM DISK = 'd:\Data_Full_Backup.BAK'WITHDBO_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 serverRun a log restore on standby server following is the exampleRESTORE LOG databaseFROM 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.seehttp://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.
|
|
|
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 server2) Run a full restore on standby server following is the exampleRESTORE DATABASE databasenameFROM DISK = 'd:\Data_Full_Backup.BAK'WITHDBO_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 serverRun a log restore on standby server following is the exampleRESTORE LOG databaseFROM 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.seehttp://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.
|
|
|
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_USERRestricts 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 |
|
|
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_USERRestricts 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
|
|
|
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 |
|
|
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
|
|
|
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 |
|
|
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 1The 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 1RESTORE 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
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-07 : 18:07:53
|
Instead of your RESTORE LOG command, try this:RESTORE LOG databaseFROM DISK = 'd:\Log_Backup.BAK'WITH NORECOVERYDoes 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 |
|
|
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 filequote: Originally posted by tduggan Instead of your RESTORE LOG command, try this:RESTORE LOG databaseFROM DISK = 'd:\Log_Backup.BAK'WITH NORECOVERYDoes 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
|
|
|
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 |
|
|
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
|
|
|
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 |
|
|
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 |
|
|
na2173
Starting Member
38 Posts |
Posted - 2005-02-07 : 18:28:47
|
Following Commands were usedBACKUP DATABASE Northwind TO DISK = 'C:\test\PCC_Data_Full_Backup.BAK'goupdate employeesset lastname ='test'goBACKUP LOG northwind TO disk = 'c:\test\PCC_Log_Backup.BAK'-- Restore database backupRESTORE DATABASE LogTesting FROM DISK = 'e:\test\PCC_Data_Full_Backup.BAK'WITHREPLACE,STANDBY = 'e:\test\undo_testLogTesting.ldf',MOVE 'Northwind' TO 'e:\test\LogTesting_Data.mdf', MOVE 'Northwind_Log' TO 'e:\test\LogTesting_Log.ldf'goRESTORE LOG LogTestingFROM 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
|
|
|
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 |
|
|
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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-07 : 18:39:11
|
This script worked fine for me:ALTER DATABASE Northwind SET RECOVERY FULLBACKUP DATABASE Northwind TO DISK = 'C:\temp\PCC_Data_Full_Backup.BAK' WITH INITgoBACKUP LOG northwind TO disk = 'c:\temp\PCC_Log_Backup.BAK' WITH INIT-- Restore database backupRESTORE DATABASE LogTesting FROM DISK = 'c:\temp\PCC_Data_Full_Backup.BAK'WITHREPLACE,STANDBY = 'c:\temp\undo_testLogTesting.ldf',MOVE 'Northwind' TO 'c:\temp\LogTesting_Data.mdf', MOVE 'Northwind_Log' TO 'c:\temp\LogTesting_Log.ldf'goRESTORE LOG LogTestingFROM 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 |
|
|
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 FULLBACKUP DATABASE Northwind TO DISK = 'C:\temp\PCC_Data_Full_Backup.BAK' WITH INITgoBACKUP LOG northwind TO disk = 'c:\temp\PCC_Log_Backup.BAK' WITH INIT-- Restore database backupRESTORE DATABASE LogTesting FROM DISK = 'c:\temp\PCC_Data_Full_Backup.BAK'WITHREPLACE,STANDBY = 'c:\temp\undo_testLogTesting.ldf',MOVE 'Northwind' TO 'c:\temp\LogTesting_Data.mdf', MOVE 'Northwind_Log' TO 'c:\temp\LogTesting_Log.ldf'goRESTORE LOG LogTestingFROM 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
|
|
|
Previous Page&nsp;
Next Page
|