Automate DB Restore

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