question

fantumahi2 avatar image
fantumahi2 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
ssisbackup-restorescript-task
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

why not create the views & stored procedure in the original database so that when it is backup, the views & sp will be in there
1 Like 1 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.