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
ldf files are located with the following command:
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.
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
CREATE DATABASE MusicStore
ON (FILENAME = 'C:\Users\mark\MusicStore.mdf'),
(FILENAME = 'C:\Users\mark\MusicStore_log.ldf')
Nice and simple, and an easy way to switch between multiple different instances of a particular database for testing purposes.