sp_attach_db & Applying Transaction log backups

Hi All,

For one of my SQL 2000 production database, we are using daily volume level NAS snapshot backup of physical .mdf/.ldf files. Then after transaction log backup of db after every 30 minutes..

I need to make ready scripts to restore database with applying transaction log backup on it. Till the time I attach database its fine. When I go for applying logs into, it display below error message: -

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I also tried to do this after putting db in single/dbo mode. But failed to do.

Can we use daily SQL physical files snapshot + transaction logs backup strategy for backup and restore policy. If yes what is the way to restore transaction log backup on newly attached database.

Any thoughts will be highly appreciable..

Thanks in advance...

Regards,
Deepak

Full recovery model is set for this database...

I don't think that you can attach a copy of a .mdf file and then roll forward through transaction logs...? I think that you have to have a database backup...?

you could perhaps try renaming your .dbf as .bak and then using the restore database command rather than the attach db command...?

Cheers
Twan

Can anyone check the series of steps and find out what I am missing, and what should be the right procedure to apply transaction logs on newly attached database.. <br /><br />--Step - 1<br />EXEC sp_attach_db @dbname = N'test', <br /> @filename1 = N'Crogram FilesMicrosoft SQL ServerMSSQLdata est_Data.MDF', <br /> @filename2 = N'Crogram FilesMicrosoft SQL ServerMSSQLdata est_Log.LDF'<br /><br />--Step - 2<br />EXEC sp_dboption 'test', 'single user', 'TRUE'<br /><br />--Step - 3<br />RESTORE LOG test<br /> FROM disk='crogram FilesMicrosoft SQL ServerMSSQLBACKUP esttran.bak'<br /><br /><img src='/community/emoticons/emotion-6.gif' alt='' />

I made some more R&D, But little problem is still there..


--Step - 1
EXEC sp_attach_db @dbname = N'test',
@filename1 = N'Crogram FilesMicrosoft SQL ServerMSSQLdata est_Data.MDF',
@filename2 = N'Crogram FilesMicrosoft SQL ServerMSSQLdata est_Log.LDF'

--Step - 2
Taken full backup of newly attached database

--Step - 3
--Trying to restore datbase with step2 full backup and old transaction logs

RESTORE DATABASE test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP estfullnew.bak'
WITH NORECOVERY,
MOVE 'test_data' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_data.mdf',
MOVE 'test_log' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_log.ldf'
RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP esttran.bak'
WITH RECOVERY

-- Now i am getting LSN error like: -

Processed 104 pages for database 'test', file 'test_Data' on file 1.
Processed 1 pages for database 'test', file 'test_Log' on file 1.
RESTORE DATABASE successfully processed 105 pages in 0.070 seconds (12.200 MB/sec).
Server: Msg 4326, Level 16, State 1, Line 6
The log in this backup set terminates at LSN 5000000041200001, which is too early to apply to the database. A more recent log backup that includes LSN 5000000041400001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE LOG is terminating abnormally.

·By anyways can we suppress Log Sequence Number (LSN) error and restore a previously taken transaction log backup?? [As we know that I have updated mdf/ ldf files and continually taken latest transaction log backup]<br /><br />·Can#%92t we have updated databases, if u have old mdf/ ldf file and up to dated transaction log backup file.??<br /><br />·Or, we should throw away all Snapshot utilities, because using snapshots we can have only old physical files and No organization can afford to loose a single minute transactions??<br /><br /><br /><img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' /> <img src='/community/emoticons/emotion-6.gif' alt='' />

<img src='/community/emoticons/emotion-6.gif' alt='' /> [ No Answers !! ]

Hi ya,

You may possibly be able to do the following

move the mdf and ldf files from the snapshot to the backup directory

RESTORE DATABASE test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_data.mdf'
WITH NORECOVERY,
MOVE 'test_data' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_data.mdf',
MOVE 'test_log' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_log.ldf'

RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_log.mdf'
WITH RECOVERY
RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP esttran.bak'
WITH RECOVERY


If the database is brought online by a attach command then any old log files are invalid and can't be rolled forward...

Cheers
Twan

Hi Twan,
I follow the same set of procedure..But got failed again..

--THIS SET OF COMMAND FAILED LIKE
RESTORE DATABASE test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_data.mdf'
WITH NORECOVERY,
MOVE 'test_data' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_data.mdf',
MOVE 'test_log' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_log.ldf'

RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_log.ldf'
WITH NORECOVERY
RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP esttran.bak'
WITH RECOVERY

/*
Server: Msg 3242, Level 16, State 1, Line 1

The file on device 'Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_data.mdf' is not a valid Microsoft Tape Format backup set.

Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 913, Level 16, State 8, Line 7
Could not find database ID 65535. Database may not be activated yet or may be in transition.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Server: Msg 913, Level 16, State 8, Line 10
Could not find database ID 65535. Database may not be activated yet or may be in transition.
Server: Msg 3013, Level 16, State 1, Line 10
RESTORE LOG is terminating abnormally.
*/

I ALSO TRIED TO EXECUTE AFTER CONVERTING .MDF/.LDF TO .BAK FILES...BUT STILL THE SAME ERROR

RESTORE DATABASE test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_data.bak'
WITH NORECOVERY,
MOVE 'test_data' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_data.mdf',
MOVE 'test_log' TO 'crogram FilesMicrosoft SQL ServerMSSQLData est_log.ldf'

RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_log.bak'
WITH NORECOVERY
RESTORE LOG test
FROM disk='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP esttran.bak'
WITH RECOVERY

/*
Server: Msg 3242, Level 16, State 1, Line 1
The file on device 'Crogram FilesMicrosoft SQL ServerMSSQLBACKUP est_data.bak' is not a valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 913, Level 16, State 8, Line 7
Could not find database ID 65535. Database may not be activated yet or may be in transition.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Server: Msg 913, Level 16, State 8, Line 10
Could not find database ID 65535. Database may not be activated yet or may be in transition.
Server: Msg 3013, Level 16, State 1, Line 10
RESTORE LOG is terminating abnormally.

*/

-----------------------Please help ------------------------------Please help----------------------

Hi ya,

I'd say that your pretty much out of options then as far as restoring from a NAS snapshot of a .mdf file. So you can restore back to that snapshot, but are not able to roll forward past that point.

This makes a snapshot not so useful... So your alternative then would be to do regular SQLServer backups and snapshot those...

Cheers
Twan

Can we take use of file and filegroup restore..or something kind of procedure to make this backup/restore strategy working..

Hi ya,

I think not... I think that the basic problem is that SQL won't allow you to bring up a file without recovery unless it is from a backup. This implies that you then can't roll forward unless you started with a backup file

Cheers
Twan

Hi Twan,

For making any database ready to restore transaction log files we need to made following changes...

sp_configure 'allow updates',1
reconfigure with override
update sysdatabases set status=48 where name='test'

if database status is set to 48 in sysdatabases tables, you can see its ready to accept more transaction logs to be applied on it.

BUT I AM FAILING AGAIN TO APPLY TRANSACTION LOG BACKUP DUE TO LSN MISMATCH..

Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 5000000045000001, which is too late to apply to the database. An earlier log backup that includes LSN 5000000043500001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.


--Can I have any hint to play with LSN number???

Hi all,<br /><br />Can BackupSet table under MSDB database play backend role..For defining LSN numbers to backupsets..<br />it has got too many fields related to above error..<br /><br />I have got some entry's from this table like: -<br /><br />first_lsn last_lsn checkpoint_lsn <br /><br />5000000043100001 5000000043500001 5000000043100003 <br />5000000045000001 5000000045400001 5000000045000003 <br /><br /><br /><br /><br />backupset : Contains a row for each backup set. This table is stored in the msdb database.<br /><br />Column name Data type Description <br />backup_set_id int NOT NULL IDENTITY PRIMARY KEY Unique backup set identification number that identifies the backup set. <br />backup_set_uuid uniqueidentifier<br />NOT NULL Unique backup set identification number that identifies the backup set on the media. <br />media_set_id int NOT NULL REFERENCES backupmediaset<br />(media_set_id) Unique media set identification number that identifies the media set containing the backup set. <br />first_family_number tinyint NULL Family number of the media where the backup set starts. <br />first_media_number smallint NULL Media number of the media where the backup set starts. <br />last_family_number tinyint NULL Family number of the media where the backup set ends. <br />last_media_number smallint NULL Media number of the media where the backup set ends. <br />catalog_family_<br />number tinyint NULL Family number of the media containing the start of the backup set directory. <br />catalog_media_number smallint NULL Media number of the media containing the start of the backup set directory. <br />position int NULL Backup set position used in the restore operation to locate the appropriate backup set and files. <br />expiration_date datetime NULL Date and time the backup set expires. <br />software_vendor_id int NULL Identification number of the software vendor writing the backup media header. <br />name nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL Name of the backup set. <br />description nvarchar(255) NULL Description of the backup set. <br />user_name nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL Name of the user performing the backup operation. <br />software_major_version tinyint NULL Microsoft® SQL Server™ major version number. <br />software_minor_<br />version tinyint NULL SQL Server minor version number. <br />software_build_version smallint NULL SQL Server build number. <br />time_zone smallint NULL Difference between local time (where the backup operation is taking place) and Universal Coordinated Time (UCT) in 15-minute intervals. Values can be -48 through +48, inclusive. A value of 127 indicates unknown. For example, -20 is Eastern Standard Time (EST) or 5 hours after UCT. <br />mtf_minor_version tinyint NULL Microsoft Tape Format minor version number. <br />first_lsn numeric(25,0) NULL Log sequence number of the first or oldest log record in the backup set. <br />last_lsn numeric(25,0) NULL Log sequence number of the last or newest log record in the backup set. <br />checkpoint_lsn numeric(25,0) NULL Log sequence number of the log record where recovery must start. <br />database_backup_lsn numeric(25,0) NULL Log sequence number of the most recent full database backup. <br />database_creation_date datetime NULL Date and time the database was originally created. <br />backup_start_date datetime NULL Date and time the backup operation started. <br />backup_finish_date datetime NULL Date and time the backup operation finished. <br />type char(1) NULL Backup type. Can be: <br />D = Database.<br />I = Database Differential.<br />L = Log.<br />F = File or Filegroup.<br /> <br />sort_order smallint NULL Sort order of the server performing the backup operation. <br />code_page smallint NULL Code page of the server performing the backup operation. <br />compatibility_level tinyint NULL Compatibility level setting for the database. Can be: <br />60 = SQL Server version 6.0.<br />65 = SQL Server 6.5.<br />70 = SQL Server 7.0.<br /> <br />database_version int NULL Database version number. <br />backup_size numeric(20,0) NULL Size of the backup set, in bytes. <br />database_name nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL Name of the database involved in the backup operation. <br />server_name nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL Name of the server running the SQL Server backup operation. <br />machine_name nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL Name of the computer running SQL Server. <br />flags int NULL Flag bits: <br />1 = Backup contains minimally logged data.<br />2 = WITH SNAPSHOT was used.<br />4 = Database was read-only at time of backup.<br />8 = Database was in single-user mode at time of backup.<br /> <br />unicode_locale int NULL Unicode locale. <br />unicode_compare_style int NULL Unicode compare style. <br />collation_name nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL Collation name. <br />

The backupset table only stores the info for historical purposes, the LSN will be inside the actual log and data file. You may be able to 'play' with them using a hex editor, but you'd definitely be outside the realm of support with ms...<br /><br />as Satya asked, are you actually trying to recover a database that is down, and therefore you need to get whatever you can restored, or are you doing a proof of concept (in the case of the latter I'd say you've definitely proven that it is not viable for a production grade backup solution <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> )<br /><br />Cheers<br />Twan

All this is I am doing for learning, on a test database. I am not caught in any tough situation where i need to recover production database due to this. But the same backup/restore strategy was chosen by one of our old DBA..
Now before any crash happen we are gonna to change it.. In the mean time i am also trying to find another way.

I agree with Twan that, LSN will be inside the actual log and data files..but SQL Error gives me correct number of LSN from backup set and i just have to try to match this with BackupSet table.

/* The log in this backup set begins at LSN 5000000045000001, which is too late to apply to the database. An earlier log backup that includes LSN 5000000043500001 can be restored. */

I will be doing R&D on this..Lets see what outcome I get...""Hoping for the Best""

--Deepak Kumar.
Noida (UP) INDIA

Ooops, I got failed..even after playiing with lots of MSDB database's bakcup tabales..

msdb..backupset
msdb..restoreHistory
msdb..restorefilegroup
msdb..restorefile
msdb..backupfile
msdb..backupMediaSet

I made lot of changes in first_lsn, last_lsn, checkpoint_lsn, backup_set_id and restore_History_id columns of above listed tables..BUT NOTHING COULD STOP LSN MISMATCH ERROR..

As per Twan, I believe.. there are also some entry's in physical bakcup files for checking LSN.. Any change or even dropping whole row did not help me for restoration of transaction log after attaching database.


Thanks a ton Champs..For helping/suggesting/informing me..it was quite a nice learning session for me and Now We are gonna to change backup/restore policy..and also throwing all snapshot utilities...


--DeepakK