Monday, December 31, 2007

Moving a SQL Server Database

Should your SQL server need to be decommission, it will be necessary to move the databases.

Two method: backup or detach.
Backing up requires an intermediate BAK file, so I prefer the detach method.

1) Shrink the datafile. You can truncate the log by:
2) Then use the GUI to shrink the log file, or if you know the log file name use: dbcc shrinkfile (file_name)
3) Detach
 sp_detach_db 'dbname'
4) Copy the database to the new server
5) On the new server attach the database:
 sp_attach_db 'dbname','D:\Sqldata\mydbdata.mdf','L:\Sqllog\mydblog.ldf'
6) Repair/fix SQL logins. If new server already has the same named logins, use the below command:
 EXEC sp_change_users_login 'auto_fix', ''

To compliment step 6, you can determine which logins need repair by running a discrepancy check:

select 'EXEC sp_change_users_login ''auto_fix'', ''' + name + ''''
from sysusers su
where hasdbAccess = 1
and not exists ( SELECT 1 FROM master..syslogins sl WHERE su.sid = sl.sid )

No comments:

Share Links