Nightly database restore

A customer had a third-party application (‘qlikviev’) that needed a database-backup downloading from the third-party’s ftp site every night and restoring over the ‘old’ one.

Already supplied was a vbs script that would do the downloading and produce a log-file.

Actually, I improved the vbs a bit to add leading zero’s to the date field’s if needed. Here’s the old then new lines …

‘ strFile = strDayOfMonth & strMonth & strYear & ” ” & strHour & strMinute

strFile = Right(“0” & strDayofMonth, 2) & Right(“0″ & strMonth, 2) & strYear & ” ” & strHour & strMinute

My task then was a) to read the log-file and b) if the download had been successful to restore the backup overwriting the ‘old’ database.

I looked at Bulk-Insert & SSIS, but they could only import the log-file not read it, before settling on the Operating-System command …

‘findstr “success” E:somefoldersomefile.txt’

… as job-step-1. I ensured that if the step failed (IE: the string was not found), the whole job would end.

Then in step-2 pasted some transact I’d bashed-out to run the restore …

RESTORE DATABASE adventureworks
FROM DISK=’E:somefolderadventureworks.bak’
WITH FILE=1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

BTW: To quickly create the right ‘restore’ command I went through the configuration-screens of the restore-wizard (not forgetting to set the ‘overwrite’ option) then hit ‘script’ to generate typo-free code.

Job step-3 was to grant db_reader access to a user. Here’s the code …

create user [domainQlikviewDevelopers] for login [domainQlikviewDevelopers]
exec sp_addrolemember ‘db_datareader’, ‘domainQlikviewDevelopers’

Finally, job-step-4 was to email the results …

declare @datetime as varchar(20)
declare @email as varchar(100)

set @datetime = convert(varchar(17),(select top 1 restore_date
from msdb.dbo.restorehistory
where (destination_database_name = ‘adventureworks’)
order by restore_date desc),113)

set @email = ‘Database ”Adventureworks” Refreshed ”’+@datetime+”’.’

exec msdb.dbo.sp_send_dbmail
@recipients = ‘someone@somewhere.co.uk’,
@subject = @email ;

As the vbs script was to run every 2am I created a sql-job to execute every 3am.