August 24, 2004 at 12:10 pm
Does anyone know of a good reference for migrating sql server 2000 databases from one server and drive letter to another server and drive letter. I am having trouble moving the system databases cleanly.
August 25, 2004 at 8:09 am
For this example, I'll use:
E:\MSSQL\Data for your new DATA location
E:\MSSQL\Log for your new LOG location
<ServerName> for the SQL Server you will be working on in SQLEM
Moving the MASTER database
==========================
- Create the new location/folder
- SQLEM/<ServerName>/Properties/General tab/Startup parameters button
. Remove line starting with -d
. Add line -d<new location of MASTER.MDF>
. Remove line starting with -l
. Add line -l<new location of MASTLOG.LDF>
(Example)
-dE:\MSSQL\Data\MASTER.MDF
-lE:\MSSQL\Log\MASTLOG.LDF
- Stop MSSQLSERVER service
- Move (or copy/rename old) MASTER.MDF and MASTLOG.LDF files to new location
- Start MSSQLSERVER service
- Verify connectivity.
Moving the TEMPDB database
==========================
- Modify the following commands to reflect the new TEMPDB.MDF and TEMPLOG.LDF file locations in the FILENAME references and execute the script:
USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\Log\templog.ldf')
GO
- Stop MSSQLSERVER service
- Remove TEMPDB.MDF and TEMPLOG.LDF files from old location
- Start MSSQLSERVER service
- Verify connectivity
Moving the MSDB and MODEL databases
===================================
- SQLEM/<ServerName>/Properties/General tab/Startup parameters button
. Add line -T3608 to the startup parameters
- Stop MSSQLSERVER service
- Start MSSQLSERVER service
- Execute the following command:
USE master
GO
EXEC sp_detach_db @dbname='MSDB'
- Move (or copy/rename old) MSDBDATA.MDF and MSDBLOG.LDF to their new locations
- Modify the following commands to reflect the new MSDBDATA.MDF and MSDBLOG.LDF file locations in the @filename references and execute the script:
USE master
GO
EXEC sp_attach_db @dbname = N'MSDB',
@filename1 = N'E:\MSSQL\Data\msdbdata.mdf',
@filename2 = N'E:\MSSQL\Log\msdblog.ldf'
- Execute the following command:
USE master
GO
EXEC sp_detach_db @dbname='MODEL'
- Move (or copy/rename old) MODEL.MDF and MODELLOG.LDF to their new locations
- Modify the following commands to reflect the new MODEL.MDF and MODELLOG.LDF file locations in the @filename references and execute the script:
USE master
GO
EXEC sp_attach_db @dbname = N'model',
@filename1 = N'E:\MSSQL\Data\model.mdf',
@filename2 = N'E:\MSSQL\Log\modellog.ldf'
- SQLEM/<ServerName>/Properties/General tab/Startup parameters button
. Remove line -T3608 from the startup parameters
- Stop MSSQLSERVER service
- Start MSSQLSERVER service
- Verify connectivity.
Moving users databases (using "pubs" as the example database)
=============================================================
- Execute the following command for each database to be moved:
NOTE: This process should be followed one complete 'detach/move/attach' per db:
USE master
GO
EXEC sp_detach_db @dbname='pubs'
- Move (or copy/rename old) pubsData.MDF and pubsLog.LDF to their new locations
- Modify the following commands to reflect the new pubsData.MDF and pubsLog.LDF file locations in the @filename references and execute the script:
USE master
GO
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'E:\MSSQL\Data\pubsdata.mdf',
@filename2 = N'E:\MSSQL\Log\pubslog.ldf'
- Verify connectivity.
August 26, 2004 at 10:33 am
The easiest way is to back up the database and then restore the database to the new location. If you are restoring it to a different Server you will need to remove the users and then add them back in. Since the SID is a combination of Server SID and User SID, the user will not be able to connect to the database until the SID entire SID is correct. This is the easiest way. If you need more details drop me a line @ ADowek@engineer.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply