Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databasesI recently built a triple redundant DB where there are two warm standbys not e this is SQL 2000 Standard. On each standby i have a program that reads a directory looking for new TLOG backup files that get pushed to it by robocop y from the active node. There are to user db's that are subject to this. It just so happens that my log restore program kicked off and restored both databases at the same time. As such the msdb's restorehistory table contain s two records with identical restore_date values. When this happened I can no longer restore to either standby user db or and any attept to restore either DB from enterprise manager(all taskes>Restore Database) yields the following error: Error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I ran a trace on the DB and found the following are executed when Enterprise Manager tries to populate the restore Dialog Box: declare @b int; set @b = (select backup_set_id from msdb..restorehistory o where o.restore_date = (select max(i.restore_date) from msdb..restorehistory i where i. destination_database _name = N'DB1')); select min(backup_set_id) from msdb..backupset where type = 'L' and backup_set_id > @b and database_name = (select database_name from msdb..backupset where backup_set_id = @b) The second line, set @b = (select.... blah....) is doing a select over the entire table but does NOT have an AND clause qualifying the results for ONLY the particular database being restored. The query should be: (The differenc e is in CAPs) set @b = (select backup_set_id from msdb..restorehistory o where O. DESTINATION_DATABASE _NAME=n'DB1' and o.restore_date = (select max(i.restore_date) from msdb..restorehistory i where i. destination_database _name = N'DB1')); Updating the restore_date column on one of the 'conflicting' rows will correct the problem.
Post Follow-up to this message
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread