January 16, 2012 at 11:15 am
As part of backup plan for SSIS packages if the SSIS goes down, Would Master database backups be useful at all?
MSDB backups may be beneficial because they are deployed to msdb, but why would Master db backups be helpful?
January 16, 2012 at 11:36 am
They wouldn't be useful for backing up the SSIS packages, they're in MSDB or the file system.
Backups of master are essential for logins, server-level permissions, linked servers, user databases, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2012 at 6:05 am
invaliddba (1/16/2012)
As part of backup plan for SSIS packages if the SSIS goes down,
Just wanted to share how i had burnt my hands a while ago!
I've opted to deploy SSIS packages to SQL Server, hoping that i could restore MSDB to recover all the packages. That wasn't a smooth ride when i had to recover.
For me, the simplest approach is to deploy to the file system. Offcourse that has to be backed up too.
Sam Vanga
http://SamuelVanga.com
January 17, 2012 at 7:35 am
we also use file system. quick and easy to backup.
January 17, 2012 at 7:40 am
I like deploying to MSDB, but the packages are in source control (zipped, some systems mess them up if the store them as XML)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 3, 2014 at 5:27 am
Storing packages in the file system is usually better for development, primarily because you can use source control. (Hopefully your source control archive is part of a regular backup.) You can only store the package itself in a server, but you can store a complete project in a file system. A project might include documentation files, a group of related packages, or anything else that might aide the development effort. In my opinion, package development should always be done with a Visual Studio project stored in a file system.
😀
Is your package intended to execute on a server, or on a client? If it runs on a client the file system version is probably sufficient. If it runs on a server there may be good reasons for deploying the finished package to that server (stored in msdb). In a secure network environment, a remote server may not have access to the file system used by developers, and developers may not have rights to copy files over to the server. Developers can work with the file-based package without affecting the production copy. If a package is used for a critical scheduled job, there are more ways for it to fail if the server has to go over the network to another file system to find the package. It may be easier for the DBA to manage security issues if the package is on the server. 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply