SQL DBA Guru

Case Scenario

We are in the process of hardware movement form a lower configuration to a higher end box. Being the DBA, I am responsible to migrate the SQL Server along with all the databases, logins and related permissions. I planned the migration as – Install SQL Server on the new server followed by all the databases migration one by one. I am going to note down the step by step procedure for this. You can also use these steps while overwriting a database on the same server or refreshing a test or development database from the production database copy.

Resolution

Follow the following steps:

1. Install SQL Server on the new hardware.
2. Take the Full backup of the databases one by one.
3. Script out all the users. Click on the database à Security à Users on the left pane of the Management Studio. Select all the users and Right Click. Choose Script user as à Create To à File and save the generated script.
4. Next script out all the Database Roles. Click on the database à Security à Roles à Database Roles on the left pane of the Management Studio. Select all the database roles and Right Click. Choose Script Database Role as à Create To à File and save the generated script.
5. Similarly, take the script of all schemas and save the generated script.
6. Then we need to script out the Database level object level permissions. To generate this script, right click on the database --> Tasks --> Generate Scripts  --> Generate and Publish scripts window appears.

             

  

1. Click on the Choose Objects option from the left side, and click on Script entire database and all database objects. Click Next.
2. The next window is Set Scripting Options. In the Output Type, Click on the first option i.e. Save scripts to a specific location. From the down frame, Select Save to file option and give a path for the file to be saved. Click on the Advanced button.

            

 

1. Change all options to False except Script Object-Level Permission, which should be set to True.

             

10.  Save the script file.

11.  Next, script out all the logins. Windows Login IDs are very easy to script, simple right click and Choose script but for SQL Login IDs, you would need to script out along with their passwords as well. The following script will help you to script out all SQL Logins with their passwords, which will be encrypted.

 

SET NOCOUNT ON

SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''

,', @defdb = ''' + dbname + ''''

,', @deflanguage = ''' + language + ''''

,', @encryptopt = ''skip_encryption'''

,', @passwd ='

, cast(password AS varbinary(256))

,', @sid ='

, sid

FROM syslogins

WHERE name NOT IN ('sa')

AND isntname = 0

 

 

12.  Take the User roles mapping by following script, per database.

 

      set nocount on

      select 'USE ' + db_name() + ' GO '

      UNION

      select 'exec sp_addrolemember N''' + rtrim(g.name) + ''',N''' + u.name + ''''

                  from sysusers u, sysusers g, sysmembers m,master..syslogins l

                  wHERE l.SID = u.sid

                  and    g.uid = m.groupuid

                  and       u.uid = m.memberuid

                  and u.name<>'dbo'

 

Remarks:

Once all the scripts are ready, apply them on the newly restored database on the new server.

• By default, only a member of the sysadmin fixed server role can do this.
• In the output script, the logins are created by using the encrypted password.