Move SQL Database to new Drive

LibertyMountain

What is the best way to do this? I have just installed a RAID 10 (8x136GB). It is Logical drive (E:\) is 546GB.

Currently Database is on Logical Drive \.

Servername is PROD. DB name is PROD. I want all this to stay the same so that any DSNs I have developed on other machines don't need to be adjusted.

Thanks for your assistance.

lubost

Hi,
I used to following steps:
1. Log out all users (the best way is to switch database to single user mode)
2. Create database backup
3. Restore database - set appropriate drive and folders during restore.
4. Check restored database
5. Switch database to multiuser mode
6. Perform needed application tests

I don't know what needs your other applications, but all things based on database communication remains the same.

ayhan06

LibertyMountain wrote:

What is the best way to do this? I have just installed a RAID 10 (8x136GB). It is Logical drive (E:\) is 546GB.

Currently Database is on Logical Drive \.

Servername is PROD. DB name is PROD. I want all this to stay the same so that any DSNs I have developed on other machines don't need to be adjusted.

Thanks for your assistance.

detach db. move db files to new drive. attach db.

LibertyMountain

Ayhan06: And this is all done within MS SQL? (Just get everyone out of NAV, detach, move, re-attach, start NAV clients again.) there are replicated services also that I don't want to recreate. thanks.

kine

1) Add new DB file on the new disk
2) use "DBCC SHRINKFILE ( { file_name} , EMPTYFILE) on the old file to make it empty (data will be moved to the new file automatically, the file will be locked for new data). Take care of Transaction log, it can grow...
3) use ALTER DATABASE to remove the old file, which is now empty and can be removed...

FOr more info, see http://msdn.microsoft.com/en-us/library/ms189493.aspx example "D. Emptying a file"

kriki

ayhan06 wrote:

detach db. move db files to new drive. attach db.

I think this way it the easiest and fastest way.

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

kine

kriki wrote:

ayhan06 wrote:

detach db. move db files to new drive. attach db.

I think this way it the easiest and fastest way.

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

But not when you are replicating something etc. because it means "removing" and "creating" the db again an it means that you will loose the connections etc. Way I described can be done on-line and doesn't mean any lost functionality. It means that both ways are possible, it depends on requirements... 8)

kriki

kine wrote:

kriki wrote:

ayhan06 wrote:

detach db. move db files to new drive. attach db.

I think this way it the easiest and fastest way.

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

But not when you are replicating something etc. because it means "removing" and "creating" the db again an it means that you will loose the connections etc. Way I described can be done on-line and doesn't mean any lost functionality. It means that both ways are possible, it depends on requirements... 8)

True!