To automate the database restore, create the following steps.
Step 1: Take Backup of
source and destination.
BACKUP DATABASE <DB Name> TO DISK = <disk path>
Step 2: Script out permissions on destination
database and save it or store it in a table.
Use <DB name >
GO
SET NOCOUNT ON
print 'Use ' + db_name()
print 'go'
--Grant DB Access---
select 'if not exists (select * from dbo.sysusers where name = N''' + usu.name +''' )' + Char(13) + Char(10) +
' EXEC sp_grantdbaccess N''' + lo.loginname+ '''' + ', N''' + usu.name + '''' + Char(13) + Char(10) +
'GO'
from sysusers usu , master.dbo.syslogins lo
where usu.sid = lo.sid and (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)
go
--Add Roles---
select 'if not exists (select * from dbo.sysusers where name = N''' + name +''' )' + Char(13) + Char(10) +
' EXEC sp_addrole N''' + name + '''' + Char(13) + Char(10) +
'GO'
from sysusers where uid> 0 and uid=gid and issqlrole=1
go
--Add RoleMember---
select 'exec sp_addrolemember N''' + user_name(groupuid) + ''', N''' + user_name (memberuid) + '''' + Char(13) + Char(10) +
'GO'
from sysmembers where user_name (memberuid) <> 'dbo' order by groupuid
--Add Alias Login also---
select 'if not exists (select * from dbo.sysusers where name = N''' + a.name +''' )' + Char(13) + Char(10) +
' EXEC sp_addalias N''' + substring(a.name , 2, len(a.name)) + '''' + ', N''' + b.name + '''' + Char(13) + Char(10) +
'GO'
from sysusers a , sysusers b where a.altuid = b.uid and a.isaliased=1
go
SET NOCOUNT OFF
--Add object & DB level permission also---
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Step 3: Refresh
destination database from source database backup.
----Make Database to single user Mode
ALTER DATABASE <Destination DB>
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE <Destination DB>
FROM DISK = <disk path>
Step 4: Apply Step 2 Output: saved in table on destination database.
Step 5: Run orphan user
script to fix orphan users on destination database. Save the output in a different table.
Use <destination_database>.
GO
setnocount on
select 'exec' +' '+'sp_change_users_login ''update_one'',''' + name + ''',''' + name + '''' +char(13)
fromsysuserssu
wheresid NOT IN
(selectsid from master..syslogins )
AND
islogin = 1
AND
name NOT LIKE 'guest' and su.issqluser=1
Step 6: Run step 5 output: saved in table on the destination database
Step 7: Change database
owner to SA.
Use <destination_database>
GO
sp_changedbownersa
