Restoring differential backups and permissions
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?