Monday, March 19, 2012

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 cop
y the db files and
attach. For the database you do get into the new system, run DBCC CHECKDB. F
or 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 messag
e
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 si
ngle SQL 2000 SP2 (on
> Win 2003) server with 26 DBs including the system DBs. They are mostly lig
ht use DBs (blackberry,
> rightfax, antivirus, WSUS, etc).
> My server had hardware failure 4 days ago, and it has led to a ton of prob
lems. The file systems
> are corrupt to the point checkdsk cannot fix them. Most applications on th
e 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 h
ave problems.
> I will have a new server on Monday. All but 4 of the DBs are up. 4 are sus
pect. they are suspect
> because their LDF files are corrupt. These files show as 0K and can't be c
opied, 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 onli
ne. I would like to move
> them, get everything up and running, and then worry about the rest. Can an
yone point me to some
> steps on moving the DBs that are functional, and then possibly attaching t
he MDF files of the DBs
> that aren't functional? Some Data loss is ok. Most of these DBs are fed th
eir information form
> other systems, and it can easily be updated. Because of the hardware failu
re, I don't really have
> good backups. I have them from 4 days ago, but I would prefer to use the f
resher 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...
>|||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 messag
e
news:%23dVTsAk3GHA.1292@.TK2MSFTNGP03.phx.gbl...
> Thank you. Can you tell me how I can back them up without being able run e
nterprise manager or
> query analyzer?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O7zpLJh3GHA.5024@.TK2MSFTNGP02.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:
> 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 conn
ect using OSQL or ISQL
(which is the most forgiving tool). From there you can try the backup comman
ds, 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 messag
e
news:O0E264k3GHA.4632@.TK2MSFTNGP03.phx.gbl...
>I can connect with EM from a remote machine. The backup utility fails thoug
h. I can't run QA from
>anywhere.
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:4513DFC8.508
0901@.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 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 eac
h 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 pa
th for the backup file.
Remember it is the service account that matters, not whatever account you ar
e 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. Othe
rwise 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 simpl
e 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 shut
down, and in such cases,
sp_attach_single_file_db can be used to rebuild the log. The prerequisites a
re 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 messag
e
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 th
e new server? What about
> the suspect DBs that have corrupt logfiles? Can I attach those with just t
he mdf file?
> I greatly appreciate your help. I often help out in other forums (AD, Exch
ange, 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 i
n message
> news:%23V1$KNl3GHA.4900@.TK2MSFTNGP03.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...
> Great!
>
> 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.
>
> They are most probably toast. Sorry.
>
> 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...
>

No comments:

Post a Comment