According to best practices for a SQL environment, you should never place your test or development databases on a production server. However sometimes best practices are ignored, sometimes the DBA’s advice is disregarded (ahem…), or sometimes an organization simply does not have the financial resources to spin up separate servers for testing or development.
Whatever the reason, if your production server happens to have test or development databases on it, you need to be make sure these databases are managed properly. Usually those databases get periodically restored with a copy of the production database, and you need to ensure there is enough room on the disk to accommodate all data and log files. Typically the production database will be in Full recovery mode possibly with a large transaction log file to accommodate daily transactions. The test or development database normally does not need the large transaction log file and usually does not need to be in Full recovery mode.
Instead of manually checking each database to see when it might have been restored, create a SQL Agent job that will notify you when this happens so that you can go in proactively and manage the environment.
The following statement can be inserted in the SQL Agent Job step, and will provide a basic notification in the event your databases have been restored. Create the job to execute however often you need for your environment… (I have mine running once per week and have set the parameter for [restore_date] to check the last 7 days of activity).
–create a temp table to hold your data
create table #Restores
( [destination_database_name] nvarchar(50),[restore_date] date, [restore_type] nvarchar(20), [user_name] nvarchar(50)
–insert data into your temp table from msdb.dbo.restorehistory table
;with LastRestores As
( select [destination_database_name],[restore_date],
WHEN ‘D’ THEN ‘Database’
WHEN ‘F’ THEN ‘File’
WHEN ‘G’ THEN ‘Filegroup’
WHEN ‘I’ THEN ‘Diff’
WHEN ‘L’ THEN ‘Log’
WHEN ‘V’ THEN ‘VerifyOnly’
END AS restore_type,[user_name]
where ([destination_database_name] like ‘%dev’ OR [destination_database_name] like ‘%test’)
AND [restore_date] >= (GETDATE() – 7) –change to timeframe appropriate to your environment
insert into #Restores
select [destination_database_name], [restore_date], [restore_type], [user_name]
–if temp table returns any rows, send notification email
if (select count(*) from #Restores) > 0
@profile_name = ‘DBMailProfile’,
@recipients = ‘SomeoneImportant@yourompany.com’,
@subject = ‘Test Database Restored’,
@body = ‘A test or training database has been restored on InstanceName. Please make necessary adjustments.’
–drop your temp table
if (OBJECT_ID(‘tempdb..#Restores’) is not null)
drop table #Restores
This is a simplistic statement to notify you of restore activity for databases you specify. You can get much more sophisticated if necessary by sending the query output as a file or html, or by triggering an immediate notification whenever the database is restored.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!