SQL Backup 9 - Restore dropping Security
McComb
Posts: 4 New member
We operate a 3 tier environment in my
organisation. Weekly I have scheduled full backups of all production databases weekly
on Friday at 02:00. A scheduled “copy files” task runs weekly on Friday at
04:00, on each production server, to copy the .SQB files from each production
server to the parallel user acceptance (UA) server. Scheduled restore (with
DCCC) jobs run at one minute intervals starting at 07:00 to restore each
production database backup over the UA database (in alphabetical order). This
process gives us a UA environment which is almost identical to production, for safe
“real” testing and also proves that the production backups are good and true.
This has been working faultlessly for over eighteen months. Two weeks ago a developer informed me that an application pointing to a UA database wasn’t functioning. I found that all security users in this database had vanished. I manually started the restore job (without repeating the other steps) and when the restore job finished the database users had reappeared. I manually started all the restore jobs and no more complaints were received. This fault is happening to some but not all UA databases, some UA database have a correct list of database users, with correct permissions.
On Friday, last week, a similar situation occurred, but this time only some of the database users had vanished. A scheduled restore, but to a new parallel database showed all database users present.
NO errors were reported on any restore. I am at a loss to find out how to fix this and I am starting to get pressure from senior management to fix this as both the UA environment isn’t “correct” and the production backups are not being proved to be good and true.
Am I alone or are you suffering too?
