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:
>> 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
>
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...
>> 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?
>|||"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...
>> "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?
>
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...
>> 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...
>> "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?
>>
>
> --
> 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...
>> "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...
>> "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?
>>
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||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...
>> 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...
>> "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?
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
>|||"Saucer Man" <saucerman@.nospam.com> wrote in message
news:48061cde$0$19820$cc2e38e6@.news.uslec.net...
> Also, where do I enter these commands in 2005 Express?
>
Anyone?|||Either use the SQLCMD tool (comes with Express) or download the "SQL Server Management Studio
Express" tool from MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Saucer Man" <saucerman@.nospam.com> wrote in message
news:48074bf8$0$19860$cc2e38e6@.news.uslec.net...
> "Saucer Man" <saucerman@.nospam.com> wrote in message
> news:48061cde$0$19820$cc2e38e6@.news.uslec.net...
>> Also, where do I enter these commands in 2005 Express?
>
> Anyone?
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23j2$wgJoIHA.5016@.TK2MSFTNGP02.phx.gbl...
> Either use the SQLCMD tool (comes with Express) or download the "SQL
> Server Management Studio Express" tool from MS.
>
Thanks!