May 17, 2006 at 8:44 am
I'm asked to move just the physical data files and Translog on new SAN drives. SAN is being upgraded to EVA8000- Data Reorganization
Current setup:
MSSQL installed on Local (D)
Datafiles: SAN(H)
Tran_log: Local(D)
Backup: SAN(N)
I've about 45 DBs on this current server. Is there any efficient and faster way to move the data files and Translog of SYSTEM and USER databases?
I researched and found this option: USER DB would be just detach n attach?
Moving the Master Database
The location of the master database and its associated log can be changed from within SQL Server Enterprise Manager. To do this:
1. Open SQL Enterprise Manger and drill down to the proper database server.
2. Right-click the SQL Server in Enterprise Manager and click Properties.
3. Click the Startup Parameters button and you will see something similar to the following entries:
-dD:\MSSQL\data\master.mdf
-eD:\MSSQL\log\ErrorLog
-lD:\MSSQL\data\mastlog.ldf
-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 file.
4. Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server.
Moving MSDB Database
When you are using this procedure to move the msdb and model databases, the order of reattachment must be model first and then msdb.
To move the MSDB database on SQL Server 2000, follow these steps:
1. In SQL Server Enterprise Manager, right-click the server name and click Properties. On the General tab, click Startup Parameters.
2. Add a new parameter as -T3608. Select OK to close the Startup Parameters and the Properties page. You will not be able to access any user databases at this time. You should not perform any operations other than the steps below while using this trace flag.
3. Drill down to the msdb database and then right click on it. Select Properties and then select the Options tab.
4. Select Restrict access and then Single User. Close the Properties sheet by selecting OK.
5. Stop and then restart SQL Server.
6. Open SQL Query Analyzer and then detach the msdb database using the following commands:
use master
go
sp_detach_db 'msdb'
go
7. Move the Msdbdata.mdf and Msdblog.ldf files from the current location to the new location.
8. Reattach the MSDB database as using the following commands:
use master
go
sp_attach_db 'msdb','E:\Mssql\Data\msdbdata.mdf','F:\Mssql\Data\msdblog.ldf'
go
9. Remove the -T3608 trace flag from the Startup Parameters box in the SQL Enterprise Manager.
10. Stop and then restart SQL Server.
11. Verify the change in file locations using sp_helpfile:
use msdb
go
sp_helpfile
go
This should return something similar to the following lines:
MSDBData E:\data\msdbdata.mdf PRIMARY 11264 KB Unlimited 256 KB data only
MSDBLog F:\logs\mssql\data\msdblog.ldf NULL 2304 KB Unlimited 256 KB log only
Moving the Model Database
Use the procedures in Moving the MSDB Dabase directly above to move the model database. Substitute model anywhere you see msdb.
Moving Tempdb
1. You can move tempdb files by using the ALTER DATABASE statement. To do this, determine the logical file names for the tempdb database by using sp_helpfile:
use tempdb
go
sp_helpfile
go
The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
2. To move the database and its log use the ALTER DATABASE statement, specifying the logical file name as follows:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'f:\Sqllogs\templog.ldf')
Go
You should receive the following messages confirming the change:
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
3. Stop and restart SQL Server.
May 17, 2006 at 11:26 am
Anybody???
May 18, 2006 at 7:13 am
Hello,
I'm not sure if the instructions that you have for the system databases are correct, but they look right. If they came from a trusted source, then I would believe that they are correct.
Moving the user databases is as simple as detaching, copying the files, and reattaching.
If user dbs are replicated, you might want to look at this: http://www.sqlservercentral.com/columnists/awarren/movingreplicateddatabases.asp
jg
May 18, 2006 at 8:55 am
No user DBs are replicated
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply