move ms/sql database files to another disk

Posted on 2005-04-21
Last Modified: 2008-01-09
I have an MS/SQL server (7.0) installed on one machine, says the production machine.
All databases are on its D: drive.
For test purpose, I have installed a fresh MS/SQL server on another (test) machine. But on this machine, there is no space enough on the D: drive. I need to have everything says on the F: drive.
I have no problem to dump/restore databases but I want to know if there is another way. I mean. I have stopped the production machine and copied all the database physical files d:\mssql7\data to the other machine f:\mssql7\data through a network connection.
But when I start the test machine, it does not find other database than the master. I suppose that the physical location of the other databases is somewhere indicated in the master and this says D: not F:
So how to easily change that so that it finds all these databases on F: ?
Question by:LeTay
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    There are several ways to do what you are asking...and several impacts

    If you want to do this OFFLINE then you can use the sp_attach sp_detach instruction.
    If you want to do this ONLINE then you can use the restore with norecovery and move instruction.below an example

       TO DISK = 'c:\Northwind.bak'
       FROM DISK = 'c:\Northwind.bak'
       FROM DISK = 'c:\Northwind.bak'
          MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',
          MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'

    Just take in consideration that using data through a network shared drive creates additional network contraints as the data and log files are and should be constantly locked.   Make sure you don't habe any bottleneck or you risk data corruption.  Hope this helped.

    LVL 15

    Expert Comment

    Hi LeTay,

    To expand a little on Racimo, you can use sp_detach_db to detach the databases from your production server, this disconnects the database leaving the files intact.  you can then copy them to the relevant locations on your new server and sp_attach_db to attach them here.  With sp_attach_db you specify the new location of the files.  If required you can then re-attach the original files to your production server leaving you with two identical copies of the database, one on your new server the other on the original production box.

    sp_detach_db yourDB

    sp_attach_db yourDB, f:\mssql\data\youDB_data.mdf, f:\mssql\data\youDB_log.ldf

    sp_detach_db full syntax:
    sp_attach_db full syntax:

    Author Comment

    Dear friends,
    I know these different ways but the situation is as follows (and I need to start from this situation, as the test server is now outside the network and is not planned to go back on it) :
    I have in fact all the \mssql7\data files from the production on the test machine (but on F: instead of D:) and I would like to be able to start from there, not from the production anymore...
    LVL 50

    Expert Comment

    by:Julian Hansen
    I would go with mcmonap's suggestion and simply do a db attach for the files.

    You might find it easier from Enterprise Manager.

    Open EM
    Right Click the databases node under the server node for the DB server on the Test machine
    Select All Tasks
    Select Attach
    Browse to mdf file of database on F: drive
    Select file

    LVL 14

    Expert Comment

    by:Thandava Vallepalli
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    sp_attach and sp_detach should be sufficient if you only need to move away user's database.  However, keep in mind that attach/detach feature is risky when dealing with system db's.  (In case you want to move these as well).  A backup restore is less risky even though the specific procedure for system db's may look more complex.
    Hope this helped...


    Author Comment

    Indeed it looks not so easy.
    What I really did on the test machine is :
    - installation of MS/SQL 7.0 (sp 1) (define data on f: ...)
    - stop SQL server (and agent)
    - copy the production data files (all, including master, model...) to f:, overriding existing
    - restarted the SQL services
    - started EM -> the databases are not visible there
    - with ISQL, I can see the databases. I detached one of them successfully, but when attaching again, it says : Server: Msg 945, Level 14, State 2, Line 1 - Database 'MyDataBase' cannot be opened because some of the files could not be activated. (I am sure the data file name is correct)

    Note when right clicking the databases node on the server node in the EM, the tasks list does not include detach or attach. Maybe because it is SQL 7.0 ?
    LVL 15

    Expert Comment

    Hi LeTay,

    Why do you need the system databases - master, model, msdb?
    I would not use the enterprise manager interface for attaching and detaching databases, (IMO) I do not think it is reliable.  You should use T-SQL as detailed on the links to the MS website above, you can execute T-SQL commands in query analyser or osql.
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    <<I detached one of them successfully, but when attaching again, it says : Server: Msg 945, Level 14, State 2, Line 1 - Database 'MyDataBase' cannot be opened because some of the files could not be activated. (I am sure the data file name is correct)>>
    make sure the logical name you are using is th right one

    <<I would not use the enterprise manager interface for attaching and detaching databases, (IMO) I do not think it is reliable. >>
    Exactly, Query Analyzer is a much more reliable way to do than EM.

    Author Comment

    I use the query analyzer for issuing the commands...
    But the logical name when attached is not the problem :
    You can do the following without any problem (I tried) :
    sp_detach_db 'testdatabase'
    sp_attach_db 'anothername','filelocationoftestdatabase.mdf'
    It works.
    And anyway, I practically used the same logical name;
    The problem is somewhere else...
    LVL 68

    Accepted Solution

    When attaching, you need to specify the location of the log file or use a proc that indicates that a log file does not exist.  Otherwise SQL will try to use the prior log file, which it won't be able to find since it's looking for D: not F:.  So:

    EXEC sp_attach_db 'dbName', 'F:\full\path\to\data\file\database.mdf', 'F:\full\path\to\log\file\logfile.ldf'


    EXEC sp_attach_single_file_db 'dbName', 'F:\full\path\to\data\file\database.mdf'

    LVL 68

    Expert Comment

    >>  installation of MS/SQL 7.0 (sp 1) (define data on f: ...) <<

    Btw, make sure you also apply a later sp at some point.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Sql Permission 6 23
    Start a field with integer 1 7 29
    Why is the output of this function is like this? 4 9
    space 16 0
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now