Moving a SQL Server 2005 transaction log

Since I don’t do this very often, I always have to refresh my memory on the process. It is easily found via your favorite search engine, but since part of my reason for this blog is to consolidate the places I have to go to look for this kind of thing, I’m going to post it here. :)

  1. Document the current database permissions.
  2. Make sure there are no active connections to the database. Really, you need to do this.
  3. Check where the current files are located before you detach the database.
  4. Detach the database. In SQL Server Management Studio (SSMS), just right-click the database and choose Tasks > Detach… and click OK. The database will disappear from SSMS (gasp!).
  5. Move the transaction log file.
  6. Back in SSMS, right click Databases and pick Attach…, click Add, find your .mdb file and click OK.
  7. In the dialog on the bottom part (database details), you will see the log file has Not Found under the Message column. Fix the pathname of the log file to the new location, use the button if you like or just type it in. The error message should disappear.
  8. Click OK. Your database is back.
  9. Check the database permissions and fix them up as needed from the documentation you created earlier.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s