USE tempdb
SET QUOTED_IDENTIFIER ON   -- In case we are running from SQLCMD or Agent.
SET XACT_ABORT ON
go
-- Temporary stored procedure that we use to run commands to change database state.
-- Having the database in single-user mode is not sufficient, since these commands
-- have a window that permits other processes come in between.
CREATE OR ALTER PROCEDURE #RunWithRetry @DB sysname,
                                        @stmt nvarchar(MAX) AS
DECLARE @retry      int = 5,
        @sql        nvarchar(MAX)

DECLARE @spids TABLE (spid int NOT NULL)

-- If caller has used @DB as placeholder in statement, replace it.
SELECT @stmt = replace(@stmt, '@DB', quotename(@DB))

Again:
   IF (SELECT user_access_desc FROM sys.databases WHERE name = @DB) = 'SINGLE_USER'
   BEGIN
      -- Get all spids holding or waiting a lock for the database.
      INSERT @spids (spid)
         SELECT request_session_id
         FROM   sys.dm_tran_locks
         WHERE  resource_database_id = db_id(@DB)

      -- Kill all of 'em - except for ourselves. Ignore any errors with KILL. (And particularly we
      -- don't care if the process is gone.)
      SELECT @sql = string_agg(convert(nvarchar(MAX),
                               concat('BEGIN TRY KILL ', spid, ' END TRY BEGIN CATCH END CATCH')), ' ')
      FROM   @spids
      WHERE  spid <> @@spid


      IF @sql IS NOT NULL
      BEGIN
         EXEC(@sql)
         -- A short grace period to permit for rollbacks etc. Can't wait
         -- too long, because of the risk of a new process sneaking in.
         WAITFOR DELAY '00:00:00.200'
      END
   END

   -- Now we may have the database for ourselves, so try running the critical commands, but with a retry,
   -- because the commands themselves are not safe.
   BEGIN TRY
      -- Force the database to single user if it is not there. (If the database was in multi-user when
      -- we started, this may be more reliable than a KILL craze.)
      SELECT @sql = 'ALTER DATABASE ' + quotename(@DB) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
      PRINT @sql
      EXEC(@sql)

      -- And run the statement itself.
      SELECT @sql = @stmt
      PRINT @sql
      EXEC(@sql)
   END TRY
   BEGIN CATCH
      PRINT concat_ws(' - ', error_message(), @retry, @sql)
      SET @retry -= 1
      IF @retry > 0
         GOTO Again
      -- Give up.
      ; THROW
   END CATCH
go
BEGIN TRY
   -- This temp table serves as a flag whether execution should still be running.
   CREATE TABLE #AllIsOK (a int NOT NULL)

   -- If there is an _old databsae already, drop it!
   IF db_id('YourDBold') IS NOT NULL
      EXEC #RunWithRetry 'YourDBold', 'DROP DATABASE @DB'

   -- Restore the YourDB database to files names that are unique for the day.
   DECLARE @prodbackup       nvarchar(200) = 'D:\temp\YourDBPROD.BAK',
           @datadir          nvarchar(200) = cast(serverproperty('InstanceDefaultDataPath') as nvarchar(200)),
           @logdir           nvarchar(200) = cast(serverproperty('InstanceDefaultLogPath') as nvarchar(200)),
           @datafilepath     nvarchar(200),
           @logfilepath      nvarchar(200)


   SELECT @datafilepath = @datadir + concat('YourDB_', replace(convert(datetime2(0), sysdatetime()), ':', ''), '.mdf'),
          @logfilepath  = @logdir + concat('YourDB_', replace(convert(datetime2(0), sysdatetime()), ':', ''), '.ldf')

   -- Restore it as YourDBnew, so that the other database is available.
   RESTORE DATABASE YourDBNew FROM DISK = @prodbackup
   WITH MOVE 'YourDB' TO @datafilepath,
        MOVE 'YourDB_log' TO @logfilepath,
        REPLACE

   -- Rename any existing database.
   IF db_id('YourDB') IS NOT NULL
   BEGIN
      EXEC #RunWithRetry N'YourDB', N'ALTER DATABASE @DB MODIFY NAME = YourDBold'

      -- And restore it to multi user.
      EXEC #RunWithRetry N'YourDBold', N'ALTER DATABASE @DB SET MULTI_USER'
   END

   -- Rename the new database. (This will leave the database in single-user mode.
   EXEC #RunWithRetry N'YourDBNew', N'ALTER DATABASE YourDBNew MODIFY NAME = YourDB'

   -- Make database configurations.
   ALTER DATABASE YourDB SET NEW_BROKER
   ALTER DATABASE YourDB SET RECOVERY SIMPLE
END TRY
BEGIN CATCH
   DROP TABLE IF EXISTS #AllIsOk
   ; THROW
END CATCH
go
IF object_id('tempdb..#AllIsOk') IS NOT NULL
BEGIN TRY
   -- Move over to YourDB.
   USE YourDB
END TRY
BEGIN CATCH
   DROP TABLE IF EXISTS #AllIsOk
   ; THROW
END CATCH
go
IF object_id('tempdb..#AllIsOk') IS NOT NULL
BEGIN TRY
   DECLARE @sql nvarchar(MAX),
           @nl  char(2) = char(13) + char(10)

   -- Diagnostics.
   PRINT concat('We are now in database "', db_name(), '", with dbid = ', db_id(), '.')



   IF db_id('YourDBOld') IS NOT NULL
   BEGIN
      ------------------------- Drop everything not in the old test/dev database.
      -- Users.
      SELECT @sql = string_agg('DROP USER ' + quotename(name), @nl)
      FROM   (SELECT name
              FROM   sys.database_principals
              WHERE  type IN ('U', 'S', 'G')
              EXCEPT
              SELECT name
              FROM   YourDBold.sys.database_principals
              WHERE  type IN ('U', 'S', 'G')) AS e
      PRINT @sql
      EXEC(@sql)

      -- Role membership.  (Always ignore certificate users.)
      SELECT @sql = string_agg('ALTER ROLE ' + quotename(rolename) + ' DROP MEMBER ' + quotename(username), @nl)
      FROM   (SELECT r.name AS rolename, u.name AS username
              FROM   sys.database_principals r
              JOIN   sys.database_role_members rm ON rm.role_principal_id = r.principal_id
              JOIN   sys.database_principals u ON rm.member_principal_id = u.principal_id
              WHERE  u.type <> 'C'
              EXCEPT
              SELECT r.name AS rolename, u.name AS username
              FROM   YourDBold.sys.database_principals r
              JOIN   YourDBold.sys.database_role_members rm ON rm.role_principal_id = r.principal_id
              JOIN   YourDBold.sys.database_principals u ON rm.member_principal_id = u.principal_id
              WHERE  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)

      -- Roles.
      SELECT @sql = string_agg('DROP ROLE ' + quotename(name), @nl)
      FROM   (SELECT name
              FROM   sys.database_principals
              WHERE  type = 'R'
              EXCEPT
              SELECT name
              FROM   YourDBold.sys.database_principals
              WHERE  type = 'R') AS e
      PRINT @sql
      EXEC(@sql)

      -- Permissions on object level.
      SELECT @sql = string_agg('REVOKE ' + permission_name COLLATE database_default + ' ON ' +
                                quotename(schemaname) + '.' + quotename(objname) +
                                ' FROM ' + quotename(username), @nl)
      FROM   (SELECT dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
              FROM   sys.database_permissions dp
              JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   sys.objects o ON dp.major_id = o.object_id
              JOIN   sys.schemas s ON s.schema_id = o.schema_id
              WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
                AND  u.type <> 'C'
              EXCEPT
              SELECT dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
              FROM   YourDBold.sys.database_permissions dp
              JOIN   YourDBold.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   YourDBold.sys.objects o ON dp.major_id = o.object_id
              JOIN   YourDBold.sys.schemas s ON s.schema_id = o.schema_id
              WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
                AND  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)

      -- Permissions on schema level...
      SELECT @sql = string_agg('REVOKE ' + permission_name COLLATE database_default + ' ON SCHEMA::' +
                                quotename(schemaname) + ' FROM ' + quotename(username), @nl)
      FROM   (SELECT dp.permission_name, s.name AS schemaname, u.name AS username
              FROM   sys.database_permissions dp
              JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   sys.schemas s ON dp.major_id = s.schema_id
              WHERE  dp.class_desc = 'SCHEMA'
                AND  u.type <> 'C'
              EXCEPT
              SELECT dp.permission_name, s.name AS schemaname, u.name AS username
              FROM   YourDBold.sys.database_permissions dp
              JOIN   YourDBold.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   YourDBold.sys.schemas s ON dp.major_id = s.schema_id
              WHERE  dp.class_desc = 'SCHEMA'
                AND  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)

      -- And database permissions.
      SELECT @sql = string_agg('REVOKE ' + permission_name COLLATE database_default +
                               ' FROM ' + quotename(username), @nl)
      FROM   (SELECT dp.permission_name, u.name AS username
              FROM   sys.database_permissions dp
              JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              WHERE  dp.class_desc = 'DATABASE'
                AND  u.type <> 'C'
              EXCEPT
              SELECT dp.permission_name, u.name AS username
              FROM   YourDBold.sys.database_permissions dp
              JOIN   YourDBold.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              WHERE  dp.class_desc = 'DATABASE'
                AND  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)

      -------------------------------------- Grant permissionss only in the old dev/test database. ---------------------------
      -- Users.
      SELECT @sql = string_agg('CREATE USER ' + quotename(name) + without, @nl)
      FROM   (SELECT name, IIF (type = 'S' and suser_sname(sid) IS NULL, ' WITHOUT LOGIN', '') AS without
              FROM   YourDBold.sys.database_principals
              WHERE  type IN ('U', 'S', 'G')
              EXCEPT
              SELECT name, IIF (type = 'S' and suser_sname(sid) IS NULL, ' WITHOUT LOGIN', '') AS without
              FROM   sys.database_principals
              WHERE  type IN ('U', 'S', 'G')) AS e
      PRINT @sql
      EXEC(@sql)

      -- Roles
      SELECT @sql = string_agg('CREATE ROLE ' + quotename(name), @nl)
      FROM   (SELECT name
              FROM   YourDBold.sys.database_principals
              WHERE  type = 'R'
              EXCEPT
              SELECT name
              FROM   sys.database_principals
              WHERE  type = 'R') AS e
      PRINT @sql
      EXEC(@sql)

      -- Role memmbership..
      SELECT @sql = string_agg('ALTER ROLE ' + quotename(rolename) + ' ADD MEMBER ' + quotename(username), @nl)
      FROM   (SELECT r.name AS rolename, u.name AS username
              FROM   YourDBold.sys.database_principals r
              JOIN   YourDBold.sys.database_role_members rm ON rm.role_principal_id = r.principal_id
              JOIN   YourDBold.sys.database_principals u ON rm.member_principal_id = u.principal_id
              WHERE  u.type <> 'C'
              EXCEPT
              SELECT r.name AS rolename, u.name AS username
              FROM   sys.database_principals r
              JOIN   sys.database_role_members rm ON rm.role_principal_id = r.principal_id
              JOIN   sys.database_principals u ON rm.member_principal_id = u.principal_id
              WHERE  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)

      -- Object level permissions.
      SELECT @sql = string_agg(state_desc + ' ' + permission_name COLLATE database_default + ' ON ' +
                                quotename(schemaname) + '.' + quotename(objname) +
                                ' TO ' + quotename(username), @nl)
      FROM   (SELECT dp.state_desc, dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
              FROM   YourDBold.sys.database_permissions dp
              JOIN   YourDBold.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   YourDBold.sys.objects o ON dp.major_id = o.object_id
              JOIN   YourDBold.sys.schemas s ON s.schema_id = o.schema_id
              WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
                AND  u.type <> 'C'
                AND  EXISTS (SELECT *
                             FROM   sys.objects o2
                             JOIN   sys.schemas s2 ON s2.schema_id = o2.schema_id
                             WHERE  s2.name = s.name
                               AND  o2.name = o.name)
              EXCEPT
              SELECT dp.state_desc,dp.permission_name, s.name AS schemaname, o.name AS objname, u.name AS username
              FROM   sys.database_permissions dp
              JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   sys.objects o ON dp.major_id = o.object_id
              JOIN   sys.schemas s ON s.schema_id = o.schema_id
              WHERE  dp.class_desc = 'OBJECT_OR_COLUMN'
                AND  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)

      -- On schema level.
      SELECT @sql = string_agg(state_desc + ' ' + permission_name COLLATE database_default + ' ON SCHEMA::' +
                                quotename(schemaname) + ' TO ' + quotename(username), @nl)
      FROM   (SELECT dp.state_desc, dp.permission_name, s.name AS schemaname, u.name AS username
              FROM   YourDBold.sys.database_permissions dp
              JOIN   YourDBold.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   YourDBold.sys.schemas s ON dp.major_id = s.schema_id
              WHERE  dp.class_desc = 'SCHEMA'
                AND  u.type <> 'C'
                AND  EXISTS (SELECT *
                             FROM   sys.schemas s2
                             WHERE  s2.name = s.name)
              EXCEPT
              SELECT dp.state_desc, dp.permission_name, s.name AS schemaname, u.name AS username
              FROM   sys.database_permissions dp
              JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              JOIN   sys.schemas s ON dp.major_id = s.schema_id
              WHERE  dp.class_desc = 'SCHEMA'
                AND  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)


      -- On database...
      SELECT @sql = string_agg(state_desc + ' ' + permission_name COLLATE database_default +
                                ' TO ' + quotename(username), @nl)
      FROM   (SELECT dp.state_desc, dp.permission_name, u.name AS username
              FROM   YourDBold.sys.database_permissions dp
              JOIN   YourDBold.sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              WHERE  dp.class_desc = 'DATABASE'
                AND  u.type <> 'C'
              EXCEPT
              SELECT dp.state_desc, dp.permission_name, u.name AS username
              FROM   sys.database_permissions dp
              JOIN   sys.database_principals u ON dp.grantee_principal_id = u.principal_id
              WHERE  dp.class_desc = 'DATABASE'
                AND  u.type <> 'C') AS e
      PRINT @sql
      EXEC(@sql)
   END

   -- Shrink log since we are in simple recovery now.
   DBCC SHRINKFILE(YourDB_log, 1000)


   -- Open the database for the public. Do this before we refresh permissions, because that step
   -- has falied with the database single-user.
   EXEC #RunWithRetry N'YourDB', N'ALTER DATABASE @DB SET MULTI_USER'

END TRY
BEGIN CATCH
  ; THROW
END CATCH