move sql database

Posted on 2005-04-20
Medium Priority
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

Expert Comment

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

Accepted Solution

Vitor Montalvão earned 40 total points
ID: 13823886
So Ricardo, what's your question?

Author Comment

ID: 13823992
No questions at all, just share information with everybody

LVL 11

Expert Comment

ID: 14437223
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

569 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