One step closer..... SQL 2012 RC0 is released

Well today we are one step closer to the release of SQL 2012 (aka Denali) with the release of RC0.... actually it was yesterday but I was burried in opperational support yesterday. Anyway this is very exciting to me as there are so many features that offer great new capabilities.

Here's some links to what others are writing about this because why should I re-invent the information:

http://blogs.technet.com/b/sqlman/archive/2011/11/18/sql-server-2012-rc0-available-now.aspx

http://blogs.msdn.com/b/analysisservices/archive/2011/11/17/announcing-microsoft-sql-server-2012-release-candidate-0-rc0.aspx

http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx


You can get the RC0 download at http://www.microsoft.com/download/en/details.aspx?id=28145

Enjoy!

Recovering/Restoring the Resource system database in SQL Server 2005

Here is the scenario I have been working through in the LAB today:

The Resource database (mssqlsystemresource) has become corrupt on a SQL 2005 SP4 instance. You are not sure why the database has become corrupt but when you try and start the SQL Server DB Engine the following is reported in the SQL Server ERRORLOG:

2011-08-01 14:57:23.89 spid5s Starting up database 'mssqlsystemresource'.
2011-08-01 14:57:23.91 spid5s Error: 5172, Severity: 16, State: 15.
2011-08-01 14:57:23.91 spid5s The header for file '\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf' is not a valid database file header. The PageAudit property is incorrect.
2011-08-01 14:57:23.95 spid5s Error: 945, Severity: 14, State: 2.
2011-08-01 14:57:23.95 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-08-01 14:57:23.97 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.



We know that we cannot backup/restore the Resource database using SQL Server (as per http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx) and you could restore the physical file from the server's file system backups. Unfortunately though the file is unable to be recovered from the server's file system backup for an unknown and unrelated external factor.

Never fear there is a simple fix to this. Reinstall the Service Pack as that will recreate the Resource database.... Hurray

Here's the steps and things to note when performing the Service Pack install though:
1. Launch the Service Pack install
2. Accept the terms and conditions and click Next
3. When asked to select the Feature to update you will notice that the Database Services for the required instance will already report "Upgraded". Simply select the instance though as this will re-apply the service pack.
4. Complete the service pack install

Once the Service Pack has been installed you should be able to start the SQL Server Service for the required Instance.

TIPS: You can use the following TSQL to grab information about your SQL Instance and Resource Database. These were taken from http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx

SELECT @@VERSION
SELECT SERVERPROPERTY('ResourceVersion');
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');


SQL 2008 / SQL 2008 R2
For the process to rebuild the Resource database in SQL 2008 follow the instructions at http://msdn.microsoft.com/en-us/library/dd207003.aspx

Happy Disaster Recovering everyone :)

Search Stored Procedure Code

Came across an interesting situation. The database that I was working with was missing from CVS, and I needed to be able to check what Stored Procedures were using Temporary tables. Thankfully I found a blog by one of my favorite authors which saved the day.

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/

TSQL to list all Permissions within a Database

Came across a situation today while trouble shooting a problem with replication where the subscription database has continually been getting modified by an unknown source and thus breaking replication. To assist with investigating this issue there was a need to look at all the permissions assigned to all users and roles within the database both at the Publisher and the Subscriber..... so here is the TSQL I wrote to help with this.

select sys.database_permissions.class_desc
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, sys.database_principals.name as [grantee_principal_name]
, ISNULL(sys.schemas.name, '') as [schema_name]
, ISNULL(AllObjects.name, '') as [object_name]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'GRANT ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'GRANT ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Grant_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'DENY ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'DENY ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Deny_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'REVOKE ' + sys.database_permissions.permission_name + ' FROM [' + sys.database_principals.name + '];'
ELSE 'REVOKE ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] FROM [' + sys.database_principals.name + '];'
END) as [Revoke_Perms_Stmnt]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
--AND sys.database_principals.name = 'public' --uncomment this line to restrict the output to a single principal
LEFT JOIN (
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.objects
UNION
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
ORDER BY sys.database_principals.name
, sys.database_permissions.class
, sys.schemas.name
, AllObjects.name