Differences

This shows you the differences between two versions of the page.

--- restore_sql_server_database [2018/12/06 21:05] – created 91.177.234.129
+++ restore_sql_server_database [2019/01/30 11:32] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Restore_SQL_Server_database ======
+  *  First copy the dump from \\\\spifls07.mon.local\\DATA.1013\\01_DBdumps\\SQL\\ to the target SQL Server in ONT
+  *  Check the current security settings on the ONT database, use this script  [[Link List all permission in DB]].
-  * First copy the dump from \\spifls07.mon.local\DATA.1013\01_DBdumps\SQL\ to the target SQL Server in ONT
+  *  Restore over the existing database. Use the GUI interface to setup the restore, check the 'with replace' box, then script the statement to a new window.
-  * Check the current security settings on the ONT database, use this script  [[Link List all permission in DB]].
+  *  There alter the script to get the following result
-  * Restore over the existing database. Use the GUI interface to setup the restore, check the 'with replace' box, then script the statement to a new window.
+<code>
-  * There alter the script to get the following result
   ALTER DATABASE [[DBToRestore]] SET Single_user with rollback immediate
   GO
   RESTORE DATABASE ...
-  * Let the restore do it's thing
+</code>
-  * When finished, recreate the premission with the above generated script
+  *  Let the restore do it's thing
-  * Eventually clean up the MON accounts ...
+  *  When finished, recreate the premission with the above generated script
-  * If any SQL accounts are restored, fix them with the following statement
+  *  Eventually clean up the MON accounts ...
+  *  If any SQL accounts are restored, fix them with the following statement
+<code>
   exec sp_change_users_login 'auto_fix', 'usernametofix'
+</code>
 [[Category:SQL Server]]
 [[Category:BackupRestore]]
 <code>
 set nocount on
@@ Line 24: / Line 26: @@
 declare @as_ObjectName sysname
 set @as_ObjectName = NULL
 --database owner info
-select 'alter authorization on database::['+db_name()+'] to ['+ suser_sname(owner_sid)+']'
+select 'alter authorization on database::[['+db_name()+']] to [['+ suser_sname(owner_sid)+']]'
 AS '--owner of database when script was created'
 from master.sys.databases where name = db_name()
 --drop and recreate users
 select '-- It is not always necessary to drop and recreate the users it will depend on the circumstances under which you need to run this script'
-select 'drop user [' + name + ']' from sys.database_principals
+select 'drop user [[' + name + ']]' from sys.database_principals
 where principal_id > 4 and owning_principal_id is NULL
 and type != 'A'
 order by name
-select 'CREATE USER [' + dp.name collate database_default + '] FOR LOGIN [' + sp.name + ']'+
+select 'CREATE USER [[' + dp.name collate database_default + ']] FOR LOGIN [[' + sp.name + ']]'+
 case dp.type
 when 'G' then ' '
 else
-' WITH DEFAULT_SCHEMA=['+dp.default_schema_name + ']'
+' WITH DEFAULT_SCHEMA=[['+dp.default_schema_name + ']]'
 end
 as '-- by default Orphaned users will not be recreated'
 from sys.server_principals sp
 inner join sys.database_principals dp on dp.sid = sp.sid
-where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> ''
+where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> //
 order by dp.name
 -- Recreate the User defined roles
 select '-- server created roles should be added by the correct processes'
-select 'CREATE ROLE ['+ name + '] AUTHORIZATION ['+USER_NAME(owning_principal_id)+']'
+select 'CREATE ROLE [['+ name + ']] AUTHORIZATION [['+USER_NAME(owning_principal_id)+']]'
 from sys.database_principals
 where name != 'public' and type = 'R' and is_fixed_role = 0
 -- recreate application roles
-select 'CREATE APPLICATION ROLE ['+ name + '] with password = '+QUOTENAME('insertpwdhere','''')+' ,default_schema = ['+default_schema_name+']'
+select 'CREATE APPLICATION ROLE [['+ name + ']] with password = '+QUOTENAME('insertpwdhere',**')+' ,default_schema = [['+default_schema_name+']]'
 from sys.database_principals
 where type = 'A'
 -- ADD ROLE MEMBERS
-SELECT 'EXEC sp_addrolemember [' + dp.name + '], [' + USER_NAME(drm.member_principal_id) + '] ' AS [-- AddRolemembers]
+SELECT 'EXEC sp_addrolemember [[' + dp.name + ']], [[' + USER_NAME(drm.member_principal_id) + ']] ' AS [[-- AddRolemembers]]
 FROM sys.database_role_members drm
 INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
 where USER_NAME(drm.member_principal_id) != 'dbo'
 order by drm.role_principal_id
 -- CREATE GRANT Object PERMISSIONS SCRIPT
-SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON ['
+SELECT replace(state_desc,'_with_grant_option',//) + ' '+ permission_name + ' ON [['
-+ OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + ']'+
++ OBJECT_SCHEMA_NAME(major_id) + ']].[[' + OBJECT_NAME(major_id) + ']]'+
 case minor_id
 when 0 then ' '
 else
-' (['+col_name(sys.database_permissions.major_Id, sys.database_permissions.minor_id) + '])'
+' ([['+col_name(sys.database_permissions.major_Id, sys.database_permissions.minor_id) + ']])'
 end
-+' TO [' + USER_NAME(grantee_principal_id)+']' +
++' TO [[' + USER_NAME(grantee_principal_id)+']]' +
 case
 when state_desc like '%with_grant_option' then ' with grant option'
@@ Line 95: / Line 97: @@
 --AND OBJECT_SCHEMA_NAME(major_id) != 'SYS'
 ORDER BY USER_NAME(grantee_principal_id),OBJECT_SCHEMA_NAME(major_id), OBJECT_NAME(major_id)
 --SCHEMA permissions
-SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON SCHEMA::['
+SELECT replace(state_desc,'_with_grant_option',//) + ' '+ permission_name + ' ON SCHEMA::[['
-+ SCHEMA_NAME(major_id) + ']'+
++ SCHEMA_NAME(major_id) + ']]'+
-+' TO [' + USER_NAME(grantee_principal_id)+']' +
++' TO [[' + USER_NAME(grantee_principal_id)+']]' +
 case
 when state_desc like '%with_grant_option' then ' with grant option'
@@ Line 109: / Line 111: @@
 WHERE class_desc = 'SCHEMA'
 ORDER BY USER_NAME(grantee_principal_id),SCHEMA_NAME(major_id)
-SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name +
+SELECT replace(state_desc,'_with_grant_option',//) + ' '+ permission_name +
-' TO [' + USER_NAME(grantee_principal_id)+']' +
+' TO [[' + USER_NAME(grantee_principal_id)+']]' +
 case
 when state_desc like '%with_grant_option' then ' with grant option'
@@ Line 122: / Line 124: @@
 </code>
 [[Category:SQL Server]]