question

user-502 (google) avatar image
user-502 (google) asked

Moving physcal MDF and LDF - Considerations?

We have a few databases whose physical files are not in a standardized location. In a couple of databases the transaction log is stored in the same physical location as the main file. We would like to move them without breaking the applications that they serve.

From what I've read (so far) only SQL Server needs to know where these physical files are and as long as I get everything logged off and shut down nicely I should be able to Detach, move files, and re-attach w/o operational issues.

I did this to one of my simple test databases and it worked as expected. I then did this to a database that serves one of our applications and we had to call vendor support. They had to do some work on fixing various accounts.

I have 2 more to move and don't really want to go through that stress again. Can someone tell me if I'm likely to run into problems each time I do this? I've read many good SQL books that explain how to do this and none of them warn of any consequences

sql-server-2005maintenance
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered

Rather than doing a full detach/attach you can bring the dB down and issue a command similar to this

Alter Database tempdb
Modify File (Name = templog, Filename = 'F:\SQLLog\templog.ldf');

Move the physical file to the new location and start the dB up again.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

When you did it on the database that serves the application, was it to a different server? Often if you move databases from one server to the other then the database principal will become detached from the server principal, and you need to use ALTER USER to re-attach the database principal (USER) to the server principal (LOGIN). Have a look at this question.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.