August 30, 2013 at 12:25 pm
I think in most cases, it probably deserves to be up a level.
In many environments, you only promote objects to production.
So designing a fool proof process to avoid overwriting Prod with Dev is what should be the main discussion.
As long as the same person has sysadmin in both environments, there is potential to repeat.
Things like color can help, but will not stop fast fingers.
I like Jeff's approach, as you would have to copy the backup to another area to do it.
Similar to ours, where the deveolopers did not have access to those backups.
So you worked with someone else to get the restore done.
August 30, 2013 at 2:09 pm
SQLRNNR (8/28/2013)
Here's another vote for multiple logins.In addition, another vote for taking greater care and caution when attempting to do a restore.
The server trigger may work, but I agree with Brian that it shouldn't be relied upon.
I checked throughout PBM as well and there is no means there to prevent the restore either (not without preventing the user to login).
You don't have to just prevent the development dba login, you must also forbid the production dba login access to the development backups, and forbid that access to the production SQL Service account and the production SQL Agent account, and also deny the development dba login write access to the production backups. Otherwise it's fairly easy to do the wrong thing.
Tom
August 30, 2013 at 2:15 pm
Brandie Tarvin (8/29/2013)
K. Brian Kelley (8/29/2013)
With the different accounts in different environments, they are all admin accounts. Just one works for dev and one works for prod and dev never has the ability to work in prod.Unless the same person is doing both and happens to have different SSMS windows open. In which case, it doesn't matter what that person does to prevent these things from happening in the future, they still will happen unless that person double-checks and pays attention.
If the two logins are different NT logins (rather than SQL logins) two windows simultaneously doesn't matter provided the production NT login doesn't have any access to the development backup filestore and the development db login doesn't have write access to the production db backup filestore; it's a good idea to deny the production service and agent accounts access to development filestore too. You are of course right tat splitting the two functions using SQL Server logins rather than NT logins is not good protection against this sort of foul-up.
Tom
August 30, 2013 at 2:33 pm
I wasn't speaking of SQL Server logins. If you go back up the thread, I was talking two Windows logins in different domains (in different forests) where there is no trust in the production domain of the non-production domain credentials.
K. Brian Kelley
@kbriankelley
August 30, 2013 at 10:34 pm
Hi All,
The situation we faced is like, one request came to restore Prod backup to Dev server with last available backup, so we have checked the last backup file history through query analyzer on prod and we have have connected the Dev through SSMS on prod, while we have to run restore on dev env. Unfortunately we have selected a new query and it was opened from prod environment and we have stared the refresh on it, so we realized after we stared. in the mean time we have contacted the application for the same and updated with the situation, but if any data might have written to the database on prod, it will be difficult to find out, we have checked the SQL server default trace to find out the details for any object got created/dropped. is there any query to find out what might have changed/inserted/updated/truncated on production during these time and apart from color coding any other suggestion on the same.
i really thank all of the member who replied on the context, i like to know more on it, please do contribute your suggestion.
September 3, 2013 at 7:48 am
So you restored an older Prod backup over the current version of Prod?
This means none of the multiple login suggestions that were made will work. You can't prevent a sysadmin from restoring over a database with its own backup.
However, if you want to restore databases from production to Dev, do what we do in our environment. Build a job on the Dev database that pulls from the Production backup repository and restores. Then you have something that you don't have to rewrite each time the request is made and you don't have to worry about switching environments in SSMS because the job will only run against Dev. (Make sure there are no Linked Servers that could confuse the issue).
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply