Restore of a Suspect Database Failes

azarcr

I am rebuilding a server from our production environment and have done the following:

1. Restored Master.
2. Restored Model and MSDB.

All of the databases then come up suspect but when I try to restore any of them I get the following message, here is the log excerpt. One thing of note is that when I simply delete the suspect database and restore the same backup file and specify create a new database it works fine.

SQL Backup log file 5.3.0.178

-SQL "RESTORE DATABASE [Janna] FROM DISK = 'G:\dba\backups\FULL_(local)_Janna_20080727_221017.sqb' WITH RECOVERY, MOVE 'janna_Data' TO 'G:\dba\backups\Janna_Data.MDF', MOVE 'janna_Data_5' TO 'G:\dba\backups\Janna_1.NDF', MOVE 'janna_Data_2' TO 'G:\dba\backups\Janna_Data_2.MDF', MOVE 'janna_Data_3' TO 'G:\dba\backups\Janna_Data_3.MDF', MOVE 'janna_Data_4' TO 'G:\dba\backups\Janna_Data_4.MDF', MOVE 'janna_Data_6' TO 'G:\dba\backups\Janna_2.NDF', REPLACE "

---

ERRORS AND WARNINGS

---

7/28/2008 1:32:19 PM: Restoring Janna (database) from:
7/28/2008 1:32:19 PM: G:\dba\backups\FULL_(local)_Janna_20080727_221017.sqb

7/28/2008 1:32:19 PM: Error 890: RESTORE DATABASE permission denied for database: (Janna)
7/28/2008 1:32:19 PM: SQL error 945: Database 'Janna' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

petey

When you restored the master database, it contained details of the other databases that were attached to that SQL Server instance at the time of the backup. These databases are obviously not available, and are probably marked as suspect.

When SQL Backup attempts to restore the database, it runs a script to check if the user running the restore has the necessary rights. Part of the script may need to connect to the database in question to check certain rights. Those portions are triggering the error you are experiencing.

To work around this, log in as a user that has the SQL Server sysadmin fixed server role, or as 'sa', to perform the restore. Or like you already did, just drop the database from the instance's list, and perform the restore.

azarcr

I was logged in using NT authentication with a user who is in the sysadmin role. The funny thing is for one of the smaller databases I took a native sql backup and restored over the suspect database without any issues so it appears to be RedGate specific issue.

petey

Could you please run Profiler and trace the restore scripts that run when you use SQL Backup? There should be two points at which it checks if the logged-on user is a sysadmin, something similar to this:

SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'YOHZ_54\Peter Yeoh')

and

SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin')

Is the logged-on user correctly identified? If you were to log on to the server using the same account as the SQL Backup Agent service startup user, and run the above 2 portions of the script, does the function return true?