Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fujihuynh
Starting Member
3 Posts |
 Posted - 2004-11-18 : 12:58:15
|
| I was performing a restore on a development server (SQL Server 7) when the restore failed due to insufficient disk space. I cleared out some disk space and opened up Enterprise Manager again and can now see my database greyed out with the words "(Loading\Suspect)" appended.When I now try and do a restore with the Force restore over existing database option checked I get the following message:"Cannot drop the database ... because it is published for replication. RESTORE DATABASE could not drop database .... Drop the database and then reissue the RESTORE DATABASE statement. Backupp or restore operation terminating abnormally"When I try and drop the database I get this message:"Cannot drop the database ... because it is published for replication. DBCC execution completed. If DBCC printed error messages, contact your system administrator."If I go into Publisher and Distributor properties and remove the subscription then I get this message:"Error 927: Database ... cannot be opened. It is in the middle of a restore."Help!! I seem to be stuck in a Catch-22 situation where I can't do a restore without disabling the replication, but I can't disable the replication because SQL Server thinks it's in the middle of a restore. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-18 : 14:07:27
|
| Take a backup of pubs or some small database. Restore over your database. You can then restore your database using your backup file. It's kludgy, but it works.Tara |
 |
|
|
fujihuynh
Starting Member
3 Posts |
Posted - 2004-11-19 : 05:14:29
|
| Still get the same message:"Cannot drop the database ... because it is published for replication. RESTORE DATABASE could not drop database .... Drop the database and then reissue the RESTORE DATABASE statement. Backupp or restore operation terminating abnormally"when I try and restore a backup of the Pubs database over my database which was published for replication. As I said SQL Server won't allow me to stop the publication because it thinks I'm in the middle of a restore and won't allow me to restore because the database is published for replication. |
|
|
|
fujihuynh
Starting Member
3 Posts |
Posted - 2004-11-19 : 06:43:41
|
| Problem solved now.I had to make the database off-line before I could drop it and now I can restore it from a backup.Exec sp_dboption 'mydatabase', 'offline', 'true'Drop Database mydatabase |
|
|
|
|
|
|
Restore failure on replicated database