move sql database

Posted on 2005-04-20
Last Modified: 2010-03-19
Move Databases in SQL Serber

--------- Moving the Master Database ----->>>
To move the master database log file to a different driver or I needed to move the Master database to a different drive.
First, right-click on SQL Server in Enterprise Manager (EM) and choose Properties.
Next, click the Startup Parameters, the following parameters appear in this box:
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
Now, to move your files you would remove the current entry and recreate your new entry with the correct path.
For example, to move the Master database log to D:\SqlData.
Delete the -l [path] by highlighting the old parameter and choosing remove, next, add the following entry, “-l D:\SqlData\mastlog.ldf” and click OK twice.
Now, stop SQL Server and move the mastlog.ldf to its new location.
(If you move it to a location that is not specified in the startup parameters, SQL Server WILL NOT start.)
---------------------- Moving Tempdb ----------------- > > >
In order to move the tempdb database, open query analyzer and run the following query:
use master
Alter database tempdb modify file (name = tempdev, filename = 'd:\Sqldata\tempdb.mdf')
Alter database tempdb modify file (name = templog, filename = 'd:\Sqldata\templog.ldf')
After running the query, delete the old file after restarting SQL Server.
-------------- Moving MSDB Database ----------- > > >
To move the MSDB and Model database, follow these steps.
First, right-click the SQL-Server name and click properties.
From the General tab, choose your startup parameters.
Next, enter the parameter -T3608. Click OK, stop and restart SQL Server.
After the restart, detach the database and move them to their appropriate place.

Question by:ricardofig
    LVL 7

    Expert Comment

    by:Jonathan Kelly
    can you not just backup the databases and restore them to the new location ?
    LVL 44

    Accepted Solution

    So Ricardo, what's your question?

    Author Comment

    No questions at all, just share information with everybody

    LVL 11

    Expert Comment

    first time i've seen someone do that.

    funny but excellent info ricardo
    keep it up.

    i'm just wondering why you gave vmontalvao a grade of C :)

    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

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    755 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

    25 Experts available now in Live!

    Get 1:1 Help Now