May 7, 2008 at 10:34 am
All,
Im pretty confused on this one.I have developed and deployed 10 packages on my Report Manager.Currently these packages are saved on a share drive in our company and are saved in the MSDB folder in SSIS Integration Services in Mngt Studio.Thses packages are also scheduled as jobs on the server itself.Our db is being backed up daily as well.
My question:
Is there any other way for me to backup the packages in the SSIS Integration Services?
Read on msdn, it states that the MSDB db stores SSIS and DTS packages.Does that mean in case my server goes down and I restore my database which includes the MSDB db.This means that when I connect to the SSIS Integration Services after the restore I would be able to see my saved packages on the server as before the restore. :exclamation:
Anyone out there who did a db restore and noticed if the saved packages where made available in the SSIS Integration Services after the restore.
What are the other possible solutions to ensure my packages are safe?
Is doing a FULL db backup, include saving SSIS packages on SSIS Integration Services.
Another Question: What about the reports that are made available in the Reporting Services,the subscription of these reports,the Roles that I have created and the scheduling being done for the reports.How can I backup all of these?How to ensure after a db restore all these would be made available in the Reporting Services as well?
Help me....Im extremely confused :unsure:
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
May 7, 2008 at 10:42 am
I have never had to restore the msdb so I can't say for sure if the packages would be restored as well. Seems logical to assume they would.
However, it does depend upon where you store the packages in the first place. You are not required to store them in the database, they can be run from the file system. If your packages are run from the file system, it stands to reason restoring msdb will NOT restore your packages.
The greatest peace of mind for me is having them on my PC and import them into msdb when I have them completed or make changes to them. My PC is backed up so, if I lose the SQL Server and restoring msdb does not restore my packages, I still have another copy I can simply re-import.
May 7, 2008 at 11:39 am
Hi Jim,
My 1st guess would be finding out where my saved packages are being runned from? Is it FileSystem or MSDB ..Correct me if Im wrong and then deciding the required steps.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
May 7, 2008 at 11:58 am
If you are running them from within SQL Agent jobs, you can look at the steps to see where they are being run from. If they are being run from the database, you can connect to SSIS on that server and export the package to a .dtsx file.
May 7, 2008 at 12:00 pm
Right. Backup the location where the packages are run from. It should be obvious where they are when you look at the step in the SQL Server job.
If the package source is SQL Server, then the packages are in msdb and a database backup will preserve them and you can get them back with a restore of msdb.
If the package source in File System, then the packages are stored at the locaton specified in the job step. You should backup the folder or share where they're stored.
Greg
May 7, 2008 at 12:07 pm
Thanks a million guys!
I have a clear understanding now. I will definately give it a try....
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
May 30, 2011 at 1:59 pm
it so nice to see so many people giving solution for the issue,
i also have a solution which might not be a great one but i think in particular scenario it will work better.
what if i just want to back up ine ssis package out of 20 running on my SSIS server.
if i backup and restore MSDB that will give me all the whole ssis package ( i have never tried , if when you restore MSDB the ssis packages will be restored on SSIS server too).
i think one way to do it..
lets say we r deploying a modified version of the ssis from dev to test to production but jus in case i also want to backup my ssis package in production if we have to revert back to the old one; because the modified ssis didn't give us desired results.
i think in that case,
before deploying the ssis package from Test to prod. i will go to ssis server -> open MSDB-> go to the package -> right click and hit export .
save it at the location n the drive or network folder , go to the config file ( in my case the xml file) and copy and save it to a network location too.
now if after the deployment if the business tells us to revert back to old i will just delete the new dtsx and config ...deploy the old dtsx and config file and walla i have the old ssis package back....
feel free to comment if some guy there is a better way .
May 30, 2011 at 2:11 pm
restoring msdb will recover your SSIS packages, if you have saved them to SQLServer. It will also recover the SQLAgent jobs that run them as well.
To backup your RS configuration back up the Reportserver and reportservertempdb databases. Restoring those databases will recover your configuration (and your reports). Make damn sure you backup your RS encryption key as well.
It is good practice to keep a copy of your SSIS packages and RDLs in some form of versioning tool so you can recover\back out individual packages.
---------------------------------------------------------------------
May 27, 2013 at 7:08 am
I know this is a fairly old thread however it contains most of the information I need. One thing I'm not clear on is, do I need the whole DTS folder for the File System Packages or just the packages directory? I'm really just curious if there is anything else in DTS that will be required or connected to existing DTS jobs. Any help is greatly appreciated. If I should create a new thread please advise.
Thanks as always!
Steve
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply