Database goes into Supect or Loading state after Restore

From: Neil Mowatt (neil_at_ncfunds.com)
Date: 09/23/04 

Date: 23 Sep 2004 10:13:40 -0700

This is the scenario:

I am do log shipping from one server to another. I have 9 databases
that I am shipping. 8 of them work flawlessly. I have one that for
some reason either always goes into a Suspect state or into a Loading
state. It just stays there until I do a manual restore. I have
created all of the store procedures exactly the same. The weird thing
is that I can do a manual restore of the exact same file that the
store procedure is trying to restore and it does it perfectly. The
only difference between this database and the others is the size. It
is a little over 6 gig versus 3 gig or less on the others.

I am stumped and not quite sure what to do. It is almost like the
stored procedure is either timing out or the job is advancing to the
next step before the restore is complete. Oh, one other thing, the
job is showing a status of completed successfully. The following is
the stored procedure I am using to restore the database:

CREATE PROCEDURE Restore_Database_W3Data_Backups AS

RESTORE DATABASE W3Data
FROM DISK = 'e:\sqlbackups\W3Data\W3Data.bak'
WITH
REPLACE,
STANDBY = 'e:\sqlbackups\W3Data\undo_W3Data.ldf',
MOVE 'w3data_dat' TO 'E:\SQL Data\MSSQL\Data\w3data_data.mdf',
MOVE 'w3data_log' TO 'E:\SQL Data\MSSQL\Data\w3data_log.ldf'

WAITFOR DELAY '00:00:05'

EXEC sp_dboption 'W3Data', 'single user', true
GO

Does Anyone have any ideas?

TIA!

Neil Mowatt
Network Administrator
The Nottingham Company

Relevant Pages

  • Re: Backend Exchange migrate to New Hardware (Server)
    ... Exchange mailbox stores and Exchange public folder stores. ... When you use Backup to restore Exchange databases, ... Storage Engine to restore Exchange database files and their ...
    (microsoft.public.exchange.setup)
  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • Re: Restoring Server which utilizes Log Shipping
    ... The Log shipping wizard will only truncate the log after it has backed it up ... You only need the ones from the last full backup. ... if you backup the log frequently to save restore time. ... > I have a large database that gets a full backup every night. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Shipping setup
    ... use the GUI on the source and remove the log shipping. ... In the second case (server blown to pieces), run all the LS_Restore jobs to ... restore database MyDestinationDB with recovery ... how do you get the database to be fully functioning? ...
    (microsoft.public.sqlserver.replication)
  • RE: Restore public folders and companyweb
    ... > where the backup of the database. ... Before you can start to restore the SharePoint Services databases, ... > Microsoft CSS Online Newsgroup Support ... > This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)