Stuart at 22:11, 28 April 2014

2014-04-28T22:11:20Z

127.0.0.1: Created page with "* 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 scri..."

2011-12-06T19:43:09Z

Created page with "* 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 scri..."

New page

* 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]].
* 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.
* 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
* When finished, recreate the premission with the above generated script
* Eventually clean up the MON accounts ...
* If any SQL accounts are restored, fix them with the following statement
exec sp_change_users_login 'auto_fix', 'usernametofix'

[[Category:SQL Server]]
[[Category:BackupRestore]]

<nowiki>
set nocount on
set quoted_identifier off
declare @as_ObjectName sysname
set @as_ObjectName = NULL

--database owner info
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
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 + ']'+
case dp.type
when 'G' then ' '
else
' 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 <> ''
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)+']'
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+']'
from sys.database_principals
where type = 'A'

-- ADD ROLE MEMBERS

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 ['
+ 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) + '])'
end
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- object/column permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class not in (0,3) and major_id = ISNULL(OBJECT_ID(@as_ObjectName), major_id)
--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::['
+ SCHEMA_NAME(major_id) + ']'+
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- Schema permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class_desc = 'SCHEMA'
ORDER BY USER_NAME(grantee_principal_id),SCHEMA_NAME(major_id)

SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name +
' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
FROM sys.database_permissions (NOLOCK)
WHERE permission_name = 'VIEW DEFINITION' and class_desc = 'database'
ORDER BY USER_NAME(grantee_principal_id)
</nowiki>
[[Category:SQL Server]]

@@ Line 17: / Line 17: @@
-<nowiki>
+<pre>
 set nocount on
 set quoted_identifier off
@@ Line 118: / Line 118: @@
 WHERE permission_name = 'VIEW DEFINITION' and class_desc = 'database'
 ORDER BY USER_NAME(grantee_principal_id)
-</nowiki>
+</pre>
 [[Category:SQL Server]]

Restore SQL Server database - Revision history

Stuart at 22:11, 28 April 2014

127.0.0.1: Created page with "* 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 scri..."