General discussion

Locked

Migrating external drive array

By jonathan.gray ·
Consider the following scenario

I have a SQL server with the databases stored on a external drive array. I want to migrate the ext drive array onto a new SQL server.
The problem i have is that both systems are running NT 4 server and SQL 7.

The old server name is ESC03 and the new is ESC08, both servers have been configured to store database's on the \ and systen files on C

Has anyone attempted anything like this before or any idea's on possible problems.


Thanks is advance

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by jonathan.gray In reply to Migrating external drive ...

bear in mind that ther server names are different

Collapse -

by sgt_shultz In reply to Migrating external drive ...

see if this helps. check out mskb Q224071 article from support.microsoft.com
<snip>
INF: Moving SQL Server Databases to a New Location with Detach/Attach
This article describes how to change the location of the data and log files for any SQL Server 7.0 or SQL Server 2000 database.
The steps involved in changing the location for some SQL Server system databases is not the same as for user databases. These special cases are described separately.
NOTE: SQL Server 7.0 system databases are not compatible with SQL Server 2000. Do not attach SQL Server 7.0 master, model, msdb or distribution databases to SQL Server 2000.

Collapse -

by sgt_shultz In reply to

All of the examples in this article assume that SQL Server is installed in the \Mssql7 directory with all database and log files located in the default directory \Mssql7\Data. The examples move the data and log files for all the databases to E:\Sqldata.
Prerequisites
Make a current backup of all databases, especially master, from their current location.
You must have system administrator (sa) privileges.
You must know the name and current location of all data and log files for the database.

NOTE: You can determine the name and current location of all files used by a database with the sp_helpfile stored procedure:use <database_name>
go
sp_helpfile
go

You should have exclusive access to the database being moved. If you encounter problems during the process and are unable to access a database you have moved, or are unable to start SQL Server, check the SQL Server error log and SQL Server Books Online for details on the errors encountered.
Moving User Databases
The following example moves a database named mydb, which contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database you are moving has additional data or log files, specify all of them in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change no matter how many files the database contains because it does not list them.

Collapse -

by sgt_shultz In reply to Migrating external drive ...

Detach the database as follows: use master
go
sp_detach_db 'mydb'
go

Next, copy the data and log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
Re-attach the database pointing to the files in the new location as follows: use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go

Verify the change in file locations using sp_helpfile: use mydb
go
sp_helpfile
go

The filename column values should reflect the new locations.
Moving Pubs and Northwind
Follow the same procedure for moving user databases.
Moving MSDB (SQL Server 7.0)
NOTE: If you are using this procedure in conjunction with moving the msdb and model databases, the order of reattachment must be model first and then msdb. If msdb is reattached first, it must be detached and not reattached until after model has been attached.
Make sure the SQL Server Agent is not currently running.
Follow the same procedure for moving user databases.
NOTE: If SQL Server Agent is running, the sp_detach_db stored procedure fails with the following message:

Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'msdb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
NOTE: If you are using this procedure in conjunction with moving the msdb and model databases, the order of reattachment must be model first and then msdb. If msdb is reattached first, it must be detached and not reattached until after model has been attached.
<snip>
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
274188 PRB: Troubleshooting Orphaned Users Topic in BOL Incomplete

246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server

168001 PRB: User Logon and/or Permission Errors After Restoring Dump

Back to Windows Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums