moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
Q
The detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation.
Thanks
Hari
SQL Server MVP
"Q" wrote:

> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:

> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>
|||Thanks Hari!
Q
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
> the datbase. This method is not at all resouce intencive. So possibility of
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
> restore the database (RESTORE DATABASE. This is actually a online operation.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:

> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. You
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>