I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.

I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?

• Edited by omatase Friday, April 24, 2015 9:13 PM
• Moved by Kalman TothModerator Friday, April 24, 2015 9:26 PM Not database design

Yes Database snapshot. We're using it to make our data more available so that when we're refreshing the underlying database we can still access the previous version of the data. Otherwise our reports and applications won't have access to the data or will get partial data sets. We don't intend to use it for DR or as a backup.

• Edited by omatase Friday, April 24, 2015 9:45 PM

Awesome, OK thanks man!