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 --
8  16670 
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 --
Stu (st************ **@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:
Stu (st************ **@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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 wrote:
Mark D Powell (Ma*********@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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 --
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 (Ma*********@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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 --
Mark D Powell (Ma*********@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:
Mark D Powell (Ma*********@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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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 -- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: jenny |
last post by:
Hi, I am trying to find a VB way that would create a folder on all
existing drives - the folder name would be the same on each drive. ie
c:\backup, d:\backup, etc. But the folders would only be created if
they don't already exist, and if the drive happens to be one a folder
cannot be created on (ie a cdrom drive) it would just be skipped...
|
by: Adam |
last post by:
My "C:\" is getting full. What's the best way to
install "wxWindows" without taking up 115 MB from my "C:\" ?
Why do the following:
MS Visual Studio, wxWindows, Borland C++,
Stingray Objective Studio, ARM SDT, etc.
like to install source code to the "C:\" drive?
|
by: Buck Turgidson |
last post by:
In my haste, I added a datafile to a tablespace, but I got it in the
wrong directory. Can I move a datafile of a tablespace?
|
by: raylopez99 |
last post by:
What's the best way of implementing a multi-node tree in C++? What I'm
trying to do is traverse a tree of possible chess moves given an intial
position (at the root of the tree). Since every chess position has
around 30 moves, it would mean every node of the tree would have 30
branches (on average), which in turn themselves would average...
|
by: FFMG |
last post by:
Hi,
I would like to add international support for my site to allow some of
my users to translate the site if they really feel the urge to do it.
I don't use any CMS, the site was developed, (and refined :)), by me.
But I see that various CMSs handle languages very differently.
Joomla! for example has a smallish define file that has all...
| | |
by: Michael |
last post by:
I'm new to PHP, I already learned the basics of the language and built
some little app's for practice.
I have a critical dilemma, soon enough, I probably start to develop
real-world applications, fully working web-sites for paying
costumers.
I have 2 options for doing this: writing everything with plain PHP, or
using some PHP framework.
I...
|
by: waj0606 |
last post by:
I have a DB that is closing in on becoming larger than the drive it resides on. The DB has one file group and one datafile. If I create a second data file within that group on another drive, how do I specify that any further growth should be written to the new data file? Thanks in advance
|
by: =?ISO-8859-1?Q?Andr=E9?= |
last post by:
Hi everyone,
I'd be interested in hearing suggestions as to the "best" way to drive
a Python program step by step from another application.
Details:
---------
I have implemented a "Robot" that can be programmed by a user to
perform certain actions. (see Reeborg below for a simple javascript-
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
