Migrating a single SQL2K Install to a SQL Clustered install.

Is it possible to to migrate a single standalone installation of SQL 2000
Enterprise Edition to be part of a clustered SQL installation. My initial
guess is that as SQL installs by default registering to the Server IP
address, and a clustered install requires its own IP address to be installed
under. Therefore I would have to unistall any SQL install and start from
scratch?
Thanks
Hi
You are correct, you first ahve to un-install and re-install. No other way.
Regards
Mike
"Siz Choudhury" wrote:

> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks
|||Be sure to back up all of your databases, so can restore them to the
cluster..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Siz Choudhury" <Siz Choudhury@.discussions.microsoft.com> wrote in message
news:E14A249A-0E2B-467D-80B6-BFCDE8B9C778@.microsoft.com...
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be
installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks
sql

Migrating a single SQL2K Install to a SQL Clustered install.

Is it possible to to migrate a single standalone installation of SQL 2000
Enterprise Edition to be part of a clustered SQL installation. My initial
guess is that as SQL installs by default registering to the Server IP
address, and a clustered install requires its own IP address to be installed
under. Therefore I would have to unistall any SQL install and start from
scratch?
ThanksHi
You are correct, you first ahve to un-install and re-install. No other way.
Regards
Mike
"Siz Choudhury" wrote:
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks|||Be sure to back up all of your databases, so can restore them to the
cluster..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Siz Choudhury" <Siz Choudhury@.discussions.microsoft.com> wrote in message
news:E14A249A-0E2B-467D-80B6-BFCDE8B9C778@.microsoft.com...
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be
installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks

Migrating a single SQL2K Install to a SQL Clustered install.

Is it possible to to migrate a single standalone installation of SQL 2000
Enterprise Edition to be part of a clustered SQL installation. My initial
guess is that as SQL installs by default registering to the Server IP
address, and a clustered install requires its own IP address to be installed
under. Therefore I would have to unistall any SQL install and start from
scratch?
ThanksHi
You are correct, you first ahve to un-install and re-install. No other way.
Regards
Mike
"Siz Choudhury" wrote:

> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be install
ed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks|||Be sure to back up all of your databases, so can restore them to the
cluster..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Siz Choudhury" <Siz Choudhury@.discussions.microsoft.com> wrote in message
news:E14A249A-0E2B-467D-80B6-BFCDE8B9C778@.microsoft.com...
> Is it possible to to migrate a single standalone installation of SQL 2000
> Enterprise Edition to be part of a clustered SQL installation. My initial
> guess is that as SQL installs by default registering to the Server IP
> address, and a clustered install requires its own IP address to be
installed
> under. Therefore I would have to unistall any SQL install and start from
> scratch?
> Thanks

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.sql

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Migrate from Single-Server to Cluster

I have a SharePoint installation that currently uses a single SQL Server and
connects to the default instance. I want to migrate to a cluster without
doing a full backup/move/restore. I'm thinking if I set up the cluster with
the same name as the current SQL server it should be transparent to
SharePoint. Has anyone tried this?
This is not a good idea, not even a great one. SPS can get picky. I would
due a true migration, with a tested backup/restore, then move the DB's.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Milan Gross" <MilanGross@.discussions.microsoft.com> wrote in message
news:19F19F3B-7721-4301-9303-B7ECF518B4A2@.microsoft.com...
>I have a SharePoint installation that currently uses a single SQL Server
>and
> connects to the default instance. I want to migrate to a cluster without
> doing a full backup/move/restore. I'm thinking if I set up the cluster
> with
> the same name as the current SQL server it should be transparent to
> SharePoint. Has anyone tried this?

Migrate from non-cluster to Cluster

I have a single SQL 2000 Enterprise server and I need to turn it into a
cluster. We have several applications using SQL, so the hostname and
configuration of the cluster must be the same as the existing single server.
The operating system is WS2003E and they are in a AD domain.
Is there a fairly easy migration path from a single to clustered
configuration, or do I have to setup two new servers and somehow get the
cluster to use the old hostname? Do I just do a SQL backup of the old
server, power it off, setup the cluster using the old hostname and then do a
SQL restore? I need to minimize downtime and I can't reconfigure the
applications to point to another SQL server.
Do I register the SPNs for the virtual hostname of the cluster?
Thanks,
Derek
Reusing the name is not really an option for several reasons (AD name
conflict, DNS name conflict, SQL cluster naming options). You can use DNS to
alias the old name to the new name.
While it is possible to migrate a non-clustered instance to a clustered
instance, I would recommend setting up a new system. That way if anything
goes wrong, your existing system is right there to pick up and run. Failure
isn't so painful. You can use manual log shipping or a simple log shipping
script to minimize downtime by getting your new server 'close' to the old
one. Be sure and plan the cutover process and test it (maybe in a smaller
scale using a couple of SQL instances on a test box).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Derek" <dseaman@.spawar.navy.mil> wrote in message
news:ep1mEXCyEHA.3584@.TK2MSFTNGP10.phx.gbl...
> I have a single SQL 2000 Enterprise server and I need to turn it into a
> cluster. We have several applications using SQL, so the hostname and
> configuration of the cluster must be the same as the existing single
server.
> The operating system is WS2003E and they are in a AD domain.
> Is there a fairly easy migration path from a single to clustered
> configuration, or do I have to setup two new servers and somehow get the
> cluster to use the old hostname? Do I just do a SQL backup of the old
> server, power it off, setup the cluster using the old hostname and then do
a
> SQL restore? I need to minimize downtime and I can't reconfigure the
> applications to point to another SQL server.
> Do I register the SPNs for the virtual hostname of the cluster?
> Thanks,
> Derek
>
>
|||How about if I backup the non-clustered server, remove it from AD, and built
up a cluster with the virtual name being the name of the old SQL server and
then do a SQL restore? We have too many apps to be changing the name, and I
hate to play tricks with DNS that are prone to human error.
Derek
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKaQfWDyEHA.1188@.tk2msftngp13.phx.gbl...
> Reusing the name is not really an option for several reasons (AD name
> conflict, DNS name conflict, SQL cluster naming options). You can use DNS
> to
> alias the old name to the new name.
> While it is possible to migrate a non-clustered instance to a clustered
> instance, I would recommend setting up a new system. That way if anything
> goes wrong, your existing system is right there to pick up and run.
> Failure
> isn't so painful. You can use manual log shipping or a simple log
> shipping
> script to minimize downtime by getting your new server 'close' to the old
> one. Be sure and plan the cutover process and test it (maybe in a smaller
> scale using a couple of SQL instances on a test box).
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Derek" <dseaman@.spawar.navy.mil> wrote in message
> news:ep1mEXCyEHA.3584@.TK2MSFTNGP10.phx.gbl...
> server.
> a
>
|||Try this in a test environment first.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Derek" <dseaman@.spawar.navy.mil> wrote in message
news:uyD4T%23czEHA.3336@.TK2MSFTNGP11.phx.gbl...
> How about if I backup the non-clustered server, remove it from AD, and
built
> up a cluster with the virtual name being the name of the old SQL server
and
> then do a SQL restore? We have too many apps to be changing the name, and
I[vbcol=seagreen]
> hate to play tricks with DNS that are prone to human error.
> Derek
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKaQfWDyEHA.1188@.tk2msftngp13.phx.gbl...
DNS[vbcol=seagreen]
anything[vbcol=seagreen]
old[vbcol=seagreen]
smaller[vbcol=seagreen]
the
>

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