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.
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