asked
Hard questions
We have file coming from FTP site and this file is zipped. Inside the zip file there is .bak file holding backup files . The name of the backups are constants except the date (example A01-01-2004 ,A02-01-2004..A(DD-MM-YYYY...). This back up file are going to be loading in the production server every day .when ever I restore the database the store procedure and views I created will be gone and I have to recreate them so is there any ways I can prevent that?like a SSIS schedule as a job ? Every time I do that manually there is going to be a down time so please I really need help The other thing is I was using FTP task to connect to the host server but again I want this task to connect first and then look the zip file with their date and execute them . I used for each loop but it didn't work for me . So again please I need help in figuring this out Basically all I need is to configure . FTP site -> connect -> look zip file-> unzip-> put it in local directory-> restore the bak file to the server Thank you so so much . It's because I am fresh dba never work with this area
Option 1: Create the views & SPs you need in the original database, as @Squirrel said in the comment. If you can't do that, for whatever reason, then: Option 2: Are these backups restored on a scheduled task? If so, make the next step of your task a step that runs your script to create the objects you need. This will minimise the time when these jobs aren't available. If your restore is being run through an SSIS project, then you can put the script into the SSIS workflow.
Just wondering about a trigger on the MSDB Restore History table too - check that to see if it's your database, then fire off a script / job / whatever to build your objects. Not sure if this would work, and may cause unexpected problems elsewhere if it does...
16 People are following this question.
