Fresh Backup Copy

--Create below table on your database server.

USE [DBATasks]
GO
IF OBJECT_ID(N'RefreshDBs', N'U') IS NULL
BEGIN  
CREATE TABLE [dbo].[RefreshDBs](
[SourceServer] [sysname] NOT NULL,
[DBName] [sysname] NOT NULL,
[FreshBackup] [char](5) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[RefreshDBs] ([SourceServer], [DBName], [FreshBackup]) VALUES (N'SourceServer\Instance', N'DatabaseName', N'Yes')
GO

--Schedule below code as SQL Agent Job on same database server where above table exists 

$databases = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database msdb -Query "SELECT SourceServer, DBName, FreshBackup from DBATasks..RefreshDBs;"

$databases

foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
$Inst = $database.SourceServer #instance from the select query
$DBname = $database.DBName #databasename from the select query
$FBackup = $database.FreshBackup

$Fbackup

if ( $FBackup -match 'Yes')
{
$Fbackup
sqlcmd -E  -S $Inst -d DBATasks -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = $DBName, @Directory = NULL, @BackupType = 'FULL', @Compress = 'Y', @Verify = 'Y', @CleanupTime = 23, @CheckSum = 'Y', @LogToTable = 'Y'"
}

$Inst
$BackupLoation =  invoke-sqlcmd -ServerInstance $Inst  -Database msdb -Query "
DECLARE @BPath varchar(900);
SELECT TOP 1 @BPath = physical_device_name
FROM msdb.dbo.backupset b
     JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
       WHERE database_name like '$DBName'

             AND b.type = 'D'
       ORDER BY backup_finish_date DESC;

IF @BPath LIKE '_:\%'
SET @BPath = '\\'+CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+'\'+REPLACE(@BPath, ':', '$');
ELSE IF @BPath like '\\%'
SET @BPath = @BPath
SELECT LEFT(@BPath, LEN(@BPath)-CHARINDEX('\', REVERSE(@BPath))) BackupFolder,
       REVERSE(LEFT(REVERSE(@BPath), CHARINDEX('\', REVERSE(@BPath))-1)) BackupFileName;
"

$CopyFolder = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database msdb -Query "DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @DBBackup VARCHAR(300);
DECLARE @DirTree TABLE
       (
       subdirectory NVARCHAR(255),
       depth        INT
       );
EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
SET @DBBackup = @DefaultBackupDirectory;
IF @DBBackup LIKE '_:\%'
SET @DBBackup = '\\'+CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+'\'+REPLACE(@DBBackup, ':', '$');
ELSE IF @DBBackup like '\\%'
SET @DBBackup = @DBBackup
SELECT @DBBackup DBBackupFile;"
$BackupLoation.BackupFolder
$BackupLoation.BackupFileName
$CopyFolder.DBBackupFile
#copy-item -Path "$BackupLocation.BackupPath" -Destination "$CopyFolder.DBBackupFile"
Robocopy $BackupLoation.BackupFolder $CopyFolder.DBBackupFile $BackupLoation.BackupFileName
}

Re-apply Database Permissions

# $databases grabs list of production databases from the SQL_DATABASES table on your Database
$PermissionsFolder = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database msdb -Query "DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @DBPermissions VARCHAR(300);
DECLARE @DirTree TABLE
       (
       subdirectory NVARCHAR(255),
       depth        INT
       );
EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
SET @DBPermissions = @DefaultBackupDirectory+'\DBPermissions';

INSERT INTO @DirTree
    (subdirectory
       , depth
    )
EXEC master.sys.xp_dirtree @DefaultBackupDirectory;
IF NOT EXISTS
     (
      SELECT 1
      FROM @DirTree
      WHERE subdirectory = 'DBPermissions'
    AND depth = 1
     )
    EXEC master.dbo.xp_create_subdir @DBPermissions;
 SELECT @DBPermissions DBPermissions;"


 

$databases = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database msdb -Query "select name  FROM master.sys.databases sd
            WHERE sd.name in ('perfstat','arg_afw_pdb' )
                  AND sd.[user_access_desc] = 'MULTI_USER'
                  AND sd.[is_in_standby] = 0;"


foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
#$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.name #databasename from the select query
$dbname
#connect to each instance\database and generate security script and output to files
$DBname
#invoke-sqlcmd -ServerInstance EDIDVWPSTPOCDB1\SQL141 -Database DBATasks -Query "exec DBATasks..[sp_DatabaseRestore] @Database = $DBname, @BackupPathFull = '\\EDIDVWPSTPOCDB1\d$\Program Files\Microsoft SQL Server\MSSQL12.SQL141\MSSQL\Backup\EDIDVWPSTPOCDB1\'"
$DBPermissionScript = $PermissionsFolder.DBPermissions +"\" + $DBname +"_DBPermissions.sql"
$DBPermissionScript
invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database master -InputFIle $DBPermissionScript
}

Restore All databases from folder

#https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_DatabaseRestore.sql

DECLARE cDatabases CURSOR
FOR SELECT name
    FROM master.sys.databases sd
           WHERE sd.name IN('perfstat', 'sqlnexus', 'pdb')
    AND sd.[user_access_desc] = 'MULTI_USER'
    AND sd.[is_in_standby] = 0;
DECLARE @DBName SYSNAME;

-- Loop through all the databases
OPEN cDatabases;
FETCH NEXT FROM cDatabases INTO @DBName;
WHILE(@@FETCH_STATUS = 0)
    BEGIN
        EXEC DBATasks..[sp_DatabaseRestore]
             @Database = @DBName,
             @BackupPathFull = '\\ServerName\d$\Program Files\Microsoft SQL Server\MSSQL12.SQL141\MSSQL\Backup\Hi\',
@RunRecovery = 1;
        FETCH NEXT FROM cDatabases INTO @DBName;
    END;
CLOSE cDatabases;
DEALLOCATE cDatabases;
GO

Script out all database permissions

--Below job will script out all database permissions and save output to default backup folder and it will create new folder there DBPermissions if not exists.
--https://sqljgood.wordpress.com/2014/09/17/using-powershell-to-loop-through-a-list-of-sql-server-databases/comment-page-1/

USE [msdb]
GO
IF EXISTS (SELECT 1 FROM msdb..sysjobs WHERE name LIKE N'Script DB Pemissions')
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name=N'Script DB Pemissions', @delete_unused_schedule=1
END
GO
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Script DB Pemissions',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'Database Maintenance',
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Script Permission',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'PowerShell',
  @command=N'# $databases grabs list of production databases from the SQL_DATABASES table on your Database
$PermissionsFolder = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database msdb -Query "DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @DBPermissions VARCHAR(300);
DECLARE @DirTree TABLE
       (
       subdirectory NVARCHAR(255),
       depth        INT
       );
EXECUTE master..xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'', N''BackupDirectory'', @DefaultBackupDirectory OUTPUT;
SET @DBPermissions = @DefaultBackupDirectory+''\DBPermissions'';

INSERT INTO @DirTree
    (subdirectory
       , depth
    )
EXEC master.sys.xp_dirtree @DefaultBackupDirectory;
IF NOT EXISTS
     (
      SELECT 1
      FROM @DirTree
      WHERE subdirectory = ''DBPermissions''
    AND depth = 1
     )
    EXEC master.dbo.xp_create_subdir @DBPermissions;
 SELECT @DBPermissions DBPermissions;"

$databases = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database msdb -Query "select name  FROM master.sys.databases sd
            WHERE HAS_DBACCESS(sd.[name]) = 1
                  AND sd.[is_read_only] = 0
                  AND sd.[state_desc] = ''ONLINE''
      AND sd.name not like ''ReportServer%''
                  AND sd.[user_access_desc] = ''MULTI_USER''
                  AND sd.[is_in_standby] = 0;"


foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
#$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.name #databasename from the select query


#generate the output file name for each server/database pair
$filepath = $PermissionsFolder.DBPermissions +"\"
$filename =  $DBname +"_DBPermissions.sql"

# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.

$outfile = ($filepath + $filename) #create out-file file name


#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database ${DBname} -Query "
DECLARE
    @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR FOR


/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT ''-- [-- DB CONTEXT --] --'' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  ''USE'' + '' '' + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '''' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/

SELECT ''-- [-- DB USERS --] --'' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  ''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + '' '' + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + '' '' + QUOTENAME([name]) + '' FOR LOGIN '' + QUOTENAME([name]) + '' WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + '' '' + ''END; '' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_principals AS rm
WHERE [type] IN (''U'', ''S'', ''G'') -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT ''-- [-- DB ROLES --] --'' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  ''EXEC sp_addrolemember @rolename =''
    + '' '' + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') + '', @membername ='' + '' '' + QUOTENAME(USER_NAME(rm.member_principal_id), '''''''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN (
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN (''G'', ''S'', ''U'') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC


UNION

SELECT '''' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT ''-- [-- OBJECT LEVEL PERMISSIONS --] --'' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> ''W'' THEN perm.state_desc
            ELSE ''GRANT''
        END
        + '' '' + perm.permission_name + '' '' + ''ON '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) --select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE ''('' + QUOTENAME(cl.name) + '')''
          END
        + '' '' + ''TO'' + '' '' + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE
                WHEN perm.state <> ''W'' THEN SPACE(0)
                ELSE '' '' + ''WITH GRANT OPTION''
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --]
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
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC



UNION

SELECT '''' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT ''-- [--DB LEVEL PERMISSIONS --] --'' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> ''W'' THEN perm.state_desc --W=Grant With Grant Option
            ELSE ''GRANT''
        END
    + '' '' + perm.permission_name --CONNECT, etc
    + '' '' + ''TO'' + '' '' + ''['' + USER_NAME(usr.principal_id) + '']'' COLLATE database_default --TO <user name>
    + CASE
            WHEN perm.state <> ''W'' THEN SPACE(0)
            ELSE '' '' + ''WITH GRANT OPTION''
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN (''G'', ''S'', ''U'') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '''' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT ''-- [--DB LEVEL SCHEMA PERMISSIONS --] --'' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> ''W'' THEN perm.state_desc --W=Grant With Grant Option
            ELSE ''GRANT''
            END
                + '' '' + perm.permission_name --CONNECT, etc
                + '' '' + ''ON'' + '' '' + class_desc + ''::'' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + '' '' + ''TO'' + '' '' + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> ''W'' THEN SPACE(0)
                    ELSE '' '' + ''WITH GRANT OPTION''
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema


ORDER BY [-- RESULT ORDER HOLDER --]


OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
        SELECT  @sql
        FETCH NEXT FROM tmp INTO @sql, @sort
END

CLOSE tmp
DEALLOCATE tmp" | Format-Table -HideTableHeaders | out-file -width 260 -filepath ($outfile)

} #end foreach loop

',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly Sunday 9PM',
  @enabled=1,
  @freq_type=8,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=1,
  @active_start_date=20170622,
  @active_end_date=99991231,
  @active_start_time=210000,
  @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Database Server and Node Information

IF SERVERPROPERTY('IsClustered') = 1

    BEGIN

        SELECT @@SERVERNAME SQLName,

               'Yes' [Cluster?],

               (

               SELECT SUBSTRING(

                      (

                      SELECT ', '+NodeName

                      FROM sys.dm_os_cluster_nodes

                             FOR xml PATH('')

                      ), 3, 8000)

               ) NodeName;

    END;

ELSE

    BEGIN

        SELECT @@SERVERNAME SQLName,

               'No' [Cluster?],

               SERVERPROPERTY('ComputerNamePhysicalNetBIOS') NodeName;

    END;

Predeploy Backup Script

USE master;
SET NOCOUNT ON;
DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @cutoff DATETIME;
DECLARE @BackupCopy VARCHAR(2000);
DECLARE @PredeployDir VARCHAR(2000);
DECLARE @name VARCHAR(50); -- database name
DECLARE @fileName VARCHAR(256); -- filename for backup
DECLARE @fileDate VARCHAR(20); -- used for file name
DECLARE @DirTree TABLE
  (
  subdirectory NVARCHAR(255),
  depth        INT
  );
SET @cutoff = DATEADD(dd, -2, GETDATE());
EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
SET @PredeployDir = @DefaultBackupDirectory+'\PreDeploy';

EXECUTE master..xp_delete_file 0, @PredeployDir, N'bak', @cutoff, 1;
EXECUTE master..xp_delete_file 0, @PredeployDir, N'trn', @cutoff, 1;

INSERT INTO @DirTree
(subdirectory
   , depth
)
EXEC master.sys.xp_dirtree @DefaultBackupDirectory;
IF NOT EXISTS
  (
   SELECT 1
   FROM @DirTree
   WHERE subdirectory = 'Predeploy'
AND depth = 1
  )
    EXEC master.dbo.xp_create_subdir @PredeployDir;
SELECT @fileDate = REPLACE(CONVERT( VARCHAR(20), GETDATE(), 112)+CONVERT(VARCHAR(5), GETDATE(), 108), ':', '');
DECLARE db_cursor CURSOR
FOR SELECT name
    FROM sys.databases
    WHERE database_id <> 2
AND name IN
 ('DBName'  -- Change Database name
 )
AND is_read_only = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
    BEGIN
  SET @fileName = @PredeployDir+'\'+@name+'_'+CONVERT(VARCHAR(128), REPLACE(@@servername, '\', '_'))+'_'+@fileDate+'.bak';
  PRINT 'Backup started for database: '+@NAME+' located @ '+@filename;
  BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 20;
  PRINT 'Backup completed for database: '+@NAME
  SELECT TOP 1 @BackupCopy = physical_device_name
  FROM msdb..backupmediafamily
  ORDER BY media_set_id DESC
  SET @BackupCopy = 'copy '+'\\'+CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+'\'+REPLACE(@BackupCopy, ':', '$')+' \\Share\DB-PreDeployBackup\';
  PRINT @BackupCopy
  FETCH NEXT FROM db_cursor INTO @name;
    END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

DDL and LOGON Trigger

USE master;
GO
IF OBJECT_ID('AuditEvents', 'U') IS  NULL
BEGIN
CREATE TABLE dbo.AuditEvents
(EventDate    DATETIME NOT NULL
                       DEFAULT CURRENT_TIMESTAMP,
 EventType    NVARCHAR(64),
 EventDDL     NVARCHAR(MAX),
 EventXML     XML,
 DatabaseName NVARCHAR(255),
 SchemaName   NVARCHAR(255),
 ObjectName   NVARCHAR(255),
 HostName     NVARCHAR(64),
 IPAddress    NVARCHAR(32),
 ProgramName  NVARCHAR(255),
 LoginName    NVARCHAR(255)
);

CREATE NONCLUSTERED INDEX [IX_AuditEvents_EventDate] ON [dbo].[AuditEvents]
(
[EventDate] ASC
) ON [PRIMARY]

END
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.server_triggers WHERE name = 'DDLTrigger')
DROP TRIGGER [DDLTrigger] ON ALL SERVER
GO
CREATE TRIGGER DDLTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR DDL_SERVER_LEVEL_EVENTS, DDL_DATABASE_LEVEL_EVENTS
AS
     BEGIN
SET ANSI_PADDING ON;
         SET NOCOUNT ON;
         DECLARE @EventData XML= EVENTDATA();
         DECLARE @ip NVARCHAR(32)=
                 (
                 SELECT top (1) client_net_address
                 FROM sys.dm_exec_connections
                        WHERE session_id = @@SPID
                 );

         INSERT INTO master.dbo.AuditEvents
         (EventType,
          EventDDL,
          EventXML,
          DatabaseName,
          SchemaName,
          ObjectName,
          HostName,
          IPAddress,
          ProgramName,
          LoginName
         )
                SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(64)'),
                       @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
                       @EventData,
                       DB_NAME(),
                       @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
                       @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
                       HOST_NAME(),
                       @ip,
                       PROGRAM_NAME(),
                       ORIGINAL_LOGIN();
     END;
GO
USE [master];
GO
IF  EXISTS (SELECT 1 FROM sys.server_triggers WHERE name = 'AuditLogins')
DROP TRIGGER [AuditLogins] ON ALL SERVER
GO
CREATE TRIGGER AuditLogins
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
     BEGIN
         IF PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio'
            OR PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio - Query'
             BEGIN
                 DECLARE @EventData XML= EVENTDATA();
                 DECLARE @ip VARCHAR(32)=
                         (
                         SELECT TOP (1) client_net_address
                         FROM sys.dm_exec_connections
                                WHERE session_id = @@SPID
                         );

                 INSERT INTO master.dbo.AuditEvents
                 (EventType,
                  EventDDL,
                  EventXML,
                  DatabaseName,
                  SchemaName,
                  ObjectName,
                  HostName,
                  IPAddress,
                  ProgramName,
                  LoginName
                 )
                        SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
                               @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
                               @EventData,
                               DB_NAME(),
                               @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
                               @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
                               HOST_NAME(),
                               @ip,
                               PROGRAM_NAME(),
                               ORIGINAL_LOGIN();
             END;
     END;
GO
ENABLE TRIGGER AuditLogins ON ALL SERVER;
GO

USE [msdb]
GO
IF EXISTS (SELECT 1 FROM msdb..sysjobs WHERE name LIKE N'DBA_AuditEvents_Export_Cleanup')
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = N'DBA_AuditEvents_Export_Cleanup',  @delete_unused_schedule=1
END
GO

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'DBA_AuditEvents_Export_Cleanup')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_AuditEvents_Export_Cleanup',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'To clean up all audit events older than 1 year.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
/****** Object:  Step [Audit_Export]    Script Date: 10/20/2017 1:36:55 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Audit_Export',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'$Folder = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database master -Query "SELECT REPLACE(CONVERT(VARCHAR(MAX),SERVERPROPERTY(''ErrorLogFileName'')),''ERRORLOG'','''') + ''AuditEvents_'' +  CONVERT(varchar(10),GETDATE(),112) + ''.csv'' AS FileName,
REPLACE(CONVERT(VARCHAR(MAX),SERVERPROPERTY(''ErrorLogFileName'')),''ERRORLOG'','''') AS RemoveFile"

invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR))  -Database master -Query "

SELECT *
  FROM [master].[dbo].[AuditEvents] WITH (NOLOCK)
  WHERE EventDate > DATEADD(DD,-1,GETDATE())

" | export-csv -Path ($Folder.FileName)  -NoTypeInformation

$limit = (Get-Date).AddDays(-5)

# Delete files older than the $limit.
Get-ChildItem -Path $Folder.RemoveFile -Recurse -include *.csv -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force

',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Cleanup_AuditEvents]    Script Date: 10/20/2017 1:36:55 PM ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 2)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cleanup_AuditEvents',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DELETE master..AuditEvents
WHERE EventDate < DATEADD(DD,-5,GETDATE())',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily_11PM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20170615,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO



----ROLLBACK Audit Triggers and Job
--USE [master]
--GO
--DROP TRIGGER [AuditLogins] ON ALL SERVER
--GO
--USE [master]
--GO
--DROP TRIGGER [DDLTrigger] ON ALL SERVER
--GO
----DROP TABLE master..AuditEvents
--USE [msdb]
--GO
--EXEC msdb.dbo.sp_delete_job @job_name = N'DBA_AuditEvents_Export_Cleanup',  @delete_unused_schedule=1
--GO

----https://technet.microsoft.com/en-us/library/ms186456(v=sql.90).aspx

Enable All Users

https://mytechmantra.com/LearnSQLServer/How-to-Enable-Database-Users-in-SQL-Server/

I have converted it to do it for all databases.

sp_MSforeachdb 'USE [?]; SELECT ''USE [?]; GRANT CONNECT TO ['' + SU.name + ''];'' FROM [?].sys.database_principals DP
INNER JOIN [?].sys.sysusers SU ON dp.principal_id = SU.uid
WHERE DP.TYPE IN (''G'',''U'')
AND SU.hasdbaccess = 0
'

Find Old Backups on Backup Disk

--Below script will find all old backups on backup drive and you can delete those backup file by --copying DeleteFiles command


DECLARE @DefaultBackupDirectory VARCHAR(200);

EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;

select @DefaultBackupDirectory = SUBSTRING(@DefaultBackupDirectory,1,3)

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
      DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @DefaultBackupDirectory,0,1;


SELECT CONVERT( CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       b.database_name,
       b.backup_start_date,
       b.backup_finish_date,
       b.expiration_date,
       CASE b.type
           WHEN 'D'
           THEN 'Database'
           WHEN 'L'
           THEN 'Log'
       END AS backup_type,
       b.backup_size,
       a.logical_device_name,
       'exec xp_delete_file 0,''' +  a.physical_device_name + '''' DeleteFileCommand,
       b.name AS backupset_name,
       b.description
FROM msdb..backupmediafamily a
     INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
  INNER JOIN #DirectoryTree C ON C.subdirectory = REVERSE(LEFT(REVERSE(A.physical_device_name), CHARINDEX('\', REVERSE(A.physical_device_name)) - 1))

       WHERE(CONVERT(DATETIME, b.backup_start_date, 102) < GETDATE() - 2)
       ORDER BY b.backup_finish_date;