I recently had to set up a nightly copy of a set of databases, I had looked around for other solutions but I didn’t see one quite what I wanted.   I also ran into an issue with powershell when I moved my script to SQL 2012, and thought it was good to share.

Environment:

• Source Host: Win 2008 R2, SQL 2008 R2
  • Backup directory where this backup resides is shared, with access allowed for the proxy user on the target host
• Target Host: Win 2008 R2, SQL 2012
  • My target host has a proxy user setup for executing the powershell script, with privileges to access to remove backup drive.
  • To create a proxy account in SQL (2008 & 2012)
    1. Create Credential
       1. Security –> Credentials –> New Credential
          1. Credential Name: PSAdminCred
          2. Identity: <Domain>\pssqladmin account
          3. Enter & confirm password
    2. Create new Proxy SQLAdminPowerShell
       1. SQL Server Agent –> Proxies –> New Proxy
          1. General Tab
             1. Proxy Name: SQLAdminPowerShell
             2. CredentialName: PSAdminCred
             3. Active to the Following Subsystems: PowerShell
          2. Principals Tab
             1. Add <domain>\pssqladmin (not needed if already in Sysadmins)
• These servers are located in the same domain.  If this was not the case, a username/password could be defined to access the backup on the source host.

Setup:

My source server runs a nightly backup, so I opted to use this backup.

The job is setup to run an hour after the the nightly backup completes, in the future I can look to add logic to check that the prior night’s backup had completed.

Create a SQL Server Agent Job:

1. Step 1: Copy Prior Night Backup
   1. Type: PowerShell
   2. Run As: PSAdminCred
   3. Command:

   4. $path = "\\<SourceHost>\Backups\AdventureWorksDW2008R2"
      $destPath = "\\<TargetHost>\Refresh"
       
      $file = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$path\*.bak | sort CreationTime -Descending | select -First 1
      Copy-Item Microsoft.PowerShell.Core\FileSystem::$file -Destination Microsoft.PowerShell.Core\FileSystem::$destPath\AdventureWorksDW2008R2_refresh.bak -force

   • Note – With SQL 2012 powershell, you must append the “Microsoft.PowerShell.Core\FileSystem::” piece for the share access.  This was not needed with SQL 2008
2. Step 2: Run the database restore:
   1. Type: T-SQL
   2. Database: Master
   3. Command:

      1. ALTER DATABASE [AdventureWorksDW2008R2CPY] 
         SET SINGLE_USER WITH
         ROLLBACK AFTER 60
         GO
         
         RESTORE DATABASE [AdventureWorksDW2008R2CPY] 
         FROM DISK = N'K:\refresh\AWDW_refresh.bak' 
         WITH FILE = 1, 
         MOVE N'AdventureWorksDW2008R2_Data' TO N'D:\Data\AdventureWorksDW2008R2CPY.mdf', 
         MOVE N'AdventureWorksDW2008R2_Log' TO N'L:\Data\AdventureWorksDW2008R2CPY_1.LDF', 
         NOUNLOAD, REPLACE, STATS = 10
         GO
         
         ALTER DATABASE [AdventureWorksDW2008R2CPY] 
         SET MULTI_USER
         GO
         
         ALTER DATABASE [AdventureWorksDW2008R2CPY] SET RECOVERY SIMPLE
         GO

      • Note: I’m altering the database to single user to force off existing connections, then once the restore has been completed, I’m setting back to multi user.  Otherwise, your restores will fail as it cannot get a connection in case someone left their connection open.
      • Note: I’m setting the database recovery to Simple as this is just a nightly copy, and we don’t need the point in time recovery.
3. Step 3 – Consistency check, Resync Users
   1. Type: T-SQL
   2. Database: AdventureWorksDW2008R2CPY

   3. dbcc checkdb

   4. sp_change_users_login 'update_one', 'ssisreader', 'ssisreader'
      GO
      
      ALTER ROLE [db_owner] ADD MEMBER [<domain>\<user>]
      GO

      • Notes: The  “sp_change_users_login ‘update_one'” command gets your database logins back in sync after a database is restored.  With the Windows authenticated accounts(<domain>\<user>), I’m running the grants as I’m allowing for a different permission than before.
4. Notifications
   1. On the Advanced tab of each step, I have on success as go to the next step (except for the last step, where it would quit the job reporting success), or on failure, quit the job reporting failure.
   2. On the Notifications tab, I have an e-mail set to my database operator in case of failure.  Database mail needs to be configured in order for this to be run.