Database Refresh

What is a database Refresh? 
The term database refresh means different to different databases technologies and it is often misused with the term database Cloning. Database refresh in Oracle may not mean the same as in SQL Server. This blog is based on SQL Server Technology and do not mix it with other databases. Refreshing a database is a process of overwriting an existing database from your production or stage database or vice versa.  In a simple term, it is a process of restoring a database to your stage or development environment from a production db backup. Refreshing and Cloning are not the same for SQL Server but it may be for other databases.  Usually, a Database Administrator perform database refresh on demand or automates it by scheduling a job.

When do you perform Database Refresh? 

1. To update a Stage or Development db with latest data.
2. To move a brand new database from Stage to Prod.
3. To sync Prod & Stage database
4. To test application with prod data
5. To verify the backup copy.

How do you perform Database Refresh?  
There is a request from our developer team to refresh employee phonebook database against production SQL as they would like to test a new feature that was recently added on development. We have two SQL Server Instances Prod SQL and QA SQL. We are going to make a backup of phonebook database from Prod SQL and store that on our shared network drive. Shared network is a drive which is accessible to all the SQL Instances in our network. This shared drive saves us from implementing an additional step of moving file from one drive to another. If you don’t have shared drive,  file transfer protocol (FTP) is a way to go. We will then restore the backup to QA SQL Instance, verify it and clean up the backup from shared drive.

Also make a note, backup that are created by more recent version of SQL server cannot be restored in earlier version of SQL Server. It is also important to ensure that you have enough space to restore and store the backup dump. For our  refresh demo, we  have verified our limitations. We are on same version of SQL Server and have enough disk space to store and restore the backup database.
Let's begin our demo!!

Prod SQL Instance:

1. Make a full backup of phonebook database.
2. Store the backup on shared network drive
3. Verify if the backup is restoreable without restoring it
4. Use Compression option if available (small file size & faster restore)

------------------------------------------- 
---Database Backup & Verify Restore --- 
-------------------------------------------- 
USE [master] 
BACKUP DATABASE [test_db] TO DISK = 
N'\\Shared_S\database_refresh\phonepbook\Backup\phonebook_backup.trn'
WITH noformat, init, NAME = N'test_db-Full Database Backup', skip, norewind, 
nounload, stats = 10 

go 

DECLARE @backupSetId AS INT 
SELECT @backupSetId = position 
FROM   msdb..backupset 
WHERE  database_name = N'phonebook' 
       AND backup_set_id = (SELECT Max(backup_set_id) 
                            FROM   msdb..backupset 
                            WHERE  database_name = N'phonebook') 

IF @backupSetId IS NULL 
  BEGIN 
      RAISERROR( 
      N'Verify failed. Backup information for database ''phonebook'' not found.', 
      16, 
      1) 
  END 

RESTORE verifyonly FROM DISK = 
N'\\Shared_S\database_refresh\phonepbook\Backup\phonebook_backup.trn'
WITH FILE = @backupSetId, nounload, norewind 

QA SQL Instance:

1. Kill any phonebook db connections
2. Make a backup of phonebook db from QA SQL 
3. Restore phonebook db from Prod SQL backup
4. Verify the restored database 
5. Clean up backup file from network drive

---------------------------- 
--- Kill connections, backup database and restore database--- 
---------------------------- 
USE [master] 
DECLARE @sql  AS VARCHAR(20), 
        @spid AS INT 
SELECT @spid = Min(spid) 
FROM   master..sysprocesses 
WHERE  dbid = Db_id('phonebook') 
       AND spid != @@spid 

WHILE ( @spid IS NOT NULL ) 
  BEGIN 
      PRINT 'Killing process ' 
            + Cast(@spid AS VARCHAR) + ' ...' 
      SET @sql = 'kill ' + Cast(@spid AS VARCHAR) 
      EXEC (@sql) 
      SELECT @spid = Min(spid) 
      FROM   master..sysprocesses 
      WHERE  dbid = Db_id('test_db') 
             AND spid != @@spid 
  END 

PRINT 'Process completed...' 

BACKUP log [test_db] TO DISK = 
N'd:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\phonebook_db_LogBackup_2015-01-12_13-59-56.bak' 
WITH noformat, noinit, NAME = N'phonebook_db_LogBackup_2015-01-12_13-59-56', noskip, 
norewind, nounload, norecovery, stats = 5 
RESTORE DATABASE [test_db] FROM DISK = 
N'\\Shared_S\database_refresh\phonepbook\Backup\phonebook_backup.trn'
WITH FILE = 1, nounload, replace, stats = 5 
go 

How do you verify database refresh?
After any task delivery, it is crucial that you test to ensure the changes you implement is working as requested. Similarly, your refresh in not complete without verifying the changes. I would make sure, the refreshed database is online, accepts new connections, and has same number of db objects as that in productions or whatever the source Instance was used. I would run the below query both in source and in destination SQL instance and compare the objects count. The objects count should be the same for both if the refresh was successful.

SELECT CASE type 
         WHEN 'U' THEN 'User Defined Tables' 
         WHEN 'S' THEN 'System Tables' 
         WHEN 'IT' THEN 'Internal Tables' 
         WHEN 'P' THEN 'Stored Procedures' 
         WHEN 'PC' THEN 'CLR Stored Procedures' 
         WHEN 'X' THEN 'Extended Stored Procedures' 
       END, 
       Count(*) 
FROM   sys.objects 
WHERE  type IN ( 'U', 'P', 'PC', 'S', 
                 'IT', 'X' ) 
GROUP  BY type 

Also, randomly pick some tables from a recently refreshed database and do the record count for both source and destination SQL Instances. The record count should match. If the record don't match, you know what you need to do next. You need to paranoid while verifying the refresh, another popular way is to compare the size of data file and log file on both the Instances.

SELECT Db_name(database_id) AS DatabaseName, 
       NAME                 AS Logical_Name, 
       physical_name, 
       ( size * 8 ) / 1024  SizeMB 
FROM   sys.master_files 
WHERE  Db_name(database_id) = 'Your-DB' 

As a DBA, you have done everything you can to verify the refresh. If everything looks good on your end, have your requester verify the refreshed database. The user of data knows their data well then anyone else in the company.  If everything looks good, you can automate it by scheduling a job or save your script and run on demand.

Database Refresh Best Practices. 
Do you know of any best practices around refreshing? I always make sure to take a good backup, verify the backup and have enough space on destination sql instance before you begin the database refresh. If you have any that your organization or you follow, please share with everyone. dbarepublic.com believes sharing is caring to the people of dba republic!!