| Author |
 Topic  |
|
na2173
Starting Member
38 Posts |
 Posted - 02/07/2005 : 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
United Kingdom
12543 Posts |
Posted - 02/07/2005 : 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. |
 |
|
|
na2173
Starting Member
38 Posts |
Posted - 02/07/2005 : 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.
|
|
|
|
na2173
Starting Member
38 Posts |
Posted - 02/07/2005 : 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.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 02/07/2005 : 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 |
|
|
|
na2173
Starting Member
38 Posts |
Posted - 02/07/2005 : 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
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 02/07/2005 : 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 - 02/07/2005 : 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
USA
35007 Posts |
Posted - 02/07/2005 : 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 - 02/07/2005 : 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
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 02/07/2005 : 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 |
|
|
|
na2173
Starting Member
38 Posts |
Posted - 02/07/2005 : 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
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 02/07/2005 : 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 - 02/07/2005 : 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
USA
35007 Posts |
Posted - 02/07/2005 : 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
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 02/07/2005 : 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 - 02/07/2005 : 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
|
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 02/07/2005 : 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
USA
35007 Posts |
Posted - 02/07/2005 : 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
USA
35007 Posts |
Posted - 02/07/2005 : 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 |
|
|
|
na2173
Starting Member
38 Posts |
Posted - 02/07/2005 : 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
|
|
|
|
Topic |
|
Log Shipping
