How to resolve "Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]"?

If SQL database is getting failed leaving the below error message.

Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

Also the database log file reveals the following:

Job 'CBI_FULL_Backup' : Step 1, 'CBI SQL BAckup - Full' : Began Executing 2012-01-04 01:00:00
10 percent backed up. [SQLSTATE 01000]
20 percent backed up. [SQLSTATE 01000]
30 percent backed up. [SQLSTATE 01000]
40 percent backed up. [SQLSTATE 01000]
50 percent backed up. [SQLSTATE 01000]
60 percent backed up. [SQLSTATE 01000]
70 percent backed up. [SQLSTATE 01000]
80 percent backed up. [SQLSTATE 01000]
90 percent backed up. [SQLSTATE 01000]
Processed 3565832 pages for database 'CBI', file 'CBI_Data' on file 1. [SQLSTATE 01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1375 pages for database 'CBI', file 'CBI_Log' on file 1. [SQLSTATE 01000]
Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

Root Cause:

The backup file got corrupted

Workaround:

Renamed the backup file / device.

URL: http://mssql-tech.blogspot.com/2012/01/how-to-resolve-msg-3013-sev-16-backup.html

Other References:

http://support.microsoft.com/kb/290787

How to attach SQL 2000 database (MDF) without a LDF file?

Repairing DB In Emergency Mode (By-Pass Recovery Mode)

Problem(s):

How to attach the MDF file using query analyzer?

How to bring database back on operation when the LDF file is damaged?

How to By-Pass Recovery Mode and bring the database online back through ‘Emergency Mode’?

Solution:

If  we need to ignore the current LDF file and if the database is not detached properly and if you do not have the backup, follow the below steps to bring up your database. In the below step-7 is a undocumented DBCC command.

1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
3. Start SQL Server.
4. Now your database will be marked suspect.

Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up.

sp_configure 'allow updates',1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where dbid=DB_ID('DB_NAME')
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

- Restart sql server. Now the database will be in Emergency mode

- Now execute the undocumented DBCC to create a log file.

dbcc rebuild_log('DB_NAME','G:\SQL_Server_DBs\MSSQL$BDC_BRS003\Data\User Databases\DB_NAME_log.ldf')
go
dbcc checkdb('DB_NAME') -- to check for errors
go

• Now run the below command.
Execute sp_resetstatus DB_NAME

• Restart SQL server and see the database is online.
 
URL: http://mssql-tech.blogspot.com/2011/11/how-to-attach-sql-2000-database-mdf.html


Other References:

http://oldlight.wordpress.com/2007/05/04/hello-world/ http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/289d7bb97da73312?hl=en&lr&ie=UTF-8&rnum=1&prev=/groups?sourceid%3Dnavclient%26q%3Djasper%2Bsmith%2Battach%2Blog&pli=1 http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/13235/suspect-database-is-sql-2000 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78376 http://solution-mdf-file-corruption.blogspot.com/2009/12/how-to-repair-mdf-files-not-detached.html

How to check the version and service pack details in SQL Server?

We may use the below script to get the version details.

select @@version

Check out http://support.microsoft.com/kb/321185 for more details.

Reference: Zubair Ahmed (http://mssql-tech.blogspot.com/)