I have a 2-part question.
1. I'm looking for a good strategy to save my data that I use for testing.
I made a set of good test data, and backed it up. I write my programs,
enter test trx, and then I want to start fresh again, so I restore from my
test data backup. This works fine.
For kicks, I tried to export the data to a new database. That worked fine.
But if I try to export data from the new database into my existing database,
I get lots duplicate key errors. I even tried setting the option to Delete
data, but that doesn't work because of foreign key constraints setup on some
of the tables.
Just curious what the recommened strategy for restoring test data is to
start anew!
2. When I make a change to the database, my collegues may have their own
test data and don't want to restore the test data that I have, but need the
database changes. What is the best way to do this? Should I just generate
the scripts for the tables I have changed?
Thanks for you help.If you want to start a fresh, then why not just drop the tables in the right
order, recreate the tables, and repopulate them with data?
This way, you will get to have scripts for both strucutres of the tables,
and data. So, if you only want structure, just run those CREATE TABLE
scripts.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Nancy De Cecco" <smiling.nancy@.verizon.net> wrote in message
news:#T0Iy5g2DHA.2620@.TK2MSFTNGP09.phx.gbl...
I have a 2-part question.
1. I'm looking for a good strategy to save my data that I use for testing.
I made a set of good test data, and backed it up. I write my programs,
enter test trx, and then I want to start fresh again, so I restore from my
test data backup. This works fine.
For kicks, I tried to export the data to a new database. That worked fine.
But if I try to export data from the new database into my existing database,
I get lots duplicate key errors. I even tried setting the option to Delete
data, but that doesn't work because of foreign key constraints setup on some
of the tables.
Just curious what the recommened strategy for restoring test data is to
start anew!
2. When I make a change to the database, my collegues may have their own
test data and don't want to restore the test data that I have, but need the
database changes. What is the best way to do this? Should I just generate
the scripts for the tables I have changed?
Thanks for you help.
Showing posts with label strategy. Show all posts
Showing posts with label strategy. Show all posts
Monday, March 26, 2012
Restoring test data
I have a 2-part question.
1. I'm looking for a good strategy to save my data that I use for testing.
I made a set of good test data, and backed it up. I write my programs,
enter test trx, and then I want to start fresh again, so I restore from my
test data backup. This works fine.
For kicks, I tried to export the data to a new database. That worked fine.
But if I try to export data from the new database into my existing database,
I get lots duplicate key errors. I even tried setting the option to Delete
data, but that doesn't work because of foreign key constraints setup on some
of the tables.
Just curious what the recommened strategy for restoring test data is to
start anew!
2. When I make a change to the database, my collegues may have their own
test data and don't want to restore the test data that I have, but need the
database changes. What is the best way to do this? Should I just generate
the scripts for the tables I have changed?
Thanks for you help.If you want to start a fresh, then why not just drop the tables in the right
order, recreate the tables, and repopulate them with data?
This way, you will get to have scripts for both strucutres of the tables,
and data. So, if you only want structure, just run those CREATE TABLE
scripts.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Nancy De Cecco" <smiling.nancy@.verizon.net> wrote in message
news:#T0Iy5g2DHA.2620@.TK2MSFTNGP09.phx.gbl...
I have a 2-part question.
1. I'm looking for a good strategy to save my data that I use for testing.
I made a set of good test data, and backed it up. I write my programs,
enter test trx, and then I want to start fresh again, so I restore from my
test data backup. This works fine.
For kicks, I tried to export the data to a new database. That worked fine.
But if I try to export data from the new database into my existing database,
I get lots duplicate key errors. I even tried setting the option to Delete
data, but that doesn't work because of foreign key constraints setup on some
of the tables.
Just curious what the recommened strategy for restoring test data is to
start anew!
2. When I make a change to the database, my collegues may have their own
test data and don't want to restore the test data that I have, but need the
database changes. What is the best way to do this? Should I just generate
the scripts for the tables I have changed?
Thanks for you help.|||check out DB Ghost @. www.dbghost.com for an excellent SQL
source code change management.
Building databases, migrating data, schema changes, static
data changes etc...
regards,
Mark Baekdal
>--Original Message--
>I have a 2-part question.
>1. I'm looking for a good strategy to save my data that
I use for testing.
>I made a set of good test data, and backed it up. I
write my programs,
>enter test trx, and then I want to start fresh again, so
I restore from my
>test data backup. This works fine.
>For kicks, I tried to export the data to a new database.
That worked fine.
>But if I try to export data from the new database into my
existing database,
>I get lots duplicate key errors. I even tried setting
the option to Delete
>data, but that doesn't work because of foreign key
constraints setup on some
>of the tables.
>Just curious what the recommened strategy for restoring
test data is to
>start anew!
>2. When I make a change to the database, my collegues
may have their own
>test data and don't want to restore the test data that I
have, but need the
>database changes. What is the best way to do this?
Should I just generate
>the scripts for the tables I have changed?
>Thanks for you help.
>
>.
>
1. I'm looking for a good strategy to save my data that I use for testing.
I made a set of good test data, and backed it up. I write my programs,
enter test trx, and then I want to start fresh again, so I restore from my
test data backup. This works fine.
For kicks, I tried to export the data to a new database. That worked fine.
But if I try to export data from the new database into my existing database,
I get lots duplicate key errors. I even tried setting the option to Delete
data, but that doesn't work because of foreign key constraints setup on some
of the tables.
Just curious what the recommened strategy for restoring test data is to
start anew!
2. When I make a change to the database, my collegues may have their own
test data and don't want to restore the test data that I have, but need the
database changes. What is the best way to do this? Should I just generate
the scripts for the tables I have changed?
Thanks for you help.If you want to start a fresh, then why not just drop the tables in the right
order, recreate the tables, and repopulate them with data?
This way, you will get to have scripts for both strucutres of the tables,
and data. So, if you only want structure, just run those CREATE TABLE
scripts.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Nancy De Cecco" <smiling.nancy@.verizon.net> wrote in message
news:#T0Iy5g2DHA.2620@.TK2MSFTNGP09.phx.gbl...
I have a 2-part question.
1. I'm looking for a good strategy to save my data that I use for testing.
I made a set of good test data, and backed it up. I write my programs,
enter test trx, and then I want to start fresh again, so I restore from my
test data backup. This works fine.
For kicks, I tried to export the data to a new database. That worked fine.
But if I try to export data from the new database into my existing database,
I get lots duplicate key errors. I even tried setting the option to Delete
data, but that doesn't work because of foreign key constraints setup on some
of the tables.
Just curious what the recommened strategy for restoring test data is to
start anew!
2. When I make a change to the database, my collegues may have their own
test data and don't want to restore the test data that I have, but need the
database changes. What is the best way to do this? Should I just generate
the scripts for the tables I have changed?
Thanks for you help.|||check out DB Ghost @. www.dbghost.com for an excellent SQL
source code change management.
Building databases, migrating data, schema changes, static
data changes etc...
regards,
Mark Baekdal
>--Original Message--
>I have a 2-part question.
>1. I'm looking for a good strategy to save my data that
I use for testing.
>I made a set of good test data, and backed it up. I
write my programs,
>enter test trx, and then I want to start fresh again, so
I restore from my
>test data backup. This works fine.
>For kicks, I tried to export the data to a new database.
That worked fine.
>But if I try to export data from the new database into my
existing database,
>I get lots duplicate key errors. I even tried setting
the option to Delete
>data, but that doesn't work because of foreign key
constraints setup on some
>of the tables.
>Just curious what the recommened strategy for restoring
test data is to
>start anew!
>2. When I make a change to the database, my collegues
may have their own
>test data and don't want to restore the test data that I
have, but need the
>database changes. What is the best way to do this?
Should I just generate
>the scripts for the tables I have changed?
>Thanks for you help.
>
>.
>
Tuesday, March 20, 2012
Restoring Multiple Logs In One Operation
I am working with a client on backup strategy. Their database undergoes a
full backup 2-3 times a week with hourly log backups during business hours
(8:00-5:00). When doing a restore, there are 10-15 log files with a TRN
extension that I need to restore one by one. Is there a way to restore the
logs as a group?
--
Larry Menzin
American Techsystems Corp.Not without executing several RESTORE LOG commands. You can automate this by writing some code for
your client. Hard to give further recommendations as we don't know anything about how the backups
are taken, named etc. But using the backup history tables in msdb is what EM does.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:CDDCDA6F-255D-4069-99C5-C10637DDD4EE@.microsoft.com...
>I am working with a client on backup strategy. Their database undergoes a
> full backup 2-3 times a week with hourly log backups during business hours
> (8:00-5:00). When doing a restore, there are 10-15 log files with a TRN
> extension that I need to restore one by one. Is there a way to restore the
> logs as a group?
> --
> Larry Menzin
> American Techsystems Corp.
full backup 2-3 times a week with hourly log backups during business hours
(8:00-5:00). When doing a restore, there are 10-15 log files with a TRN
extension that I need to restore one by one. Is there a way to restore the
logs as a group?
--
Larry Menzin
American Techsystems Corp.Not without executing several RESTORE LOG commands. You can automate this by writing some code for
your client. Hard to give further recommendations as we don't know anything about how the backups
are taken, named etc. But using the backup history tables in msdb is what EM does.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:CDDCDA6F-255D-4069-99C5-C10637DDD4EE@.microsoft.com...
>I am working with a client on backup strategy. Their database undergoes a
> full backup 2-3 times a week with hourly log backups during business hours
> (8:00-5:00). When doing a restore, there are 10-15 log files with a TRN
> extension that I need to restore one by one. Is there a way to restore the
> logs as a group?
> --
> Larry Menzin
> American Techsystems Corp.
Monday, March 12, 2012
restoring master database
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy
If you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy
|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>
|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas W?ckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the master
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
>
|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>
|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>
|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...[vbcol=seagreen]
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
|||Hmm. Do both have the same hotfixes?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy
If you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy
|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>
|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas W?ckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the master
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
>
|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>
|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>
|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...[vbcol=seagreen]
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
|||Hmm. Do both have the same hotfixes?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>
restoring master database
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andyIf you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas Wöckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the master
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> > If you are going to restore master from one server onto another, then you
> > need to have all DB's in the exact same location as they were on the
> > original server.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> > news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> > Hi group!
> >
> > I am trying to do the following for a recovery strategy: If server1 fails
> > all databases should be restored on server2. I did a restore of the master
> > database as described in some articles (starting in single user mode,
> > restoring...) after restoring my sql server does not start. An example
> > error
> > message is:
> >
> > FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> > virtual device number (VDN) 1.
> >
> > On server1 my data directory is d:\sql\mssql\data - on server2 my data
> > directory is d:\sql\mssql\mssql\data (configuration error during setup).
> > He
> > wants to load all databases but of course does not have all the
> > databases -
> > I have to recover them now - but how - I can't start the service..
> >
> > Anyone an idea?
> >
> > best regards
> >
> > andy
> >
> >
>
>|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1 fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during setup).
>> He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> > hi tom!
> >
> > hmm - but what are the steps that I have to take - Am I right that I first
> > have to restore all the databases and the last step is to restore the
> > master database?
> >
> > best regards
> >
> > andy
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> >> If you are going to restore master from one server onto another, then you
> >> need to have all DB's in the exact same location as they were on the
> >> original server.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> >> Hi group!
> >>
> >> I am trying to do the following for a recovery strategy: If server1 fails
> >> all databases should be restored on server2. I did a restore of the
> >> master
> >> database as described in some articles (starting in single user mode,
> >> restoring...) after restoring my sql server does not start. An example
> >> error
> >> message is:
> >>
> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> >> virtual device number (VDN) 1.
> >>
> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> >> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> >> He
> >> wants to load all databases but of course does not have all the
> >> databases -
> >> I have to recover them now - but how - I can't start the service..
> >>
> >> Anyone an idea?
> >>
> >> best regards
> >>
> >> andy
> >>
> >>
> >
> >
>
>|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> > hi tom!
> >
> > hmm - but what are the steps that I have to take - Am I right that I first
> > have to restore all the databases and the last step is to restore the
> > master database?
> >
> > best regards
> >
> > andy
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> >> If you are going to restore master from one server onto another, then you
> >> need to have all DB's in the exact same location as they were on the
> >> original server.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> >> Hi group!
> >>
> >> I am trying to do the following for a recovery strategy: If server1 fails
> >> all databases should be restored on server2. I did a restore of the
> >> master
> >> database as described in some articles (starting in single user mode,
> >> restoring...) after restoring my sql server does not start. An example
> >> error
> >> message is:
> >>
> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> >> virtual device number (VDN) 1.
> >>
> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> >> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> >> He
> >> wants to load all databases but of course does not have all the
> >> databases -
> >> I have to recover them now - but how - I can't start the service..
> >>
> >> Anyone an idea?
> >>
> >> best regards
> >>
> >> andy
> >>
> >>
> >
> >
>
>|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
>> What I do is restore the app DB's, then msdb then master. As I said
>> before - make sure they are in the exact same folders with the exact same
>> filenames as on your source server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> > hi tom!
>> >
>> > hmm - but what are the steps that I have to take - Am I right that I
>> > first
>> > have to restore all the databases and the last step is to restore the
>> > master database?
>> >
>> > best regards
>> >
>> > andy
>> >
>> >
>> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> >> If you are going to restore master from one server onto another, then
>> >> you
>> >> need to have all DB's in the exact same location as they were on the
>> >> original server.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Columnist, SQL Server Professional
>> >> Toronto, ON Canada
>> >> www.pinpub.com
>> >> .
>> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> >> Hi group!
>> >>
>> >> I am trying to do the following for a recovery strategy: If server1
>> >> fails
>> >> all databases should be restored on server2. I did a restore of the
>> >> master
>> >> database as described in some articles (starting in single user mode,
>> >> restoring...) after restoring my sql server does not start. An example
>> >> error
>> >> message is:
>> >>
>> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> >> virtual device number (VDN) 1.
>> >>
>> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> >> directory is d:\sql\mssql\mssql\data (configuration error during
>> >> setup).
>> >> He
>> >> wants to load all databases but of course does not have all the
>> >> databases -
>> >> I have to recover them now - but how - I can't start the service..
>> >>
>> >> Anyone an idea?
>> >>
>> >> best regards
>> >>
>> >> andy
>> >>
>> >>
>> >
>> >
>>|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> hi tom!
>> hmm - but what are the steps that I have to take - Am I right that I
>> first have to restore all the databases and the last step is to restore
>> the master database?
>> best regards
>> andy
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then
>> you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1
>> fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during setup).
>> He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>>
>|||Hmm. Do both have the same hotfixes?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>> What I do is restore the app DB's, then msdb then master. As I said
>> before - make sure they are in the exact same folders with the exact same
>> filenames as on your source server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> hi tom!
>> hmm - but what are the steps that I have to take - Am I right that I
>> first have to restore all the databases and the last step is to restore
>> the master database?
>> best regards
>> andy
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then
>> you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1
>> fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during
>> setup). He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>>
>>
>
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andyIf you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas Wöckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the master
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> > If you are going to restore master from one server onto another, then you
> > need to have all DB's in the exact same location as they were on the
> > original server.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinpub.com
> > .
> > "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> > news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> > Hi group!
> >
> > I am trying to do the following for a recovery strategy: If server1 fails
> > all databases should be restored on server2. I did a restore of the master
> > database as described in some articles (starting in single user mode,
> > restoring...) after restoring my sql server does not start. An example
> > error
> > message is:
> >
> > FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> > virtual device number (VDN) 1.
> >
> > On server1 my data directory is d:\sql\mssql\data - on server2 my data
> > directory is d:\sql\mssql\mssql\data (configuration error during setup).
> > He
> > wants to load all databases but of course does not have all the
> > databases -
> > I have to recover them now - but how - I can't start the service..
> >
> > Anyone an idea?
> >
> > best regards
> >
> > andy
> >
> >
>
>|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1 fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during setup).
>> He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> > hi tom!
> >
> > hmm - but what are the steps that I have to take - Am I right that I first
> > have to restore all the databases and the last step is to restore the
> > master database?
> >
> > best regards
> >
> > andy
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> >> If you are going to restore master from one server onto another, then you
> >> need to have all DB's in the exact same location as they were on the
> >> original server.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> >> Hi group!
> >>
> >> I am trying to do the following for a recovery strategy: If server1 fails
> >> all databases should be restored on server2. I did a restore of the
> >> master
> >> database as described in some articles (starting in single user mode,
> >> restoring...) after restoring my sql server does not start. An example
> >> error
> >> message is:
> >>
> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> >> virtual device number (VDN) 1.
> >>
> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> >> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> >> He
> >> wants to load all databases but of course does not have all the
> >> databases -
> >> I have to recover them now - but how - I can't start the service..
> >>
> >> Anyone an idea?
> >>
> >> best regards
> >>
> >> andy
> >>
> >>
> >
> >
>
>|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> > hi tom!
> >
> > hmm - but what are the steps that I have to take - Am I right that I first
> > have to restore all the databases and the last step is to restore the
> > master database?
> >
> > best regards
> >
> > andy
> >
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> >> If you are going to restore master from one server onto another, then you
> >> need to have all DB's in the exact same location as they were on the
> >> original server.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >> .
> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> >> Hi group!
> >>
> >> I am trying to do the following for a recovery strategy: If server1 fails
> >> all databases should be restored on server2. I did a restore of the
> >> master
> >> database as described in some articles (starting in single user mode,
> >> restoring...) after restoring my sql server does not start. An example
> >> error
> >> message is:
> >>
> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> >> virtual device number (VDN) 1.
> >>
> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> >> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> >> He
> >> wants to load all databases but of course does not have all the
> >> databases -
> >> I have to recover them now - but how - I can't start the service..
> >>
> >> Anyone an idea?
> >>
> >> best regards
> >>
> >> andy
> >>
> >>
> >
> >
>
>|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
>> What I do is restore the app DB's, then msdb then master. As I said
>> before - make sure they are in the exact same folders with the exact same
>> filenames as on your source server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> > hi tom!
>> >
>> > hmm - but what are the steps that I have to take - Am I right that I
>> > first
>> > have to restore all the databases and the last step is to restore the
>> > master database?
>> >
>> > best regards
>> >
>> > andy
>> >
>> >
>> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> > news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> >> If you are going to restore master from one server onto another, then
>> >> you
>> >> need to have all DB's in the exact same location as they were on the
>> >> original server.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Columnist, SQL Server Professional
>> >> Toronto, ON Canada
>> >> www.pinpub.com
>> >> .
>> >> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> >> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> >> Hi group!
>> >>
>> >> I am trying to do the following for a recovery strategy: If server1
>> >> fails
>> >> all databases should be restored on server2. I did a restore of the
>> >> master
>> >> database as described in some articles (starting in single user mode,
>> >> restoring...) after restoring my sql server does not start. An example
>> >> error
>> >> message is:
>> >>
>> >> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> >> virtual device number (VDN) 1.
>> >>
>> >> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> >> directory is d:\sql\mssql\mssql\data (configuration error during
>> >> setup).
>> >> He
>> >> wants to load all databases but of course does not have all the
>> >> databases -
>> >> I have to recover them now - but how - I can't start the service..
>> >>
>> >> Anyone an idea?
>> >>
>> >> best regards
>> >>
>> >> andy
>> >>
>> >>
>> >
>> >
>>|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> hi tom!
>> hmm - but what are the steps that I have to take - Am I right that I
>> first have to restore all the databases and the last step is to restore
>> the master database?
>> best regards
>> andy
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then
>> you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1
>> fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during setup).
>> He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>>
>|||Hmm. Do both have the same hotfixes?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wöckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>> What I do is restore the app DB's, then msdb then master. As I said
>> before - make sure they are in the exact same folders with the exact same
>> filenames as on your source server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>> hi tom!
>> hmm - but what are the steps that I have to take - Am I right that I
>> first have to restore all the databases and the last step is to restore
>> the master database?
>> best regards
>> andy
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
>> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>> If you are going to restore master from one server onto another, then
>> you
>> need to have all DB's in the exact same location as they were on the
>> original server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andreas Wöckl" <woeckl@.esys.at> wrote in message
>> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
>> Hi group!
>> I am trying to do the following for a recovery strategy: If server1
>> fails
>> all databases should be restored on server2. I did a restore of the
>> master
>> database as described in some articles (starting in single user mode,
>> restoring...) after restoring my sql server does not start. An example
>> error
>> message is:
>> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
>> virtual device number (VDN) 1.
>> On server1 my data directory is d:\sql\mssql\data - on server2 my data
>> directory is d:\sql\mssql\mssql\data (configuration error during
>> setup). He
>> wants to load all databases but of course does not have all the
>> databases -
>> I have to recover them now - but how - I can't start the service..
>> Anyone an idea?
>> best regards
>> andy
>>
>>
>>
>
restoring master database
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andyIf you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas W?ckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the mast
er
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
>|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...[vbcol=seagreen]
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
>|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>|||Hmm. Do both have the same hotfixes?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andyIf you are going to restore master from one server onto another, then you
need to have all DB's in the exact same location as they were on the
original server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
Hi group!
I am trying to do the following for a recovery strategy: If server1 fails
all databases should be restored on server2. I did a restore of the master
database as described in some articles (starting in single user mode,
restoring...) after restoring my sql server does not start. An example error
message is:
FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
virtual device number (VDN) 1.
On server1 my data directory is d:\sql\mssql\data - on server2 my data
directory is d:\sql\mssql\mssql\data (configuration error during setup). He
wants to load all databases but of course does not have all the databases -
I have to recover them now - but how - I can't start the service..
Anyone an idea?
best regards
andy|||hi tom!
hmm - but what are the steps that I have to take - Am I right that I first
have to restore all the databases and the last step is to restore the master
database?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
> If you are going to restore master from one server onto another, then you
> need to have all DB's in the exact same location as they were on the
> original server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:en%230WpR6FHA.1000@.tk2msftngp13.phx.gbl...
> Hi group!
> I am trying to do the following for a recovery strategy: If server1 fails
> all databases should be restored on server2. I did a restore of the master
> database as described in some articles (starting in single user mode,
> restoring...) after restoring my sql server does not start. An example
> error
> message is:
> FCB::Open failed: Could not open device d:\sql\MSSQL\data\pubs.mdf for
> virtual device number (VDN) 1.
> On server1 my data directory is d:\sql\mssql\data - on server2 my data
> directory is d:\sql\mssql\mssql\data (configuration error during setup).
> He
> wants to load all databases but of course does not have all the
> databases -
> I have to recover them now - but how - I can't start the service..
> Anyone an idea?
> best regards
> andy
>|||A technique we have used successfully in tests is:
1- Install SQL Server on new server
2- Restore individual databases to new server using one of three techniques:
a- Restore with third party software with new location
b- restore with manual restore command with move option
c- re-attach copies of detached databases
3- Get a list of the users from each application database
4- Either run sp_helplogins (and related scripts/procedures) or use the list
from 3 to rebuild access.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Andreas W?ckl" wrote:
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the mast
er
> database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>
>|||What I do is restore the app DB's, then msdb then master. As I said
before - make sure they are in the exact same folders with the exact same
filenames as on your source server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
> hi tom!
> hmm - but what are the steps that I have to take - Am I right that I first
> have to restore all the databases and the last step is to restore the
> master database?
> best regards
> andy
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%23jM$tsR6FHA.4076@.tk2msftngp13.phx.gbl...
>|||We restore the Master, MSDB before rebooting, and then the individual
databases.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>|||Here's the info I mentioned from BOL:
To recover from a disaster, perform the following steps after acquiring
suitable replacement hardware:
Install Windows NT 4.0 or Windows 2000, and apply the appropriate service
pack. Verify that appropriate domain functionality exists.
Install SQL Server, and apply the appropriate service pack. Restore the
master and msdb database backups. Restart the server after restoring the
master database.
Reconfigure the server for the appropriate network libraries and security
mode.
Confirm that SQL Server is running properly by checking SQL Server Service
Manager and the Windows application log. If the Windows NT 4.0 or Windows
2000 name was changed, use sp_dropserver and sp_addserver to match it with
the SQL Server computer name.
Restore and recover each database according to its recovery plan.
Verify the availability of the system. Run a base functionality script to
ensure correct operation.
Allow users to resume normal usage.
"Tom Moreau" wrote:
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas W?ckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
>|||The problem with that is that you get error messages, since the app DB's
don't (yet) exist.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"burt_king" <burt_king@.yahoo.com> wrote in message
news:1101C4D3-115D-43C8-A7FF-01DC1D728098@.microsoft.com...[vbcol=seagreen]
> We restore the Master, MSDB before rebooting, and then the individual
> databases.
> "Tom Moreau" wrote:
>|||hi tom!
thanks for your help - the recovery with the same paths was o.k - I restored
the user databases and then the master database. I still have some troubles
with the msdb because sql server prompts that the msdb has been created with
a different version - both sql servers have service pack 4..?
best regards
andy
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
> What I do is restore the app DB's, then msdb then master. As I said
> before - make sure they are in the exact same folders with the exact same
> filenames as on your source server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Andreas Wckl" <woeckl@.esys.at> wrote in message
> news:OglOL2R6FHA.3048@.TK2MSFTNGP10.phx.gbl...
>|||Hmm. Do both have the same hotfixes?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Andreas Wckl" <woeckl@.esys.at> wrote in message
news:eZ9onDb6FHA.2608@.tk2msftngp13.phx.gbl...
> hi tom!
> thanks for your help - the recovery with the same paths was o.k - I
> restored the user databases and then the master database. I still have
> some troubles with the msdb because sql server prompts that the msdb has
> been created with a different version - both sql servers have service pack
> 4..?
> best regards
> andy
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> schrieb im Newsbeitrag
> news:%2345V%232T6FHA.2896@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)