Sql-server – MS SQL Server 2012 Error on TLog Backup

backupsql serversql-server-2012

I have a MS SQL Server 2012 installation on which I am restoring a SQL 2005 .bak which includes some full-text catalogues

The original .bak has no conflicts with 2012 (as the migrate advisor was used to clear all of these). Upon running a TLog backup I receive the following error message:

Executing the query "BACKUP DATABASE [databaseName] TO DISK = N'..." failed with the following error: "An error occurred while processing 'BackupMetadata' metadata for database id 5 file id 1. Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

After much searching I am at a loss as to why. Any suggestions greatly appreciated.

Best Answer

Turns out we needed to remove the full text catalogs from 2005 then take the backup. We then recreated the catalogs in 2012 and all is good. Following a single full backup, the t-log maintenance plan runs fine.

Related Solutions

Sql-server – Filestream database backup error

FindNextFile is a Windows API call, so what's happening is the backup process is enumerating all the files in the FILESTREAM folder and backing them up. The error message indicates that the operating system cannot read the file system; this indicates, at least to me, that the file/folder structure on disk is corrupt. There would be a different error returned if the file/folder was simply not found. This may mean the I/O subsystem has problems. I would run chkdsk in Windows on that volume.

From the DBCC CHECKDB documentation, it doesn't look like FILESTREAM checks go beyond ensuring there is linkage between the database and the file system. Running chkdsk has the ability to do a low-level check of the disk contents itself, which is probably what's needed in this scenario. It certainly wouldn't hurt to run CHECKDB on the database anyway (read: regularly) either.

Sql-server – SQL Server tail log backup fails where it shouldn’t

Paul Randal did a good piece on backing up the tail of the log that includes just this case. In it he says that the method to use when there is no data file is using NO_TRUNCATE. Based on that your command should be:

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE

BOL does say

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

So you could try using both of them together but I'm not sure if NORECOVERY can be used if the data file is missing or not, even with NO_RECOVERY.

BACKUP LOG [AdventureWorks2012] TO
DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NAME = N'AW Tail Log Backup', NO_TRUNCATE, NORECOVERY

It's certainly worth testing anyway.

Best Answer

Related Solutions

Sql-server – Filestream database backup error

Sql-server – SQL Server tail log backup fails where it shouldn’t

Related Question