Archive for the ‘T-SQL’ Category

Server Roles with TSQL

Here’s a few bits of TSQL you can use when working with Server-Level Roles in SQL Server 2012. List the server roles setup on an instance… ?View Code TSQLEXEC sp_helpsrvrole; List the members of a role… ?View Code TSQLEXEC sp_helpsrvrolemember ‘sysadmin’; List the permissions assigned to a server-level role… ?View Code TSQLEXEC sp_srvrolepermission ‘sysadmin’; Test […]

TSQL: Restore a database without a log (ldf) file

I had to restore a bunch of databases missing a log file today. This was only a test server but I wanted to get it running as quickly as possible. The script here is based on the advice in this blog post from sqlskills. Note that this is very dodgy and should only be used […]

TSQL: Accuracy of DATETIME

Here’s something I didn’t know about the DATETIME data type in SQL Server…. ?View Code TSQLSELECT CAST(’2014-04-10 00:00:00.000′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.001′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.002′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.003′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.004′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.005′ AS DATETIME) […]

TSQL: Table count per filegroup

Here’s a query that uses the SQL Server System Catalog Views to return a table count per table. I used this to check even table distribution in a data warehouse. ?View Code TSQLSELECT ds.name AS filegroup_name, COUNT(DISTINCT t.[object_id]) AS table_count FROM sys.tables t INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id] AND i.is_primary_key = 1 INNER […]

The database cannot be recovered because the log was not restored

A test restore of a SQL Server database had somehow been left in the “RESTORING” state. I attempted to bring the database online with ?View Code TSQLRESTORE DATABASE db_name; But I was greeted with… Msg 4333, Level 16, State 1, Line 1 The database cannot be recovered because the log was not restored. Msg 3013, […]