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 --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:
Quote:
Originally Posted by
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 --
Quote:
Originally Posted by
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
Stu (stuart.ainsworth@.gmail.com) writes:
Quote:
Originally Posted by
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@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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 --|||Mark D Powell (Mark.Powell@.eds.com) writes:
Quote:
Originally Posted by
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
Mark D Powell (Mark.Powell@.eds.com) writes:
Quote:
Originally Posted by
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@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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 --|||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:
Quote:
Originally Posted by
Erland Sommarskog wrote:
Quote:
Originally Posted by
Mark D Powell (Mark.Powell@.eds.com) writes:
Quote:
Originally Posted by
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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 --|||Mark D Powell (Mark.Powell@.eds.com) writes:
Quote:
Originally Posted by
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 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
Quote:
Originally Posted by
Mark D Powell (Mark.Powell@.eds.com) writes:
Quote:
Originally Posted by
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 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
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@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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 --
No comments:
Post a Comment