Restoring differential backups and permissions
ChrisAVWood
Posts: 361
Hi,
We are trying to use differential backups as a way of reducing the time and disk space requirements on databases over 100Gb.
We use T-SQL scripts to perform backups and restores for security reasons. I was testing a situation where an SQL login that was a db_owner of a database with its default DB different had taken a differential backup then tried to restore the differential after restoring the last full backup. I had the full restore with NORECOVERY so I could then restore the differential. In this situation the full restore happened but the differential failed as it threw an 890 error Restore permission denied. I then tried the same idea but with the full restore having RECOVERY rather than NORECOVERY and now I get a 3013 error followed by a 3117 error saying that the differential cannot be restored because there are no files to rollforward.
Now if I try this with sysadmin authority it works. This is using 5.4. I haven't tried this with 6.2 yet.
If I understand this correctly after the full restore the database is in recovery mode and so the account running the script now needs to be a sysadmin rather than a db_owner and for a differential restore to happen the DB has to be in recovery mode?
Can I get a confirmation of permissions required to run a differential restore please?
Thanks
Chris
We are trying to use differential backups as a way of reducing the time and disk space requirements on databases over 100Gb.
We use T-SQL scripts to perform backups and restores for security reasons. I was testing a situation where an SQL login that was a db_owner of a database with its default DB different had taken a differential backup then tried to restore the differential after restoring the last full backup. I had the full restore with NORECOVERY so I could then restore the differential. In this situation the full restore happened but the differential failed as it threw an 890 error Restore permission denied. I then tried the same idea but with the full restore having RECOVERY rather than NORECOVERY and now I get a 3013 error followed by a 3117 error saying that the differential cannot be restored because there are no files to rollforward.
Now if I try this with sysadmin authority it works. This is using 5.4. I haven't tried this with 6.2 yet.
If I understand this correctly after the full restore the database is in recovery mode and so the account running the script now needs to be a sysadmin rather than a db_owner and for a differential restore to happen the DB has to be in recovery mode?
Can I get a confirmation of permissions required to run a differential restore please?
Thanks
Chris
English DBA living in CANADA
Comments
It is SQL Server which controls these underlying permissions so they are unlikely to have changed in v6. I have just had a look in BOL and have not found anything conclusive. If you grant a login the dbcreator role then they can
This might be sufficient in the scenario you are looking at. I am not 100% sure but the db_owner not working might also be due to orphaned users (See http://support.microsoft.com/kb/274188 and the database being offline.
I will ask a few people around the office if they know the answer to your permissions issue.
The second issue you describe is due to bringing a database online before applying the diffierential. In this case there are partially complete transactions which may have been rolled back etc and once a database is online other changes might have occured so SQL Server will not apply any more backups.
Head of DBA Tools
Red Gate Software Ltd
I am going to retest with the full and NORECOVERY. If I get the permissions error I will try making my SQL login a db_creator and try again.
Chris
Making the SQL login a dbcreator fixes the intermediate problem of restoring the full backup but still being in recovery mode and then applying the differential.
Thanks for your insight. Now to get permission to make this change to our environment.
Chris