Migrate DBs off dying server

I desperately need some help here.
I am the SQL admin by default, but I am by no means an expert. I have a
single SQL 2000 SP2 (on Win 2003) server with 26 DBs including the system
DBs. They are mostly light use DBs (blackberry, rightfax, antivirus, WSUS,
etc).
My server had hardware failure 4 days ago, and it has led to a ton of
problems. The file systems are corrupt to the point checkdsk cannot fix
them. Most applications on the server cannot run. The SQLAgent service
won't. Enterprise manager on the server won't run. I can access it remotely
with Enterprise manager, but query analyzer can't connect. Needless to say,
I have problems.
I will have a new server on Monday. All but 4 of the DBs are up. 4 are
suspect. they are suspect because their LDF files are corrupt. These files
show as 0K and can't be copied, moved, renamed, deleted etc even if the SQL
service isn't running. I need to get all these DBs moved to a new server,
and then rename the server back to the old servers name. Only 2 of these DBs
(Blackberry and Rightfax) need to be back online quickly. They are both
currently online. I would like to move them, get everything up and running,
and then worry about the rest. Can anyone point me to some steps on moving
the DBs that are functional, and then possibly attaching the MDF files of
the DBs that aren't functional? Some Data loss is ok. Most of these DBs are
fed their information form other systems, and it can easily be updated.
Because of the hardware failure, I don't really have good backups. I have
them from 4 days ago, but I would prefer to use the fresher data if
possible.
Thanks in advance for any assistance you can provide!
-Matthew KitchinLogin and backup the healthy databases. For the un-healthy database, try copy the db files and
attach. For the database you do get into the new system, run DBCC CHECKDB. For the ones you don't,
re-populate/use the older backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
news:uFo83Wf3GHA.1300@.TK2MSFTNGP05.phx.gbl...
>I desperately need some help here.
> I am the SQL admin by default, but I am by no means an expert. I have a single SQL 2000 SP2 (on
> Win 2003) server with 26 DBs including the system DBs. They are mostly light use DBs (blackberry,
> rightfax, antivirus, WSUS, etc).
> My server had hardware failure 4 days ago, and it has led to a ton of problems. The file systems
> are corrupt to the point checkdsk cannot fix them. Most applications on the server cannot run. The
> SQLAgent service won't. Enterprise manager on the server won't run. I can access it remotely with
> Enterprise manager, but query analyzer can't connect. Needless to say, I have problems.
> I will have a new server on Monday. All but 4 of the DBs are up. 4 are suspect. they are suspect
> because their LDF files are corrupt. These files show as 0K and can't be copied, moved, renamed,
> deleted etc even if the SQL service isn't running. I need to get all these DBs moved to a new
> server, and then rename the server back to the old servers name. Only 2 of these DBs (Blackberry
> and Rightfax) need to be back online quickly. They are both currently online. I would like to move
> them, get everything up and running, and then worry about the rest. Can anyone point me to some
> steps on moving the DBs that are functional, and then possibly attaching the MDF files of the DBs
> that aren't functional? Some Data loss is ok. Most of these DBs are fed their information form
> other systems, and it can easily be updated. Because of the hardware failure, I don't really have
> good backups. I have them from 4 days ago, but I would prefer to use the fresher data if possible.
> Thanks in advance for any assistance you can provide!
>
> -Matthew Kitchin
>|||Thank you. Can you tell me how I can back them up without being able run
enterprise manager or query analyzer?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7zpLJh3GHA.5024@.TK2MSFTNGP02.phx.gbl...
> Login and backup the healthy databases. For the un-healthy database, try
> copy the db files and attach. For the database you do get into the new
> system, run DBCC CHECKDB. For the ones you don't, re-populate/use the
> older backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
> message news:uFo83Wf3GHA.1300@.TK2MSFTNGP05.phx.gbl...
>>I desperately need some help here.
>> I am the SQL admin by default, but I am by no means an expert. I have a
>> single SQL 2000 SP2 (on Win 2003) server with 26 DBs including the system
>> DBs. They are mostly light use DBs (blackberry, rightfax, antivirus,
>> WSUS, etc).
>> My server had hardware failure 4 days ago, and it has led to a ton of
>> problems. The file systems are corrupt to the point checkdsk cannot fix
>> them. Most applications on the server cannot run. The SQLAgent service
>> won't. Enterprise manager on the server won't run. I can access it
>> remotely with Enterprise manager, but query analyzer can't connect.
>> Needless to say, I have problems.
>> I will have a new server on Monday. All but 4 of the DBs are up. 4 are
>> suspect. they are suspect because their LDF files are corrupt. These
>> files show as 0K and can't be copied, moved, renamed, deleted etc even if
>> the SQL service isn't running. I need to get all these DBs moved to a new
>> server, and then rename the server back to the old servers name. Only 2
>> of these DBs (Blackberry and Rightfax) need to be back online quickly.
>> They are both currently online. I would like to move them, get everything
>> up and running, and then worry about the rest. Can anyone point me to
>> some steps on moving the DBs that are functional, and then possibly
>> attaching the MDF files of the DBs that aren't functional? Some Data loss
>> is ok. Most of these DBs are fed their information form other systems,
>> and it can easily be updated. Because of the hardware failure, I don't
>> really have good backups. I have them from 4 days ago, but I would prefer
>> to use the fresher data if possible.
>> Thanks in advance for any assistance you can provide!
>>
>> -Matthew Kitchin
>|||Matthew Kitchin (Usenet/Lists) wrote:
> Thank you. Can you tell me how I can back them up without being able run
> enterprise manager or query analyzer?
>
You said that 4 of the databases are suspect, but all of the others are
up. How do you know this if you can't connect with EM or QA?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Try ISQL.EXE or OSQL.EXE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
news:%23dVTsAk3GHA.1292@.TK2MSFTNGP03.phx.gbl...
> Thank you. Can you tell me how I can back them up without being able run enterprise manager or
> query analyzer?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O7zpLJh3GHA.5024@.TK2MSFTNGP02.phx.gbl...
>> Login and backup the healthy databases. For the un-healthy database, try copy the db files and
>> attach. For the database you do get into the new system, run DBCC CHECKDB. For the ones you
>> don't, re-populate/use the older backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:uFo83Wf3GHA.1300@.TK2MSFTNGP05.phx.gbl...
>>I desperately need some help here.
>> I am the SQL admin by default, but I am by no means an expert. I have a single SQL 2000 SP2 (on
>> Win 2003) server with 26 DBs including the system DBs. They are mostly light use DBs
>> (blackberry, rightfax, antivirus, WSUS, etc).
>> My server had hardware failure 4 days ago, and it has led to a ton of problems. The file systems
>> are corrupt to the point checkdsk cannot fix them. Most applications on the server cannot run.
>> The SQLAgent service won't. Enterprise manager on the server won't run. I can access it remotely
>> with Enterprise manager, but query analyzer can't connect. Needless to say, I have problems.
>> I will have a new server on Monday. All but 4 of the DBs are up. 4 are suspect. they are suspect
>> because their LDF files are corrupt. These files show as 0K and can't be copied, moved, renamed,
>> deleted etc even if the SQL service isn't running. I need to get all these DBs moved to a new
>> server, and then rename the server back to the old servers name. Only 2 of these DBs (Blackberry
>> and Rightfax) need to be back online quickly. They are both currently online. I would like to
>> move them, get everything up and running, and then worry about the rest. Can anyone point me to
>> some steps on moving the DBs that are functional, and then possibly attaching the MDF files of
>> the DBs that aren't functional? Some Data loss is ok. Most of these DBs are fed their
>> information form other systems, and it can easily be updated. Because of the hardware failure, I
>> don't really have good backups. I have them from 4 days ago, but I would prefer to use the
>> fresher data if possible.
>> Thanks in advance for any assistance you can provide!
>>
>> -Matthew Kitchin
>>
>|||I can connect with EM from a remote machine. The backup utility fails
though. I can't run QA from anywhere.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4513DFC8.5080901@.realsqlguy.com...
> Matthew Kitchin (Usenet/Lists) wrote:
>> Thank you. Can you tell me how I can back them up without being able run
>> enterprise manager or query analyzer?
> You said that 4 of the databases are suspect, but all of the others are
> up. How do you know this if you can't connect with EM or QA?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||If you can connect using EM (from anywhere), then you should be able to connect using OSQL or ISQL
(which is the most forgiving tool). From there you can try the backup commands, and see if/what
error message(s) are returned.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>I can connect with EM from a remote machine. The backup utility fails though. I can't run QA from
>anywhere.
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:4513DFC8.5080901@.realsqlguy.com...
>> Matthew Kitchin (Usenet/Lists) wrote:
>> Thank you. Can you tell me how I can back them up without being able run enterprise manager or
>> query analyzer?
>>
>> You said that 4 of the databases are suspect, but all of the others are up. How do you know this
>> if you can't connect with EM or QA?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||I am able to connect using OSQL. Thanks!
Can you tell me the best path to take now? Do I import the backups onto the
new server? What about the suspect DBs that have corrupt logfiles? Can I
attach those with just the mdf file?
I greatly appreciate your help. I often help out in other forums (AD,
Exchange, etc), but could use all the help I can get on this!
Thanks again,
Matthew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
> If you can connect using EM (from anywhere), then you should be able to
> connect using OSQL or ISQL (which is the most forgiving tool). From there
> you can try the backup commands, and see if/what error message(s) are
> returned.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
> message news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>I can connect with EM from a remote machine. The backup utility fails
>>though. I can't run QA from anywhere.
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:4513DFC8.5080901@.realsqlguy.com...
>> Matthew Kitchin (Usenet/Lists) wrote:
>> Thank you. Can you tell me how I can back them up without being able
>> run enterprise manager or query analyzer?
>>
>> You said that 4 of the databases are suspect, but all of the others are
>> up. How do you know this if you can't connect with EM or QA?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>|||>I am able to connect using OSQL. Thanks!
Great!
> Can you tell me the best path to take now?
Read up on the BACKUP DATABASE command. Execute it to create a backup of each database. Something
like:
BACKUP DATABASE dbname TO DISK = 'C:\dbname.BAK'
GO
If the SQL Server service account is a domain account, you can try an UNC path for the backup file.
Remember it is the service account that matters, not whatever account you are logged in as.
> What about the suspect DBs that have corrupt logfiles?
They are most probably toast. Sorry.
> Can I attach those with just the mdf file?
Probably not. Whenever SQL Server starts a database, it need to look in the log file to see what
operations were in flight so it can perform a REDO and then UNDO phase. Otherwise you end up with a
database in a chaotic state (both logically and possibly also physically):
Your app wanted to transfer $200 from one account to another. The two update statements are
obviously protected inside a transaction. The first statement succeeded, but the second was never
executed. You don't want a database in such state. This is only a very simple case of why the log
file is so important at database startup.
You might find stuff out there on commands that rebuilds the log. These are not documented and were
never meant to reach the public, so I won't mention them here. Judging from above, you probably
realize what havoc such can cause to your database.
There are situations where SQL Server know that a database were cleanly shutdown, and in such cases,
sp_attach_single_file_db can be used to rebuild the log. The prerequisites are documented, one of
them are that you actually detached the database first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
news:%231PDTRl3GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I am able to connect using OSQL. Thanks!
> Can you tell me the best path to take now? Do I import the backups onto the new server? What about
> the suspect DBs that have corrupt logfiles? Can I attach those with just the mdf file?
> I greatly appreciate your help. I often help out in other forums (AD, Exchange, etc), but could
> use all the help I can get on this!
> Thanks again,
> Matthew
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
>> If you can connect using EM (from anywhere), then you should be able to connect using OSQL or
>> ISQL (which is the most forgiving tool). From there you can try the backup commands, and see
>> if/what error message(s) are returned.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>I can connect with EM from a remote machine. The backup utility fails though. I can't run QA from
>>anywhere.
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:4513DFC8.5080901@.realsqlguy.com...
>> Matthew Kitchin (Usenet/Lists) wrote:
>> Thank you. Can you tell me how I can back them up without being able run enterprise manager or
>> query analyzer?
>>
>> You said that 4 of the databases are suspect, but all of the others are up. How do you know
>> this if you can't connect with EM or QA?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>|||The backups with OSql are working great.
Do I need to restore all the system DBs as well? I assume I don't need to
restore tempdb, but what about master and model? Thanks again for all your
help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYGbDYl3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> >I am able to connect using OSQL. Thanks!
> Great!
>
>> Can you tell me the best path to take now?
> Read up on the BACKUP DATABASE command. Execute it to create a backup of
> each database. Something like:
> BACKUP DATABASE dbname TO DISK = 'C:\dbname.BAK'
> GO
> If the SQL Server service account is a domain account, you can try an UNC
> path for the backup file. Remember it is the service account that matters,
> not whatever account you are logged in as.
>> What about the suspect DBs that have corrupt logfiles?
> They are most probably toast. Sorry.
>
>> Can I attach those with just the mdf file?
> Probably not. Whenever SQL Server starts a database, it need to look in
> the log file to see what operations were in flight so it can perform a
> REDO and then UNDO phase. Otherwise you end up with a database in a
> chaotic state (both logically and possibly also physically):
> Your app wanted to transfer $200 from one account to another. The two
> update statements are obviously protected inside a transaction. The first
> statement succeeded, but the second was never executed. You don't want a
> database in such state. This is only a very simple case of why the log
> file is so important at database startup.
> You might find stuff out there on commands that rebuilds the log. These
> are not documented and were never meant to reach the public, so I won't
> mention them here. Judging from above, you probably realize what havoc
> such can cause to your database.
> There are situations where SQL Server know that a database were cleanly
> shutdown, and in such cases, sp_attach_single_file_db can be used to
> rebuild the log. The prerequisites are documented, one of them are that
> you actually detached the database first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
> message news:%231PDTRl3GHA.1252@.TK2MSFTNGP04.phx.gbl...
>>I am able to connect using OSQL. Thanks!
>> Can you tell me the best path to take now? Do I import the backups onto
>> the new server? What about the suspect DBs that have corrupt logfiles?
>> Can I attach those with just the mdf file?
>> I greatly appreciate your help. I often help out in other forums (AD,
>> Exchange, etc), but could use all the help I can get on this!
>> Thanks again,
>> Matthew
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
>> If you can connect using EM (from anywhere), then you should be able to
>> connect using OSQL or ISQL (which is the most forgiving tool). From
>> there you can try the backup commands, and see if/what error message(s)
>> are returned.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
>> message news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>I can connect with EM from a remote machine. The backup utility fails
>>though. I can't run QA from anywhere.
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:4513DFC8.5080901@.realsqlguy.com...
>> Matthew Kitchin (Usenet/Lists) wrote:
>> Thank you. Can you tell me how I can back them up without being able
>> run enterprise manager or query analyzer?
>>
>> You said that 4 of the databases are suspect, but all of the others
>> are up. How do you know this if you can't connect with EM or QA?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>>
>|||Restoring system database can be tricky if you don't know a bit about how SQL Server boots. KB
224071 can give some clarity to this topic, for instance. If you chose not to restore the system
databases, you need to handle stuff in them, like:
Master:
Logins
Linked Servers
Sp_configure settings
msdb:
Jobs
Alerts
operators
No need to restore tempdb.
If you modified model, you need to handle this as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
news:uLf9KBp3GHA.1300@.TK2MSFTNGP05.phx.gbl...
> The backups with OSql are working great.
> Do I need to restore all the system DBs as well? I assume I don't need to restore tempdb, but what
> about master and model? Thanks again for all your help.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OYGbDYl3GHA.1588@.TK2MSFTNGP02.phx.gbl...
>> >I am able to connect using OSQL. Thanks!
>> Great!
>>
>> Can you tell me the best path to take now?
>> Read up on the BACKUP DATABASE command. Execute it to create a backup of each database. Something
>> like:
>> BACKUP DATABASE dbname TO DISK = 'C:\dbname.BAK'
>> GO
>> If the SQL Server service account is a domain account, you can try an UNC path for the backup
>> file. Remember it is the service account that matters, not whatever account you are logged in as.
>> What about the suspect DBs that have corrupt logfiles?
>> They are most probably toast. Sorry.
>>
>> Can I attach those with just the mdf file?
>> Probably not. Whenever SQL Server starts a database, it need to look in the log file to see what
>> operations were in flight so it can perform a REDO and then UNDO phase. Otherwise you end up with
>> a database in a chaotic state (both logically and possibly also physically):
>> Your app wanted to transfer $200 from one account to another. The two update statements are
>> obviously protected inside a transaction. The first statement succeeded, but the second was never
>> executed. You don't want a database in such state. This is only a very simple case of why the log
>> file is so important at database startup.
>> You might find stuff out there on commands that rebuilds the log. These are not documented and
>> were never meant to reach the public, so I won't mention them here. Judging from above, you
>> probably realize what havoc such can cause to your database.
>> There are situations where SQL Server know that a database were cleanly shutdown, and in such
>> cases, sp_attach_single_file_db can be used to rebuild the log. The prerequisites are documented,
>> one of them are that you actually detached the database first.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:%231PDTRl3GHA.1252@.TK2MSFTNGP04.phx.gbl...
>>I am able to connect using OSQL. Thanks!
>> Can you tell me the best path to take now? Do I import the backups onto the new server? What
>> about the suspect DBs that have corrupt logfiles? Can I attach those with just the mdf file?
>> I greatly appreciate your help. I often help out in other forums (AD, Exchange, etc), but could
>> use all the help I can get on this!
>> Thanks again,
>> Matthew
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
>> If you can connect using EM (from anywhere), then you should be able to connect using OSQL or
>> ISQL (which is the most forgiving tool). From there you can try the backup commands, and see
>> if/what error message(s) are returned.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>I can connect with EM from a remote machine. The backup utility fails though. I can't run QA
>>from anywhere.
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:4513DFC8.5080901@.realsqlguy.com...
>> Matthew Kitchin (Usenet/Lists) wrote:
>>> Thank you. Can you tell me how I can back them up without being able run enterprise manager
>>> or query analyzer?
>>>
>> You said that 4 of the databases are suspect, but all of the others are up. How do you know
>> this if you can't connect with EM or QA?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>>
>>
>|||Thank you!
Long day, but everything is up and running now. Thanks again for your help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ov29Qgu3GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Restoring system database can be tricky if you don't know a bit about how
> SQL Server boots. KB 224071 can give some clarity to this topic, for
> instance. If you chose not to restore the system databases, you need to
> handle stuff in them, like:
> Master:
> Logins
> Linked Servers
> Sp_configure settings
> msdb:
> Jobs
> Alerts
> operators
> No need to restore tempdb.
> If you modified model, you need to handle this as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
> message news:uLf9KBp3GHA.1300@.TK2MSFTNGP05.phx.gbl...
>> The backups with OSql are working great.
>> Do I need to restore all the system DBs as well? I assume I don't need to
>> restore tempdb, but what about master and model? Thanks again for all
>> your help.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OYGbDYl3GHA.1588@.TK2MSFTNGP02.phx.gbl...
>> >I am able to connect using OSQL. Thanks!
>> Great!
>>
>> Can you tell me the best path to take now?
>> Read up on the BACKUP DATABASE command. Execute it to create a backup of
>> each database. Something like:
>> BACKUP DATABASE dbname TO DISK = 'C:\dbname.BAK'
>> GO
>> If the SQL Server service account is a domain account, you can try an
>> UNC path for the backup file. Remember it is the service account that
>> matters, not whatever account you are logged in as.
>> What about the suspect DBs that have corrupt logfiles?
>> They are most probably toast. Sorry.
>>
>> Can I attach those with just the mdf file?
>> Probably not. Whenever SQL Server starts a database, it need to look in
>> the log file to see what operations were in flight so it can perform a
>> REDO and then UNDO phase. Otherwise you end up with a database in a
>> chaotic state (both logically and possibly also physically):
>> Your app wanted to transfer $200 from one account to another. The two
>> update statements are obviously protected inside a transaction. The
>> first statement succeeded, but the second was never executed. You don't
>> want a database in such state. This is only a very simple case of why
>> the log file is so important at database startup.
>> You might find stuff out there on commands that rebuilds the log. These
>> are not documented and were never meant to reach the public, so I won't
>> mention them here. Judging from above, you probably realize what havoc
>> such can cause to your database.
>> There are situations where SQL Server know that a database were cleanly
>> shutdown, and in such cases, sp_attach_single_file_db can be used to
>> rebuild the log. The prerequisites are documented, one of them are that
>> you actually detached the database first.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
>> message news:%231PDTRl3GHA.1252@.TK2MSFTNGP04.phx.gbl...
>>I am able to connect using OSQL. Thanks!
>> Can you tell me the best path to take now? Do I import the backups onto
>> the new server? What about the suspect DBs that have corrupt logfiles?
>> Can I attach those with just the mdf file?
>> I greatly appreciate your help. I often help out in other forums (AD,
>> Exchange, etc), but could use all the help I can get on this!
>> Thanks again,
>> Matthew
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
>> If you can connect using EM (from anywhere), then you should be able
>> to connect using OSQL or ISQL (which is the most forgiving tool). From
>> there you can try the backup commands, and see if/what error
>> message(s) are returned.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
>> message news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>I can connect with EM from a remote machine. The backup utility fails
>>though. I can't run QA from anywhere.
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:4513DFC8.5080901@.realsqlguy.com...
>>> Matthew Kitchin (Usenet/Lists) wrote:
>>> Thank you. Can you tell me how I can back them up without being
>>> able run enterprise manager or query analyzer?
>>>
>>>
>>> You said that 4 of the databases are suspect, but all of the others
>>> are up. How do you know this if you can't connect with EM or QA?
>>>
>>>
>>> --
>>> Tracy McKibben
>>> MCDBA
>>> http://www.realsqlguy.com
>>
>>
>>
>>
>|||Glad you had it working in the end. Remember to do DBCC CHECKDB on the restored databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
news:uDITyC33GHA.836@.TK2MSFTNGP02.phx.gbl...
> Thank you!
> Long day, but everything is up and running now. Thanks again for your help.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Ov29Qgu3GHA.1848@.TK2MSFTNGP06.phx.gbl...
>> Restoring system database can be tricky if you don't know a bit about how SQL Server boots. KB
>> 224071 can give some clarity to this topic, for instance. If you chose not to restore the system
>> databases, you need to handle stuff in them, like:
>> Master:
>> Logins
>> Linked Servers
>> Sp_configure settings
>> msdb:
>> Jobs
>> Alerts
>> operators
>> No need to restore tempdb.
>> If you modified model, you need to handle this as well.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:uLf9KBp3GHA.1300@.TK2MSFTNGP05.phx.gbl...
>> The backups with OSql are working great.
>> Do I need to restore all the system DBs as well? I assume I don't need to restore tempdb, but
>> what about master and model? Thanks again for all your help.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OYGbDYl3GHA.1588@.TK2MSFTNGP02.phx.gbl...
>> >I am able to connect using OSQL. Thanks!
>> Great!
>>
>> Can you tell me the best path to take now?
>> Read up on the BACKUP DATABASE command. Execute it to create a backup of each database.
>> Something like:
>> BACKUP DATABASE dbname TO DISK = 'C:\dbname.BAK'
>> GO
>> If the SQL Server service account is a domain account, you can try an UNC path for the backup
>> file. Remember it is the service account that matters, not whatever account you are logged in
>> as.
>> What about the suspect DBs that have corrupt logfiles?
>> They are most probably toast. Sorry.
>>
>> Can I attach those with just the mdf file?
>> Probably not. Whenever SQL Server starts a database, it need to look in the log file to see
>> what operations were in flight so it can perform a REDO and then UNDO phase. Otherwise you end
>> up with a database in a chaotic state (both logically and possibly also physically):
>> Your app wanted to transfer $200 from one account to another. The two update statements are
>> obviously protected inside a transaction. The first statement succeeded, but the second was
>> never executed. You don't want a database in such state. This is only a very simple case of why
>> the log file is so important at database startup.
>> You might find stuff out there on commands that rebuilds the log. These are not documented and
>> were never meant to reach the public, so I won't mention them here. Judging from above, you
>> probably realize what havoc such can cause to your database.
>> There are situations where SQL Server know that a database were cleanly shutdown, and in such
>> cases, sp_attach_single_file_db can be used to rebuild the log. The prerequisites are
>> documented, one of them are that you actually detached the database first.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:%231PDTRl3GHA.1252@.TK2MSFTNGP04.phx.gbl...
>>I am able to connect using OSQL. Thanks!
>> Can you tell me the best path to take now? Do I import the backups onto the new server? What
>> about the suspect DBs that have corrupt logfiles? Can I attach those with just the mdf file?
>> I greatly appreciate your help. I often help out in other forums (AD, Exchange, etc), but
>> could use all the help I can get on this!
>> Thanks again,
>> Matthew
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
>> If you can connect using EM (from anywhere), then you should be able to connect using OSQL or
>> ISQL (which is the most forgiving tool). From there you can try the backup commands, and see
>> if/what error message(s) are returned.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in message
>> news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>>I can connect with EM from a remote machine. The backup utility fails though. I can't run QA
>>>from anywhere.
>>>
>>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>>> news:4513DFC8.5080901@.realsqlguy.com...
>>> Matthew Kitchin (Usenet/Lists) wrote:
>>> Thank you. Can you tell me how I can back them up without being able run enterprise
>>> manager or query analyzer?
>>>
>>>
>>> You said that 4 of the databases are suspect, but all of the others are up. How do you
>>> know this if you can't connect with EM or QA?
>>>
>>>
>>> --
>>> Tracy McKibben
>>> MCDBA
>>> http://www.realsqlguy.com
>>>
>>>
>>
>>
>>
>|||Thanks. 4 had issues. I was able to repair 3, but one I ended up having to
restore from a 2 week old backup.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lLXyC73GHA.3428@.TK2MSFTNGP05.phx.gbl...
> Glad you had it working in the end. Remember to do DBCC CHECKDB on the
> restored databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
> message news:uDITyC33GHA.836@.TK2MSFTNGP02.phx.gbl...
>> Thank you!
>> Long day, but everything is up and running now. Thanks again for your
>> help.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Ov29Qgu3GHA.1848@.TK2MSFTNGP06.phx.gbl...
>> Restoring system database can be tricky if you don't know a bit about
>> how SQL Server boots. KB 224071 can give some clarity to this topic, for
>> instance. If you chose not to restore the system databases, you need to
>> handle stuff in them, like:
>> Master:
>> Logins
>> Linked Servers
>> Sp_configure settings
>> msdb:
>> Jobs
>> Alerts
>> operators
>> No need to restore tempdb.
>> If you modified model, you need to handle this as well.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
>> message news:uLf9KBp3GHA.1300@.TK2MSFTNGP05.phx.gbl...
>> The backups with OSql are working great.
>> Do I need to restore all the system DBs as well? I assume I don't need
>> to restore tempdb, but what about master and model? Thanks again for
>> all your help.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OYGbDYl3GHA.1588@.TK2MSFTNGP02.phx.gbl...
>> >I am able to connect using OSQL. Thanks!
>> Great!
>>
>> Can you tell me the best path to take now?
>> Read up on the BACKUP DATABASE command. Execute it to create a backup
>> of each database. Something like:
>> BACKUP DATABASE dbname TO DISK = 'C:\dbname.BAK'
>> GO
>> If the SQL Server service account is a domain account, you can try an
>> UNC path for the backup file. Remember it is the service account that
>> matters, not whatever account you are logged in as.
>> What about the suspect DBs that have corrupt logfiles?
>> They are most probably toast. Sorry.
>>
>> Can I attach those with just the mdf file?
>> Probably not. Whenever SQL Server starts a database, it need to look
>> in the log file to see what operations were in flight so it can
>> perform a REDO and then UNDO phase. Otherwise you end up with a
>> database in a chaotic state (both logically and possibly also
>> physically):
>> Your app wanted to transfer $200 from one account to another. The two
>> update statements are obviously protected inside a transaction. The
>> first statement succeeded, but the second was never executed. You
>> don't want a database in such state. This is only a very simple case
>> of why the log file is so important at database startup.
>> You might find stuff out there on commands that rebuilds the log.
>> These are not documented and were never meant to reach the public, so
>> I won't mention them here. Judging from above, you probably realize
>> what havoc such can cause to your database.
>> There are situations where SQL Server know that a database were
>> cleanly shutdown, and in such cases, sp_attach_single_file_db can be
>> used to rebuild the log. The prerequisites are documented, one of them
>> are that you actually detached the database first.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote in
>> message news:%231PDTRl3GHA.1252@.TK2MSFTNGP04.phx.gbl...
>>I am able to connect using OSQL. Thanks!
>> Can you tell me the best path to take now? Do I import the backups
>> onto the new server? What about the suspect DBs that have corrupt
>> logfiles? Can I attach those with just the mdf file?
>> I greatly appreciate your help. I often help out in other forums (AD,
>> Exchange, etc), but could use all the help I can get on this!
>> Thanks again,
>> Matthew
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.phx.gbl...
>>> If you can connect using EM (from anywhere), then you should be able
>>> to connect using OSQL or ISQL (which is the most forgiving tool).
>>> From there you can try the backup commands, and see if/what error
>>> message(s) are returned.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>>
>>>
>>> "Matthew Kitchin (Usenet/Lists)" <mkitchin.public@.gmail.com> wrote
>>> in message news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>>>I can connect with EM from a remote machine. The backup utility
>>>fails though. I can't run QA from anywhere.
>>>
>>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>>> news:4513DFC8.5080901@.realsqlguy.com...
>>> Matthew Kitchin (Usenet/Lists) wrote:
>>>> Thank you. Can you tell me how I can back them up without being
>>>> able run enterprise manager or query analyzer?
>>>>
>>>
>>> You said that 4 of the databases are suspect, but all of the
>>> others are up. How do you know this if you can't connect with EM
>>> or QA?
>>>
>>>
>>> --
>>> Tracy McKibben
>>> MCDBA
>>> http://www.realsqlguy.com
>>>
>>>
>>>
>>
>>
>>
>>
>