Restore of a Suspect Database Failes

azarcrazarcr Posts: 5
edited July 30, 2008 2:31AM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • azarcrazarcr Posts: 5
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.