Taking databases offline in SQL Server 2012

Whenever I try and use the Take Offline feature in SQL Server 2012 via SQL Server Management Studio, the progress window hangs, and no matter how long I wait, nothing seems to happens.

I found an alternative way to do this today, which solves the problem nicely. Simply run the following T-SQL:

ALTER DATABASE {DatabaseName} SET OFFLINE WITH ROLLBACK IMMEDIATE

…and the database goes offline, ready for other operations such as database restores to be performed.

Getting the auto-incremental ID of a record in SQL Server

Time for another SQL Server aide-memoire…

I can never remember which command to use in T-SQL to get the ID of the most recently added record when using auto-incrementing IDs. There are three choices:

  • @@IDENTITY
  • SCOPE_IDENTITY()
  • IDENT_CURRENT(‘{TableName}’)

However, as described in great and excellent detail by the ‘SQL Authority’ in one of his blog posts, the one to use is SCOPE_IDENTITY(). This to restrict the ID to one generated in the current scope, rather than accidentally getting an ID generated by a trigger further down the line.

Enabling ‘xp_cmdshell’ in SQL Server

I was doing some work with SQL Server today and came across a solution I’ve used a lot over the years. This is a perfect candidate for my “Aides-Memoires” category.

Part of the task involved copy a file from one location to another using T-SQL. This is simple to achieve:

DECLARE @CommandText varchar(1000)
SET @CommandText = 'copy [source] [destination]'
EXEC master..xp_cmdshell @CommandText

However, when I executed the code above I got the following error:

“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’, search for ‘xp_cmdshell’ in SQL Server Books Online.”

The solution to this is simple. Run the following command to enable the use of xp_cmdshell:

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

After running the T-SQL above, the code to copy the file worked as expected.