On occasion we have to restore a test DB to do testing of our ERP. It’s not hard but you have to do the clicking. I created an automagic .bat file to do this.

It starts off looking for a “go.txt” file, if it finds it then on to the fun

IF NOT EXIST C:\Auto_Restore\StartRestore\go.txt GOTO LabelExit

REM delete previous backups

ECHO Y | DEL C:\BackupRestore\*.*bak

REM copy the newest .bak file over

set folder1=\\Backups\SQL-Backups\ERP\FULL
set folder2=C:\BackupRestore
for /f “tokens=*” %%a in (‘dir /b /a-d /o-d “%folder1%\*.bak”‘) do copy “%folder1%\%%~a” “%folder2%” & goto next
:next

REM check there is only file

set cnt=0
for %%A in (C:\BackupRestore\*) do set /a cnt+=1

IF NOT %cnt% ==1 goto LabelExit

for %%A in (C:\BackupRestore\*.bak) do Nuke_Test.bat & Restore_Test.bat

:LabelExit
exit /b

—-     Nuke_Test.bat    —-

sqlcmd -S SERVERNAME -U user -P Password -i C:\Auto_Restore\Nuke_Test.sql

—-     Nuke_Test.sql     —–

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’Test’
GO
USE [master]
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [Test] ******/
DROP DATABASE [Test]
GO

—–     Restore_Test.bat     —–

SqlCmd -S TEST -U User -P thepassword -Q “RESTORE DATABASE Test FROM DISK=’%1′”

—

my compadre DBA has a much more SQLY restore script here —-> https://michaelellerbeck.com/2015/01/23/stored-proc-for-auto-restoring-a-database/