Jay Harris is Cpt. LoadTest

With all that we stuff into the database on the QA environment, we need to perform a regular database restore. This way, we also get a fresh DB without any of the corruption from the previous day’s QA attacks.

I created a NAnt script to automate the process, including restoring security access when we restore from a backup created on a different machine. Centerting around the NAnt code below, my script disconnects all current connections to the database in question (we can not restore the DB without dropping it, and we can not drop it while connections are open), drops and restores the database, refreshes security, and performs a few other tasks such as setting all email addresses to internal addresses to prevent spamming the client and truncating the log since our server is a little short on disk space.

if exists (Select * from master.dbo.sysdatabases where name = '${database}')
Begin
    DROP DATABASE [${database}]
End
 
RESTORE DATABASE [${database}]
    FROM DISK = N'${backupfile}'
    WITH FILE = 1,
    NOUNLOAD ,
    STATS = 10,
    RECOVERY,
    – changes file locations from what was in the backup
    MOVE '${dataname}' TO '${path::combine(datadirectory,database+'.mdf')}',
    MOVE '${logname}' TO '${path::combine(logdirectory,database+'_Log.ldf')}'