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
STATS = 10
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
where (destination_database_name = ‘adventureworks’)
order by restore_date desc),113)
set @email = ‘Database ”Adventureworks” Refreshed ”’+@datetime+”’.’
@recipients = ‘email@example.com’,
@subject = @email ;
As the vbs script was to run every 2am I created a sql-job to execute every 3am.