Best way to move a user database?

Question

Best way to move a user database?

Jason Williams-387288

SSCommitted

Points: 1740
More actions
January 23, 2008 at 5:56 pm

#119722

I need to move a user database from one location to another. Specifically, I need to just move that transaction file to another array because of disk I/O performance.
I came across this Microsoft article:
Problem and question is, the database is in use, so I cannot take the steps as listed in the article.
Whats the best way to do this?
I appreciate it.
JW

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

How about backup across servers? If the size of this database is huge, you may consider the third party tool, such as LiteSpeed.

Vivien Xing SSChampion Points: 12297 More actions · Answer 2

How about shrink the current log file to a very small size when the used space is tiny and restrict the maximum file size, should be a small size, for this file. Add one more log file on the other drive, and let the transaction log grow from there.

Jason Williams-387288 SSCommitted Points: 1740 More actions · Answer 3

For this particular move, I am just moving the t-logs from one array to a new array on the same server.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

I Think for your purpose you have to dettach the database and copy paste the log file to other array. In that case database will be offline.You can also try this, Give the autoclose option YES and copy the log file to other array.

Jason Williams-387288 SSCommitted Points: 1740 More actions · Answer 5

Appreciate the help.

Can you give some suggestions on how to properly detach the database?

I know once I detach the database, I move the transaction log file to the new array, then reattach it with the query analyzer.

But getting the database offline is where I need to start. Right now, when I look at it, right click on the DB and select "Detach", gives me options to "drop connections", "update statistics", and "Keep full Text". Also says that there are 12 active connections.

Appreciate it.

Jason

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 6

Hopefully, the following steps helps.

Add a new log file to your new drive;

Empty your old existing log file using DBCC SHRINKFILE command;

Remove your old existing log file using ALTER DATABASE command.