Best way to move datafile from C drive to D

**Stu** · Aug 17 '06, 06:15 PM

Re: Best way to move datafile from C drive to D

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

Stu

Mark D Powell wrote:

I obviously did not search the archives on the right terms so what is
the easiest and fastest way to move a 3G database from a nearly full C
drive to the nearly empty D drive that should have been used.
>
I could back it up, drop it, recreate it using the D drive, and restore
it but it seems like there should be a way to just move the datafile
and use if from the new location.
>
I am thinking that detatch/attach is the best method, but I would like
confirmation or suggestions on how to proceed or things to be aware of
when using this method.
>
-- Mark D Powell --

**Erland Sommarskog** · Aug 17 '06, 09:55 PM

Re: Best way to move datafile from C drive to D

Stu (stuart.ainswor th@gmail.com) writes:

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

The advantage with detach/attach is that you don't have to wait for
the new data file to be initiated. For a 3GB database that's maybe 5-10
minutes on SQL 2000. SQL 2005 has some quick initiation under some circum-
stances.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Mark D Powell** · Aug 17 '06, 11:05 PM

Re: Best way to move datafile from C drive to D

Erland Sommarskog wrote:

Stu (stuart.ainswor th@gmail.com) writes:

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

>
The advantage with detach/attach is that you don't have to wait for
the new data file to be initiated. For a 3GB database that's maybe 5-10
minutes on SQL 2000. SQL 2005 has some quick initiation under some circum-
stances.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

OK, the plan is

1- that the customer is going to try to lock the end-users out of the
application
2- I will backup the database
3- I will detatch it
4- I will copy the files to the new location
5- I will attach the db
6- I will test accessing some data
7- the customer will open the application

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

Sounds easy enough.
-- Mark D Powell --

**Erland Sommarskog** · Aug 18 '06, 07:05 AM

Re: Best way to move datafile from C drive to D

Mark D Powell (Mark.Powell@ed s.com) writes:

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

You don't have to create the database prior to restoring it. RESTORE will
do that for you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Mark D Powell** · Aug 18 '06, 01:45 PM

Re: Best way to move datafile from C drive to D

Erland Sommarskog wrote:

Mark D Powell (Mark.Powell@ed s.com) writes:

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

>
You don't have to create the database prior to restoring it. RESTORE will
do that for you.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names? I have changed them
when I overlaid a production database with a test (upgrade) version
that ran on the same server but I changed them to existing files for
the existing producion database that I wanted to overlay.

I should take a few minutes and try this on the test server.

Thanks -- Mark --

**Stu** · Aug 18 '06, 02:05 PM

Re: Best way to move datafile from C drive to D

You want to use the WITH MOVE option; check the BOL. However, detach,
move, and attach should work fine.

HTH,
Stu

Mark D Powell wrote:

Erland Sommarskog wrote:

Mark D Powell (Mark.Powell@ed s.com) writes:

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

You don't have to create the database prior to restoring it. RESTORE will
do that for you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>
I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names? I have changed them
when I overlaid a production database with a test (upgrade) version
that ran on the same server but I changed them to existing files for
the existing producion database that I wanted to overlay.
>
I should take a few minutes and try this on the test server.
>
Thanks -- Mark --

**Erland Sommarskog** · Aug 18 '06, 03:55 PM

Re: Best way to move datafile from C drive to D

Mark D Powell (Mark.Powell@ed s.com) writes:

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names?

Don't know what the screen permits, and I don't want to engage in guessing
what it does.

The syntax is:

RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilenam e1' TO 'newlocation.md f',
MOVE 'logicalfilenam e2' TO 'newlocation.ld f',
REPLACE

The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.

The command above with create the database files, and then restore the
backup.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Mark D Powell** · Aug 20 '06, 02:55 PM

Re: Best way to move datafile from C drive to D

Erland Sommarskog wrote:

Mark D Powell (Mark.Powell@ed s.com) writes:

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names?

>
Don't know what the screen permits, and I don't want to engage in guessing
what it does.
>
The syntax is:
>
RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilenam e1' TO 'newlocation.md f',
MOVE 'logicalfilenam e2' TO 'newlocation.ld f',
REPLACE
>
The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.
>
The command above with create the database files, and then restore the
backup.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I used attach/detach and it seemed to work fine. The slow part was
moving the 1.2G and 3.5G datafiles to the new locations. I swear the
copies took 3X longer the making the backups I made before I tried the
detatch/attach.

Thanks to all
-- Mark D Powell --

Best way to move datafile from C drive to D

Best way to move datafile from C drive to D

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment