restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box in
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have to
restart sql server. The last backup that was done was file backup by Veritas.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creating
a new one by the same name and then doing the detach/attach work?
Thanks,
--
Dan D.Seems you are investigating every possible action except the correct action ;-)
Do a log backup using the NO_TRUNCATE option (the option is required since your database is
suspect). Restore the latest database backup and all subsequent log backups (including this last log
backup). Zero data loss.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
restore.
> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases on
> the SAN were marked "suspect". I've been able to reboot the sql server box in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have to
> restart sql server. The last backup that was done was file backup by Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Seems you are investigating every possible action except the correct action ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since your database is
> suspect). Restore the latest database backup and all subsequent log backups (including this last log
> backup). Zero data loss.
>
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
> restore.
>
> > Can I use the restore command if I just have an .mdf file?
> No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases on
> > the SAN were marked "suspect". I've been able to reboot the sql server box in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have to
> > restart sql server. The last backup that was done was file backup by Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>|||Thanks.
--
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases
> > on
> > the SAN were marked "suspect". I've been able to reboot the sql server box
> > in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have
> > to
> > restart sql server. The last backup that was done was file backup by
> > Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that
> > I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and
> > creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>
>