MSSQL Database Server Migration from default instance to named Instance

Task: Migrating ERA DB to DUDB\ERA

Senario: -There were 44 databases and its associated users along with SQL server Agent jobs, It was a tedious and time consuming task which includes following steps

  • Attach/ restore all databases
  • Migrate SQL server logins from ERADB to DUDB which in-turn disturbs the user mapping between Database due to SID issue
  • Migrate SQL agent Jobs

To reduce the repetitive manual work and save time , I choose to Migrate Entire instance with the help of system databases

Issues :-

  1. On ERADB SQL server was using default instance and on DUDB Instance name is ERA
  2. The folder structure of SQL server on ERADB was C:\Program Files\Microsoft SQL Server\
    MSSQL_10_50.MSSQLSERVER\MSSQL
    and on DUDB it was C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\

    The Folder structure is getting used by SYSTEM databases of SQL server

Solution worked to resolve the issues

  1. First I have changed the SQL server instance name on ERADB as below

     

    1. Check the current instance name

select
@@servername

 

  1. Remove the existing instance name


sp_dropserver
‘ERADB’

go

c) change the new Instance name


sp_addserver
‘DUDB\ERA’
,
‘local’


go

 

d) Verify sql
server instance configuration


sp_helpserver


select
@@servername

 

  1. Changing the folder structure of SYSTEM databases ( MASTER, MSDB,TEMPDB,MODEL )
    1. First created the folder structure as of DUDB on ERADB
    2. Stopped SQL server service on ERADB
    3. Copied all System databases to the newly created folder structure
    4. Started SQL server Service
    5. Now the main task to tell SQL server about this folder structure change

       

       

    To do so used below tricks

For msdb Database :- MSDB database contains all the SQL Agent jobs , and its schedules..

Use master
go
Alter database msdb modify file (name=msdbdata,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\msdbdata.mdf’)
go
Alter database msdb modify file (name=msdblog,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\msdblog.ldf’)
go

For tempdb Database :- in tempDB all the temporary variables, tables transactions are carried out

Use master
go
Alter database tempdb modify file (name=tempdev,filename= ‘C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\tempdb.mdf’)
go
Alter database tempdb modify file (name=templog,filename= ‘C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\tempdb.ldf’)
go

For MODEL Database

Use master
go
Alter database model modify file (name=modeldev,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\model.mdf’)
go

Alter database model modify file (name=modellog,filename= C:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\modellog.ldf’)
go

And the LAST

For master Database :- as Master database is the Core database of SQL server in which all the user databases , and all users information is present.

Open regedit

In HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server \MSSQL_10_50.MSSQLSERVER\MSSQLSERVER\Parameters

Change the vales as below

SQLARG0 = -dC:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\ master.mdf

SQLARG1 = -eC:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\Log \ERRORlog

SQLARG2 = -lC:\Program Files\Microsoft SQL Server ERA\MSSQL10_50.ERA\MSSQL\DATA\ mastlog.ldf

 

  1. Now restarted SQL server service on ERADB, checked the files path for system databases and found it has been pointing to new folder structure location.

     

  2. Then stopped SQL server on ERADB

     

  3. Stopped SQL Server service on DUDB for ERA instance.

     

  4. Created d:\ERADB and E:\ERADB2 on DUDB server

     

  5. Copied all subfolders of ERADB and ERADB2 from eradb server to dudb server

     

  6. Taken backup of system databases of DUDB for ERA instance

     

  7. Copied system databases from ERADB ( new folder structure ) to DUDB era instance folder

     

  8. Started SQL Server service on DUDB for ERA instance.
Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s