Moving the database in SQL Server 2005 Express

Can someone tell me how to move the database from one drive to another? We
are running out of room on our drive.
Thanks!
Saucer Man wrote:
> Can someone tell me how to move the database from one drive to another? We
> are running out of room on our drive.
>
detach db, move files, attach db
you can find detach by right click on the database
you can find attach by right click on databases
|||Hi
ALTER DATABASE dbname SET OFFLINE
AER DATABASE dbname MODIFY FILE(NAME= dbname _dataFILENAME ='o:\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\b.mdf')
ALTER DATABASE dbname MODIFY FILE(NAME= dbname_log,FILENAME ='d:\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\bbbb.ldf')
ALTER DATABASE dbname SET ONLINE
"Saucer Man" <saucerman@.nospam.com> wrote in message
news:480497fc$0$19829$cc2e38e6@.news.uslec.net...
> Can someone tell me how to move the database from one drive to another?
> We are running out of room on our drive.
> --
> Thanks!
>
|||"Zarko Jovanovic" <mind_lessIsHsAsTsEsSsPsAsMs@.inet.hr> wrote in message
news:fu261j$erk$1@.sunce.iskon.hr...
> Saucer Man wrote:
> detach db, move files, attach db
> you can find detach by right click on the database
> you can find attach by right click on databases
>
Where do I right click the database? In Windows Explorer?
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OwrQAw5nIHA.4292@.TK2MSFTNGP04.phx.gbl...
> Hi
> ALTER DATABASE dbname SET OFFLINE
> AER DATABASE dbname MODIFY FILE(NAME= dbname _dataFILENAME ='o:\Microsoft
> SQL Server\MSSQL.1\MSSQL\Data\b.mdf')
> ALTER DATABASE dbname MODIFY FILE(NAME= dbname_log,FILENAME ='d:\Microsoft
> SQL Server\MSSQL.1\MSSQL\Data\bbbb.ldf')
> ALTER DATABASE dbname SET ONLINE
>
I don't understand this. In this example, are you chaning a .mdf file on
one drive to a .ldf file on another drive?
|||Yes
I prefer to separate log amd data files.
"Saucer Man" <saucerman@.nospam.com> wrote in message
news:4805ecaf$0$19811$cc2e38e6@.news.uslec.net...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OwrQAw5nIHA.4292@.TK2MSFTNGP04.phx.gbl...
> I don't understand this. In this example, are you chaning a .mdf file on
> one drive to a .ldf file on another drive?
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ulaXx67nIHA.5096@.TK2MSFTNGP02.phx.gbl...
> Yes
> I prefer to separate log amd data files.
>
Actually I think to be perfectly clear, between the first ALTER DATABASE and
the AER (sic) DATABASE the step Uri assumed and didn't type out is:
xcopy b.mdf "o:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\b.mdf"
xcopy bbbb.ldf "d:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\b.mdf"
at the CMD prompt.
Then do the following 3 ALTER statements.

> "Saucer Man" <saucerman@.nospam.com> wrote in message
> news:4805ecaf$0$19811$cc2e38e6@.news.uslec.net...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks Greg
I should have said that the OP at this moment has to copy files to the new
path.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uWdULg8nIHA.548@.TK2MSFTNGP06.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ulaXx67nIHA.5096@.TK2MSFTNGP02.phx.gbl...
> Actually I think to be perfectly clear, between the first ALTER DATABASE
> and the AER (sic) DATABASE the step Uri assumed and didn't type out is:
> xcopy b.mdf "o:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\b.mdf"
> xcopy bbbb.ldf "d:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\b.mdf"
> at the CMD prompt.
> Then do the following 3 ALTER statements.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||I thought AER was a typo and was supposed to ALTER. What does AER mean?
Once this is done, do the programs that use the database have to be adjusted
or will they automatically connect to the database in its new location?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23a14Rm8nIHA.548@.TK2MSFTNGP06.phx.gbl...
> Thanks Greg
> I should have said that the OP at this moment has to copy files to the new
> path.
>
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:uWdULg8nIHA.548@.TK2MSFTNGP06.phx.gbl...
>
|||Also, where do I enter these commands in 2005 Express?
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uWdULg8nIHA.548@.TK2MSFTNGP06.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ulaXx67nIHA.5096@.TK2MSFTNGP02.phx.gbl...
> Actually I think to be perfectly clear, between the first ALTER DATABASE
> and the AER (sic) DATABASE the step Uri assumed and didn't type out is:
> xcopy b.mdf "o:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\b.mdf"
> xcopy bbbb.ldf "d:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\b.mdf"
> at the CMD prompt.
> Then do the following 3 ALTER statements.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
>
sql