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 enable xp_cmdshell in SQL Server 2008?

Problem:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Cause:
The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.

Solution:

- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO


- To update the currently configured value for advanced options.
RECONFIGURE
GO


- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO


- To update the currently configured value for this feature.
RECONFIGURE
GO


Reference: Zubair Ahmed
http://mssql-tech.blogspot.com/2011/11/how-to-enable-xpcmdshell-in-sql-server.html

Other References:

Check out the below links for more details on this topic.

http://technet.microsoft.com/en-us/library/ms190693.aspx
http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/11/13/why-we-recommend-against-xp-cmdshell.aspx

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/)

How to get product key details in SQL Server 2008 R2?

We may use the below script to extract product key details in SQL Server 2008 R2

USE master
GO
EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\100\BIDS\Setup','ProductID'
GO

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

How to enable the sa login in SQL Server 2008 R2?

If you don't see the 'sa' account in SQL server 2008 under security node even you have already enabled ' SQL Server and Windows Authentication mode' then Just follow the steps below to enable 'sa' login in SQL Server 2008 R2.

Method 1 - Enable the sa login by using Transact-SQL:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

 

Method 2 - Enable the sa login by using Management Studio:

1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
2. On the General page, you might have to create and confirm a password for the sa login.
3. On the Status page, in the Login section, click Enabled, and then click OK.

Check out http://msdn.microsoft.com/en-us/library/ms188670.aspx for more details.

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