move sql database
Posted on 2005-04-20
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:
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.