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…

image

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

image_3

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.

image_4

Then reattach the DB once copied. All done!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>