Problem

When trying to restore a backup taken on SQL Server 2000 on a SQL Server 2012 the following error appears:

 Msg 3169, Level 16, State 1, Line 2

The database was backed up on a server running version 8.00.0818. That version is incompatible with this server, which is running version 11.00.3000. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

The problem is that on a SQL Server 2012 instance you cannot restore a SQL Server 2000 backup. Practically on SQL Server 2012 you can restore backups taken on SQL Server 2005 or later.

Solution

This situation can be avoided by performing the following steps:

1. Restoring the SQL Server 2000 backup on SQL Server 2005 or 2008
2. Set compatibility level to 90 (SQL Server 2005) or 100 (SQL Server 2008 / SQL Server 2008 R2)
3. Backup the same database and restore it on the SQL Server 2012.

Problem

The SQL Server Agent fails to start with the following errors:

[sqagtres] StartResourceService: Failed to start SQLAgent service.  CurrentState: 1

[sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435)

The error is logged only in the Event Viewer, Application category and it can't be found in the SQL Server error log.

The problem can appear in the following scenarios:

1. After a SQL Server cluster instance failover 
2. After moving system databases to a new location

Solution

Check the following two registry keys and make sure that their values are correctly pointing to the actual location of WorkingDirectory and ErrorLogfile directories:

 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<instance number>\SQLServerAgent\WorkingDirectory

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<instance number>\SQLServerAgent\ErrorLogfile

 After modifying the above registry the SQL Agent started successfully. 

Problem

Could not obtain information about Windows NT group/user 'DOMAIN_NAME\user_name', error code 0x5.

The above error can appear when users which are not members of sysadmin server role try to:

-          Execute a SQL job which is not owned by sa or by the user under SQL Server is running

-          Edit a SQL job

-          Any operation that implies the  execution of the external system stored procedure xp_logininfo

xp_logininfo 'DOMAIN_NAME\user_name'

Solution

1. A solution for this problem is to create an AD Security Group and make it a member of Pre-Windows 2000 Compatible Access group. Add the domain account under which SQL Server is running to this new group. 
   
2. Activate Kerberos
3. Create SPN (service principal name)
4. Configure the account under which SQL Server is running to use Kerberos with full delegation:

It seems that the user under which SQL Server is running was unable to delegate the user which was trying to execute the xp_logininfo stored procedure.

When trying to create an assembly, the following error can appear:

USE [DBA]
GO
/****** Object:  SqlAssembly [ASSEMBLY_Name]    Script Date: 10/20/2012 15:16:08 ******/     
CREATE ASSEMBLY [ASSEMBLY_Name]
AUTHORIZATION [dbo]
FROM 0x4D5EA9?.(incomplete here)
WITH PERMISSION_SET = SAFE
GO

 Msg 6513, Level 16, State 27, Line 2

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.

The same error can appear when trying to launch an SSRS report:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for dataset 'Dataset_Name'. (rsErrorExecutingCommand)

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.

Solution

First of all check if AWE is enabled (on 32 bit Windows Server). 

There are some thinks you can check regarding the CLR status and the memory is using \ allowed to use.

Full story »

When you try to revoke the rights of a login on a database the following error can appear:

Cannot drop the user 'dbo'

Solution

This happens because the dbo user is mapped to that particular login. 

You need to change the database owner to a different login:

USE DATABASE_NAME;
ALTER AUTHORIZATION ON DATABASE::DATABASE_NAME TO [sa]

After this the login will not be mapped anymore in that database with dbo user: