How to move msde DB's to different Drive same server 3

[pctekkk]

what is the syntax to move some db's that were loaded on the C:\ drive to D:\ drive
on same server ??

Thanks

 

[paul172]

This would be easier if you had access to Enterprise Manager. It would just be a case of detaching and reattaching then. Is it possible you could get a hold of it, maybe even a trial version.

 

[mrdenny]

It's very easy to do this without Enterprise Manager. You'll be using the sp_detachdb and sp_attachdb procedures. You can get the full syntax on them in BOL, but here's the basics.

exec sp_detach_db @dbname='TestDB', 'false'
go
/*
Move the files to the new drive.
*/
exec sp_attach_db @dbname='TestDB', @filename1='e:\newfile.mdf', @filename2='f:\file.ldf'

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[louiem]

i do have the full version of SQL on another box, so i can just load the enterprise manager on there or tools only ??


denny,
on the attach command, is the d:\newfil.mdf
the location of the new location of the file ?

i saw u put f:\file.ldf
on the filename2
or was that suppose to be the same drive e:\ ??

thanks gang

 

[mrdenny]

You can to a tools only install on the machine with MSDE from the SQL Server CD.

With the attach command the paths and files listed would be to the new locations of the files. Each file can be on it's own drive. SQL doesn't care if the files are on the same drive, or on different drives. I used E and F to try to show this. For example you could have a very large system with 6 database files on 6 different drive letters, and SQL will still work just fine with it.

If you've got any more questions let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pctekkk]

thanks Mr D

 

[mrdenny]

no problem

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)