Quicknote: Backing Up A DB

Just a quick set of notes. To backup a SQL 2005 / 8 DB, you need to detach the database then copy the relevant database and log file accordingly. You can then simply attach the database again. Note that to detach, you must ensure that there are no active connections.

To do this, I load up SQL Management Studio, and detach the appropriate DB…


If there are active connections, you will get the following message when detaching. Simply kill these connections in the detach dialog prior to detaching or force a restart of the sql service


I then go to the location where the DB files reside and copy them (typically something like D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA. Make sure you copy both the mdf and the ldf files.


Then reattach the DB once copied. All done!