April 13, 2016 at 9:31 am
Hello experts,
I manage a set of SQL Servers to cover the basic environments production, staging, and development. I am working on a project to improve staging by enabling some databases to be restored automatically by restoring backups of the production databases.
Currently, the staging SQL Server keeps one day of backups for the system and user databases - but just today I wondered, is that necessary? I can understand keeping backups of the staging system databases, but if I am proposing using staging to restore production backups (in order to (1) help test that those backups work and (2) to keep those staging databases as close as possible to production), do I need to back up those user databases on staging?
I realize this question probably touches on the whole giant issue of how best to set up production, staging, and development for a given organization as well as the goal of automated database deployment promoted by Red Gate. But for now I am curious if I will need to continue backing up user databases on staging.
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 13, 2016 at 9:39 am
There is no one answer to that question but "it depends". Does your app team need to be able to recover its testing to a point in time? You need backups. Will there never be any restores of the test databases? Then maybe not. It's up to your individual team to decide what it needs.
April 13, 2016 at 9:42 am
jeff.mason (4/13/2016)
There is no one answer to that question but "it depends". Does your app team need to be able to recover its testing to a point in time? You need backups. Will there never be any restores of the test databases? Then maybe not. It's up to your individual team to decide what it needs.
OK, thanks. I was mulling this over as a possible way to save space or avoid wasting space, but as you suggest I will take it up with the team. They do most of their work on dev but occasionally use staging, which I know is a bleeding over that should not happen, but this is the real, imperfect world we live in. 🙂
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 13, 2016 at 2:53 pm
I agree with Jeff, it depends.
However, let's say Staging is really Staging, a place to validate deployments prior to production. Then no. I wouldn't have backups at all.
If they're developing in multiple locations... Stop them. To heck with automating the deployments, just managing them and getting them right means you should be developing through a single source (source control being the best).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2016 at 10:59 am
I've been required to backup dev databases before now, it depends on requirements
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 14, 2016 at 11:20 am
Based on my experience, staging could be the +1 version of production, but it could also be the place where they work on bugs that only show up with specific data. For the latter, my users often ask me to bring over a few tables so they can work on the problem away from the current development work.
April 14, 2016 at 12:08 pm
lptech (4/14/2016)
Based on my experience, staging could be the +1 version of production, but it could also be the place where they work on bugs that only show up with specific data. For the latter, my users often ask me to bring over a few tables so they can work on the problem away from the current development work.
Yes, thanks. While mulling over these questions I realized I have to decide whether what my group needs is (a) production, staging, and dev or (b) production and two levels of dev. (b) strikes me as kind of odd but it seems to be the way the developers work.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 14, 2016 at 12:16 pm
Grant Fritchey (4/13/2016)
I agree with Jeff, it depends.However, let's say Staging is really Staging, a place to validate deployments prior to production. Then no. I wouldn't have backups at all.
If they're developing in multiple locations... Stop them. To heck with automating the deployments, just managing them and getting them right means you should be developing through a single source (source control being the best).
Well, there are also other reasons to back up a non prod area. They could be doing lengthy processes that need to be able to start in the middle if something goes wrong and they want a recovery point. The QA team could be using specific cases that they want to have reference to later on that week. It seems counter intuitive, I know, but at my last job developers did in fact want to recover to points in time in their development processes, and those processes could be on dev, test, QA, or stage.
If I were in charge, no non-prod user DBs would be backed up ever. However, since when do DBAs have the ability to make those kinds of decisions......? :rolleyes:
April 14, 2016 at 12:22 pm
jeff.mason (4/14/2016)
Grant Fritchey (4/13/2016)
I agree with Jeff, it depends.However, let's say Staging is really Staging, a place to validate deployments prior to production. Then no. I wouldn't have backups at all.
If they're developing in multiple locations... Stop them. To heck with automating the deployments, just managing them and getting them right means you should be developing through a single source (source control being the best).
Well, there are also other reasons to back up a non prod area. They could be doing lengthy processes that need to be able to start in the middle if something goes wrong and they want a recovery point. The QA team could be using specific cases that they want to have reference to later on that week. It seems counter intuitive, I know, but at my last job developers did in fact want to recover to points in time in their development processes, and those processes could be on dev, test, QA, or stage.
If I were in charge, no non-prod user DBs would be backed up ever. However, since when do DBAs have the ability to make those kinds of decisions......? :rolleyes:
Thanks!
Yes, I still will keep dev backups. The only reason I wondered about staging was because if my goal is to keep refreshing staging to keep it close to production, would I need to back up those databases that are getting overwritten anyway.
But if I had the space I would be in favor of the usual backups even on staging, so now I am re-thinking the overall plan, including what Grant mentioned about the database deployment process bring drive by source control. I am using source control just not for deployment yet.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply