Re: Moving DB from one drive to another

Piero 'Giops' Giorgi (giorgi.piero@xxxxxxxxx) writes:
On Sep 25, 12:59 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:

This link in Books Online should be useful:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13fb-457d-996a-
66ffc2d55b79.htm

... Am I right? (Just to be sure...)

The files NOW are in G:\CrimDB\ and I need them in F:\CrimDB\
The Filegroups are in G:\CrimDB\FileGroups

Basically I have to :

1) Detach the Database

In the copy of Books Online I have it suggests ALTER DATABASE SET OFFLINE.
Does it really say sp_detach_db in yours?

This may be possible if you have an old version. I see now that the page
was updated on 5 December 2005, and the Change History says "Corrected the
steps in all procedures.".

See my signature for a link do download the updated Books Online.

Here is what my Books Online says:

To move a data or log file as part of a planned relocation, follow these
steps:

Run the following statement.

ALTER DATABASE database_name SET OFFLINE

Move the file or files to the new location.

For each file moved, run the following statement.

ALTER DATABASE database_name MODIFY FILE
( NAME = logical_name, FILENAME = 'new_path\os_file_name' )

Run the following statement.

ALTER DATABASE database_name SET ONLINE

Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

The Filegroups will be seen without any other change, right?

I don't know, and I don't have a multi-filegroup database to try on.

In any case, what I had in mind was ALTER DATABASE SET OFFLINE. Since
I did not remember the procedures exactly, I found link and posted
that. Sorry if that lead you to an old version that was incorrect.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Second Transaction Log File
    ... What do you think is it good or bad to remove the second log file? ... Let's see, then I would have to look up the exact syntax in Books Online, ... and then ALTER DATABASE with REMOVE FILE. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: How to drop one of the tempdb files
    ... then ALTER DATABASE. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: Backup Question
    ... As Erland recommends, be very comfortable with TSQL as it's a lot more ... ALTER DATABASE db MODIFY FILE ... Start SQL Server. ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: MSDE data files
    ... You can modify file sizes using alter database. ... For compacting, check books online for dbcc shrinkfile. ... If the log files are what's causing problems, ...
    (microsoft.public.sqlserver.programming)
  • Re: Logical File Names
    ... Look at ALTER DATABASE in Books Online. ... Aaron Bertrand ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
Loading