Bkp Restore Failed

I am trying to restore backup and the system gives me error for LSN. No error no. is displayed but it seems that the system doesnot accept trn log restore.

My bkp strategy is:

Full Backup: weekly

Diff bkp: daily

T Log Bkp : every 2 hours.

But sometime I need to shrink Log file due to heavy growth. Hope that is not creating problem. The following is what i do for shrinking TLog file.

BACKUP LOG ABC TO ABC_BackUp

DBCC SHRINKFILE(ABC_Log, 500)

pL. guide why my restore fails.

you are right on the track... THe shrinking is creating the problem. shrinking log file step will invalidate the transaction log backup chain. So what you should have done after shrinking was take fullbackup of your database. If the transaction log size is the problem, then increase the frequency of the TL Backup. Now you have every 2 hourse, keep it every 15 min or so... it will control the size of the log and then there will be no need to shrink. If you are shrinking then the first step after the shrinking of log should be Full backup remember that.

Read follwing links

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu|||

Thanks. I tried restoring full bkp, diff bkp and trn bkp which is without any shrinkage and i get the same error.

ERROR:

The log in this backup set begins at LNS .....502600001, which is too recent to apply to the database. An earlier log backup
that includes LSN .....478700001 can be restored.

what is this error and why my restore fails.

|||I would recommend that you do a RESTORE HEADERONLY on all you backups and check the FirstLSN and LastLSN fields of your backup series. The values should not have any gaps. If there are, the backup would be invalid...more of like a "do 5 first after 4 and not 6" kind of thing is how I would explain LSNs|||

Thanks. When i try to exclude filed with gaps, i get the error:

System.Data.sqlclient.sqlerror: Invalid column name 'lightweight'

Following is the bkp procedure i follow;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pl. guide

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

Bkp Restore Failed

I am trying to restore backup and the system gives me error for LSN. No error no. is displayed but it seems that the system doesnot accept trn log restore.

My bkp strategy is:

Full Backup: weekly

Diff bkp: daily

T Log Bkp : every 2 hours.

But sometime I need to shrink Log file due to heavy growth. Hope that is not creating problem. The following is what i do for shrinking TLog file.

BACKUP LOG ABC TO ABC_BackUp

DBCC SHRINKFILE(ABC_Log, 500)

pL. guide why my restore fails.

you are right on the track... THe shrinking is creating the problem. shrinking log file step will invalidate the transaction log backup chain. So what you should have done after shrinking was take fullbackup of your database. If the transaction log size is the problem, then increase the frequency of the TL Backup. Now you have every 2 hourse, keep it every 15 min or so... it will control the size of the log and then there will be no need to shrink. If you are shrinking then the first step after the shrinking of log should be Full backup remember that.

Read follwing links

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu|||

Thanks. I tried restoring full bkp, diff bkp and trn bkp which is without any shrinkage and i get the same error.

ERROR:

The log in this backup set begins at LNS .....502600001, which is too recent to apply to the database. An earlier log backup
that includes LSN .....478700001 can be restored.

what is this error and why my restore fails.

|||I would recommend that you do a RESTORE HEADERONLY on all you backups and check the FirstLSN and LastLSN fields of your backup series. The values should not have any gaps. If there are, the backup would be invalid...more of like a "do 5 first after 4 and not 6" kind of thing is how I would explain LSNs|||

Thanks. When i try to exclude filed with gaps, i get the error:

System.Data.sqlclient.sqlerror: Invalid column name 'lightweight'

Following is the bkp procedure i follow;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pl. guide

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

|||

Thanks. When i see the LSN of my TLog bkp, the last 6 doenot match and hence i tried to restore the first files Full bkp - TLog - TLog - Diff bkp.

This gave me error:

Msg 207, Level 16, State 1, Procedure sp_MSremovedbreplication, Line 242

Invalid column name 'lightweight'.

Msg 3165, Level 16, State 1, Line 1

Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'ABC'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Que1 why my LSN of TLogbkps are not matching?

Que2 why i got the error as above?

Following steps i follow to take bkps;

Full bkp-Weekly
BACKUP DATABASE [abc] TO [abc_Backup] WITH INIT
RESTORE VERIFYONLY
FROM DISK = 'C:\Database Backup\User Databases\abc\abc_BackUp.BAK'

Diff bkp-Daily
BACKUP DATABASE [abc] TO [abc_BackUp] WITH DIFFERENTIAL;

Tlog bkp-Every 2 hr
BACKUP LOG [abc] TO [abc_BackUp];

Pls. guide. Thanks in advance

Bizarre maintenance plan failure (db backup)

I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.

Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Anyone had anything similar?(nielsonj1976@.yahoo.co.uk) writes:
> I am getting a failure on the db backup job of one of my maintenance
> plans. It is coming back with the generic error message of,
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
> failed."
> I then checked the Database Maintenance Plan History page, but this
> shows all the steps having run successfully. If I check the drives for
> the actual backup files, they exist and look healthy too!
> There is plenty of space on the drives, so it is not that.
> I've checked the NT logs and all they say is,
> "SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan for All User Databases''
> (0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
> 2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
> Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "
> Which doesn't give me any clues.

So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.

> Since the backups have actually run to success, I am going to take off
> the option on the maintenance plan to "Verfiy the integrity of the
> backup upon completion". Maybe it is this that is causing problems,
> rather than the backup?

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> So did you look at the Job History? Find the backup job, right-click
> and select Show History. In this dialog check "Show step details". The
> step details usually have some more meat for information.

This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).

> > Since the backups have actually run to success, I am going to take off
> > the option on the maintenance plan to "Verfiy the integrity of the
> > backup upon completion". Maybe it is this that is causing problems,
> > rather than the backup?

> Ehum, if the problem is that the backups don't verify, that I would
> not like to know that and not sweep it under the carpet.

Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?|||nielsonj1976@.yahoo.co.uk (nielsonj1976@.yahoo.co.uk) writes:
> This doesn't really give me any clues either. If I click show step
> details, I get it displaying 2 steps,
> Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.
> Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
> 35 (Schedule 1). The last step to run was step 1 (Step 1).

I rarely set into maintenance job, so I don't really know where sqlmaint
might write its errors. But looking at the properties of a plan that I've
actually set up, I see that there is a Reporting tab. Have you checked
what's yours, and if that leads to a useful file?

>> > Since the backups have actually run to success, I am going to take off
>> > the option on the maintenance plan to "Verfiy the integrity of the
>> > backup upon completion". Maybe it is this that is causing problems,
>> > rather than the backup?
>> Ehum, if the problem is that the backups don't verify, that I would
>> not like to know that and not sweep it under the carpet.
> Having checked this morning the backup status is still showing Failed
> even with the verify option off. The only reason I thought it *could*
> be this is that the backups are fairly large (25gb+) so SQL server
> could be having problems verifying with the disk space available?

I will have to retract what I said above. I recalled a discussion among
my MVP colleagues who are into server administration. The consensus was
that verification of backups as offered by RESTORE VERIFYOLNLY is useless.
The only way to really verify a backup is to restore it. And run DBCC checks
on the restored copy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do your maint plans cover more than 1 database? If so check the recovery modes of each - this error seems to pop up if there are mixed modes within 1 plan.