[SOLVED] Sql restore error: database is in use.

Sometimes you are trying to restore a sql server database and you'll get the error:
"Exclusive access could not be obtained because the database is in use."

This simple one-liner will kick everyone off SQL Server (including any inadvertant connections from yourself e.g. edit table connections).

USE MASTER;
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

This technique works fine on Microsoft SQL Server 2005 / 2008 and Express Editions (probably works on more versions). It works very quickly and allows you to restore you database straight away afterwards.

This excellent tip was found on dba stackexchange. Please let me know if you found this tip helpful.

Encrypt SQL statements

If you distribute your SQL code to third parties (perhaps in runs on other peoples servers), you may wish to help protect your intellectual property by encrypting your SQL statements.

Simply using "WITH ENCRYPTION" at the end of your normal CREATE PROCEDURE will stop the definition of your sql statements being returned. The statement will be automatically decrypted on the fly.


CREATE PROCEDURE AlexTestProcedure
WITH ENCRYPTION
AS
SET NOCOUNT ON;
SELECT Firstname, Surname, Email
FROM Users;
GO


After having a quick look around the internet on the merits of the added security;
In SQL server 2000, the de-obfuscated text is stored in the SYSCOMMENTS table before it is executed. So you may wish to consider this as a preventative measure for the casual nosey parker, but not a bullet proof solution. However the equivalent MSDN page for SQL 2008 R2 doesn't mention this.

MSDN article on CREATE PROCEDURE sql server 2008 r2

MSDN article on created procedure sql server 2000

Interesting things you learn whilst talking to DBA's (thanks Solomon!)

Get database ID from MS Sql Server

If you need to get the database ID quickly (for example you are using SQL profiler and need to filter by database ID)

One of the quickest ways I have found is to execute the following built-in function:

select db_id('yourdatabasename')

Shortcut Key for Table Details in Sql Server Management Studio

If you select a table name in the query window of Sql Server Management Studio
and press ALT + F1 it will display the details of that table.

In the background shortcut key will execute sp_help on your behalf, so in this example it executes: sp_help users, which is much quicker than typing it.

I'm running Sql Server Management Studio 2008 R2 (but i'm pretty sure it'll work with earlier versions)