Attach and Detach LocalDB Databases

Every now and then I need to attach and detach databases from a SQL Server Express Local DB instance. Its often useful to be able to quickly and easily automate attaching different versions of your database, and I can never remember what the SQL commands are. So this post is mainly for my own benefit in the future!

I tend to use LINQPad for most of my database querying needs, which allows me to use LINQ or plain SQL. However, although it lets you connect to a local DB instance (e.g. with a connection string like Data Source=(localdb)\ProjectsV12;Integrated Security=SSPI), it doesn't provide any capability to manage databases - you need to write the SQL directly yourself.

Find out where the files are

If you already have a database (we'll call it MusicStore for these examples), you can find out where the mdf and ldf files are located with the following command:

USE [MusicStore] 
SELECT type_desc, name, physical_name from sys.database_files;

Detaching a database

Detaching the database can be done with the sp_detach_db command, but that can fail if the database is in use, so setting it offline first ensures it can be detached.

USE master
ALTER DATABASE [MusicStore] SET OFFLINE WITH ROLLBACK IMMEDIATE
EXEC sp_detach_db 'MusicStore';

Having detached the database, you're free to back up the mdf and ldf files to another location, or swap them out with different versions.

Attaching a database

And here's the command to attach a new database with specific mdf and ldf files.

USE master
CREATE DATABASE MusicStore
    ON (FILENAME = 'C:\Users\mark\MusicStore.mdf'),   
    (FILENAME = 'C:\Users\mark\MusicStore_log.ldf')   
    FOR ATTACH;  

Nice and simple, and an easy way to switch between multiple different instances of a particular database for testing purposes.