Sql-server – SQL Server Backup transaction error

backupcorruptionmirroringsql server

Hi I am attempting to backup the transaction log file to setup mirroring in SQL Server

I execute

BACKUP LOG CUSTOMER TO DISK ='K:\JonDB\CUSTOMER.trn' WITH INIT
GO

And I receive

Processed 6587361 pages for database 'CUSTOMER', file 'CUSTOMER' on file 1.
Processed 0 pages for database 'CUSTOMER', file 'CUSTOMER_log' on file 1.
Processed 6 pages for database 'CUSTOMER', file 'CUSTOMER_log2' on file 1.
BACKUP DATABASE successfully processed 6587368 pages in 969.013 seconds (46.948 MB/sec).
Msg 3049, Level 16, State 1, Line 5
BACKUP detected corruption in the database log. Check the errorlog for more information.
Msg 3013, Level 16, State 1, Line 5
BACKUP LOG is terminating abnormally.

Where is the error log? I have a lot of disk space available so it cannot be this. I need to create the backup of the database and transaction log file to setup mirroring I believe. If I can get around this step, it would be ideal.

Best Answer

You appear to have corruption in your transaction log file. This needs to e resolved before you continue with you mirroring configuration.

Where is the error log?

Using SSMS the error log can be found in the management folder.

I would check the log for more information and run a full DBCC CHECKDB to ensure the database is OK.

Paul Randall is an expert in data corruption in SQL Server and he mentions a fix to your problem in this article. I would read this through to get a good understanding of the situation you are in. In brief he says that the situation may be solved by switching the database to SIMPLE recovery mode, back to FULL recovery mode and then performing a FULL backup. The full backup restarts the log chain.

I would also inspect the hard drives that the transaction log file is running on as they may have caused the corruption if they are faulty.

Related Question