November 21, 2008 at 1:10 pm
yeah,
Can we take the tail log backup of a database and put it into recovery mode and applying this tail log backup at the end of restore sequence in SQL Server 2000?
is this possible only in SQL Server 2005?
November 25, 2008 at 9:36 am
I only work with SQL Server 2000, and have never heard of it.
A simple google search seems to suggest that this concept only exists in SQL Server 2005 and beyond.
For example: http://www.sqlmag.com/Article/ArticleID/98376/sql_server_98376.html
November 25, 2008 at 10:26 am
It's not a new concept. It existed on SQL 2000 as well. Probably 7 too, though I don't have any docs.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
Significant quotes:
Tail of transaction log
The transactions that have been committed but not backed up since the previous complete or differential database backup or transaction log backup.
Restoring to a Point of Failure
Under certain circumstances it may be possible to recover the database to a point where it failed. Recovery to a point of failure is available only when the database is in the Full or Bulk-Logged Recovery models. Restoring a database to the point of failure requires the following backup components:
1. A backup of the tail of transaction log.
2. A complete database or file/filegroup backup. Optionally we may also need differential or file differential backups.
3. A sequence of all transaction log backups from the oldest complete (or file/filegroup backup) or the latest differential (or file/filegroup differential) backup restored.
Step 1: Backup of the tail of transaction log
To perform a backup of the tail of transaction log if database files are damaged, see the procedure in article Q253817 in the Microsoft Knowledge Base.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2008 at 1:08 pm
Thanks Gail,
I have tried with tail backup in sql server 2000 and Iam able get point in time recovery. But one change I observed,between 2000 and 2005 is, In 2000 when we take the tail backup the of database,that database is not going to recovery mode where as in sql server 2005 the database will be in recovery mode sothat no changes can be done to the database untill it is recovered.
can we put the database in recovery mode after taking the tail backup in sql server 2000?
Plz correct me If I misunderstood. I appreciate your help
Thanks Again
November 26, 2008 at 7:49 am
kotlas7 (11/25/2008)
can we put the database in recovery mode after taking the tail backup in sql server 2000?
Sure. Add the NoRecovery option to the log backup statement along with no_truncate.
The fact that the gui doesn't do it by default is immaterial. I doubt I'd be trying to use the GUI in a disaster anyway.
http://msdn.microsoft.com/en-us/library/aa225964(SQL.80).aspx
BACKUP LOG { database_name | @database_name_var }
{
TO [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
}
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 11:33 am
Thank you
Iam trying to take the tail backup as you suggested, But Iam getting the following error?
backup log ABC
to disk='d:\abc.trn'
with no_truncate,norecovery
Server: Msg 3031, Level 16, State 1, Line 1
Option 'no_truncate' conflicts with option(s) 'norecovery'. Remove the conflicting option and reissue the statement.
plz advice me
November 26, 2008 at 11:49 am
norecovery can't be used with notrunate apparently. I don't see anything in BOL, but I bet you can remove the no_truncate and it will work.
http://msdn.microsoft.com/en-us/library/aa225964(SQL.80).aspx
November 26, 2008 at 12:02 pm
Yes, its worked after removing the no_truncate option.
But One thing still confusing me..In BOL, they mentioned tail backup is introduced in SQL Server 2005. But it also working in sql server 2000.
SQL Server 2008 Books Online (October 2008)
Tail-Log Backups
This topic is relevant only for databases that are using the full or bulk-logged recovery models.
In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.
November 26, 2008 at 12:05 pm
Ok, I've never actually tried running both. Isn't needed. If what you want is to take a log backup and switch the DB into recovery to prevent more changes before restoring, then norecovery is the only one you need.
Truncate_only is typically used when the mdf file is missing or damaged, ie, you're trying to backup the last of the changes before restoring in a disaster situation. In that case, there's no need for norecovery as the DB is toast already.
Technically, the presence of the no_truncate option is what makes the log a tail-log backup, as no_truncate will force SQL to continue even with the mdf damaged of missing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 12:17 pm
kotlas7 (11/26/2008)
But One thing still confusing me..In BOL, they mentioned tail backup is introduced in SQL Server 2005. But it also working in sql server 2000.
No, they don't say that the tail log was introduced in 2005. They say that in 2005+ it's required when restoring over an existing database.
Big difference.
In 2000 and below, you could restore over an existing database without using the 'with replace' option and SQL would allow it even if there were log records not backed up. It meant that those changes would be lost.
In 2005, if you restore a database over an existing one without using the with replace and there are log records of the old db in place that have not been backed up, SQL throws an error.
alter database abc set recovery full
go
backup database abc to disk = 'C:\abc.bak'
go
use abc
go
Create table Dummy (id int)
go
insert into Dummy values (0)
go
use master
go
restore database abc from disk = = 'C:\abc.bak'
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "abc" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
On SQL 2000, that would have run without an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply