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.
>
>

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 i
n
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 t
o
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 creatin
g
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 y
our 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 us
ing either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't perfor
med 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 attac
h 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 o
n
> 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 Verit
as.
> 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 creat
ing
> 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 actio
n ;-)
> 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 backup
s (including this last log
> backup). Zero data loss.
>
> 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 perf
ormed by that last
> restore.
>
> No. If you are *very* lucky, you can do attach. As for the rest of the att
ach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in to
p 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...
>|||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...
>
>

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.
>
> 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.
>
> 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...
>
|||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...
>
>

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.
Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.
|||We had already changed the name of the new server to what the server
name was. So this shouldn't apply to my problem. However, if I
restored msdb while SQL Server Agent service was still running, would
this have caused the problem?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.|||We had already changed the name of the new server to what the server
name was. So this shouldn't apply to my problem. However, if I
restored msdb while SQL Server Agent service was still running, would
this have caused the problem?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.

Restoring Master question

Ive done some reading and it appears as though restoring master to a box
with another name is frowned upon. Does anyone know if its supported or not?
If so, do you have step by step instructions? If not, what about another box
name but a matching named instance?
--
sql2k sp3
TIA, ChrisRHi
Check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#10
and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
John
"ChrisR" <ChrisR@.NoEmails.com> wrote in message
news:u$x%23y2quEHA.2144@.tk2msftngp13.phx.gbl...
> Ive done some reading and it appears as though restoring master to a box
> with another name is frowned upon. Does anyone know if its supported or
not?
> If so, do you have step by step instructions? If not, what about another
box
> name but a matching named instance?
> --
> sql2k sp3
> TIA, ChrisR
>

Restoring Master on another machine...

We set up a test box and having been trying to move an entire instance
from our production server to this one. I have moved all my created
dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
Model, and msdb. This is where I am having trouble. On the production
box the dbs are stored on D:\mssql$instancename\data. On the test
server the data is stored in C:\Program Files\Microsoft SQL
Server\mssql$instancename\data. Also the server names are different.
So I finally got the Master from production to restore over the test's
copy. Now the instance will not start. I was trying to use the
following code in cmd line to connect to the instace so I could then
copy model and msdb:

sqlservr -c -f -T3608 -T4022 -sINSTANCENAME

When this is trying to connect I read it is failing and saying that the
MDF and LDF may be corrupt or not there. The problem is it is now
trying to look in D:\mssql$instancename\data instead of C:\Program
Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
can change this, or if I have to reinstall the entire instace, not run
into this again?(murrayb3024@.gmail.com) writes:
> We set up a test box and having been trying to move an entire instance
> from our production server to this one. I have moved all my created
> dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
> Model, and msdb. This is where I am having trouble. On the production
> box the dbs are stored on D:\mssql$instancename\data. On the test
> server the data is stored in C:\Program Files\Microsoft SQL
> Server\mssql$instancename\data. Also the server names are different.
> So I finally got the Master from production to restore over the test's
> copy. Now the instance will not start. I was trying to use the
> following code in cmd line to connect to the instace so I could then
> copy model and msdb:
> sqlservr -c -f -T3608 -T4022 -sINSTANCENAME
> When this is trying to connect I read it is failing and saying that the
> MDF and LDF may be corrupt or not there. The problem is it is now
> trying to look in D:\mssql$instancename\data instead of C:\Program
> Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
> can change this, or if I have to reinstall the entire instace, not run
> into this again?

Before you set off, did you look at
http://support.microsoft.com/defaul...b;EN-US;224071?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The article is really useful
http://support.microsoft.com/defaul...kb;en-us;224071

Also attach & detach should save your lot of time.

Thanks
Ajay Rengunthwar
MCDBA

Restoring master database

Hi All
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.com
Hi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com
|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony
|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default...22120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony
|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default...22120121120120
>John
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default...22120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com

Restoring master database

Hi All
I?m testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
I?ve started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start.
On checking the error log there are some VDN errors, I assume that this is
because the server I?m restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
--
Message posted via http://www.sqlmonster.comHi
Does this occur on subsequent restarts?
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5410926593904@.SQLMonster.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.sqlmonster.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Server,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54196F15F1D57@.SQLMonster.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
I?m not sure how Attach/Detach can help me as I?m trying to restore from
backup?
The common error I?m getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesn?t exist
producing VDN errors
Tony
John Bell wrote:
>Hi Tony
>Even with the -f flag?
>Have you followed
>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>John
>> Hi
>[quoted text clipped - 3 lines]
>> Tony
Message posted via http://www.sqlmonster.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
To move tempdb use the alter database command as described.
John
"Tony S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:544195E6FFA0B@.SQLMonster.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>>Hi Tony
>>Even with the -f flag?
>>Have you followed
>>http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071#XSLTH3188121122120121120120
>>John
>> Hi
>>[quoted text clipped - 3 lines]
>> Tony
>
> --
> Message posted via http://www.sqlmonster.com

Restoring master database

Hi All
Im testing our disaster recovery plan by rebuilding our production server
onto a new server box with different hardware and partitions.
Ive started the SQL server in single user mode and restored the master
database to a success message but when it tries to restart it fails to start
.
On checking the error log there are some VDN errors, I assume that this is
because the server Im restoring it to has different drive setup to the live
server.
Is there anyway around this problem?
Message posted via http://www.droptable.comHi
Does this occur on subsequent restarts?
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:5410926593904@.droptable.com...
> Hi All
> I'm testing our disaster recovery plan by rebuilding our production server
> onto a new server box with different hardware and partitions.
> I've started the SQL server in single user mode and restored the master
> database to a success message but when it tries to restart it fails to
> start.
>
> On checking the error log there are some VDN errors, I assume that this is
> because the server I'm restoring it to has different drive setup to the
> live
> server.
> Is there anyway around this problem?
>
> --
> Message posted via http://www.droptable.com|||Hi
I get the VDN error reported after everytime I try and restart the SQL Serve
r,
regardless of how I try to restart it.
Tony|||Hi Tony
Even with the -f flag?
Have you followed
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:54196F15F1D57@.droptable.com...
> Hi
> I get the VDN error reported after everytime I try and restart the SQL
> Server,
> regardless of how I try to restart it.
> Tony|||Hi John
Yes it fails to start even with the -f flag
I've tried changing the startup parameters but because the SQL server isn't
starting I can not get into the properties through Enterprise Manager.
Im not sure how Attach/Detach can help me as Im trying to restore from
backup?
The common error Im getting is when the SQL server is trying to start it is
looking for the other system databases from a drive that doesnt exist
producing VDN errors
Tony
John Bell wrote:[vbcol=seagreen]
>Hi Tony
>Even with the -f flag?
>Have you followed
>2120121120120" target="_blank">http://support.microsoft.com/defaul...
2120121120120
>John
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Hi
Test this out by calling sqlservr from a command prompt, see "sqlservr
Application" in Books online for parameters. Also check out the trace flag
3608 which will skip recovery on all databases except master, you can then
detach each database and re-attach with the new files as descibed for msdb
and model in
120121120120" target="_blank">http://support.microsoft.com/defaul...>
120121120120
To move tempdb use the alter database command as described.
John
"Tony S via droptable.com" <forum@.droptable.com> wrote in message
news:544195E6FFA0B@.droptable.com...
> Hi John
> Yes it fails to start even with the -f flag
> I've tried changing the startup parameters but because the SQL server
> isn't
> starting I can not get into the properties through Enterprise Manager.
> I'm not sure how Attach/Detach can help me as I'm trying to restore from
> backup?
> The common error I'm getting is when the SQL server is trying to start it
> is
> looking for the other system databases from a drive that doesn't exist
> producing VDN errors
> Tony
> John Bell wrote:
>
> --
> Message posted via http://www.droptable.com