Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise editio
n ?
I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
prompted the following message after a few step.
"To change existing instance of SQL2005 to a different edition of SQL2005,
you must run SQL2005 Setup from the command prompt and include the
SKUUPGRADE=1 parameter".
Followed the instruction to run setup with the parameter, upgraded
successfully but it got error when I started SSMS.
"could not load file or assembly Microsoft.Sqlserver.Management.Reports,
Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one o
f
its dependencies. The system could not find the file specified."
End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
Looking forward to your advices.Hi
"Johnny" wrote:
> Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edit
ion ?
> I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> prompted the following message after a few step.
> "To change existing instance of SQL2005 to a different edition of SQL2005,
> you must run SQL2005 Setup from the command prompt and include the
> SKUUPGRADE=1 parameter".
> Followed the instruction to run setup with the parameter, upgraded
> successfully but it got error when I started SSMS.
> "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one
of
> its dependencies. The system could not find the file specified."
> End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> Looking forward to your advices.
You don't say what options you used on the command prompt, at a guess you
didn't specify UPGRADE=SQL_Engine as shown in the example on
http://msdn2.microsoft.com/en-us/li...aspx#skuupgrade and have
also upgraded the tools as well, which for some reason didn't work?
You could have re-installed the tools on their own using the reinstallmode
parameter
http://msdn2.microsoft.com/en-us/li...x#reinstallmode and the
ADDLOCAL parameter set to the Client_components
You will now need to re-apply service pack 2 and subsequent hotfixes.
John|||Hi John,
I wish to clarify that upgrading the edition has to specify
UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
the client tools is no need for the edition upgrade (only sql_engine is
required.)
Johnny
"John Bell" wrote:
> Hi
> "Johnny" wrote:
>
> You don't say what options you used on the command prompt, at a guess you
> didn't specify UPGRADE=SQL_Engine as shown in the example on
> http://msdn2.microsoft.com/en-us/li...aspx#skuupgrade and have
> also upgraded the tools as well, which for some reason didn't work?
> You could have re-installed the tools on their own using the reinstallmode
> parameter
> http://msdn2.microsoft.com/en-us/li...x#reinstallmode and t
he
> ADDLOCAL parameter set to the Client_components
> You will now need to re-apply service pack 2 and subsequent hotfixes.
> John|||Hi
"Johnny" wrote:
> Hi John,
> I wish to clarify that upgrading the edition has to specify
> UPGRADE=SQL_ENGINE as well which I did not specify before. That means tha
t
> the client tools is no need for the edition upgrade (only sql_engine is
> required.)
> Johnny
> "John Bell" wrote:
Had you service packed the original installation, and were they re-applied
after the upgrade?
John|||Applied the service pack 2 before and after the edition upgrade.
Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
well for edition upgrade, isn't it ?
Ta.
"John Bell" wrote:
> Hi
> "Johnny" wrote:
>
> Had you service packed the original installation, and were they re-applied
> after the upgrade?
> John
>|||Hi
"Johnny" wrote:
> Applied the service pack 2 before and after the edition upgrade.
> Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
> well for edition upgrade, isn't it ?
> Ta.
>
I believe so, but I have not found anywhere to confirm this.
John
Showing posts with label sp2. Show all posts
Showing posts with label sp2. Show all posts
Friday, March 23, 2012
Migrate SQL2005 from Standard to Enterprise edition
Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
prompted the following message after a few step.
"To change existing instance of SQL2005 to a different edition of SQL2005,
you must run SQL2005 Setup from the command prompt and include the
SKUUPGRADE=1 parameter".
Followed the instruction to run setup with the parameter, upgraded
successfully but it got error when I started SSMS.
"could not load file or assembly Microsoft.Sqlserver.Management.Reports,
Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
its dependencies. The system could not find the file specified."
End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
Looking forward to your advices.
Hi
"Johnny" wrote:
> Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
> I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> prompted the following message after a few step.
> "To change existing instance of SQL2005 to a different edition of SQL2005,
> you must run SQL2005 Setup from the command prompt and include the
> SKUUPGRADE=1 parameter".
> Followed the instruction to run setup with the parameter, upgraded
> successfully but it got error when I started SSMS.
> "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
> its dependencies. The system could not find the file specified."
> End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> Looking forward to your advices.
You don't say what options you used on the command prompt, at a guess you
didn't specify UPGRADE=SQL_Engine as shown in the example on
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
also upgraded the tools as well, which for some reason didn't work?
You could have re-installed the tools on their own using the reinstallmode
parameter
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
ADDLOCAL parameter set to the Client_components
You will now need to re-apply service pack 2 and subsequent hotfixes.
John
|||Hi John,
I wish to clarify that upgrading the edition has to specify
UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
the client tools is no need for the edition upgrade (only sql_engine is
required.)
Johnny
"John Bell" wrote:
> Hi
> "Johnny" wrote:
>
> You don't say what options you used on the command prompt, at a guess you
> didn't specify UPGRADE=SQL_Engine as shown in the example on
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
> also upgraded the tools as well, which for some reason didn't work?
> You could have re-installed the tools on their own using the reinstallmode
> parameter
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
> ADDLOCAL parameter set to the Client_components
> You will now need to re-apply service pack 2 and subsequent hotfixes.
> John
|||Hi
"Johnny" wrote:
> Hi John,
> I wish to clarify that upgrading the edition has to specify
> UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
> the client tools is no need for the edition upgrade (only sql_engine is
> required.)
> Johnny
> "John Bell" wrote:
Had you service packed the original installation, and were they re-applied
after the upgrade?
John
|||Applied the service pack 2 before and after the edition upgrade.
Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
well for edition upgrade, isn't it ?
Ta.
"John Bell" wrote:
> Hi
> "Johnny" wrote:
>
> Had you service packed the original installation, and were they re-applied
> after the upgrade?
> John
>
|||Hi
"Johnny" wrote:
> Applied the service pack 2 before and after the edition upgrade.
> Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
> well for edition upgrade, isn't it ?
> Ta.
>
I believe so, but I have not found anywhere to confirm this.
John
I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
prompted the following message after a few step.
"To change existing instance of SQL2005 to a different edition of SQL2005,
you must run SQL2005 Setup from the command prompt and include the
SKUUPGRADE=1 parameter".
Followed the instruction to run setup with the parameter, upgraded
successfully but it got error when I started SSMS.
"could not load file or assembly Microsoft.Sqlserver.Management.Reports,
Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
its dependencies. The system could not find the file specified."
End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
Looking forward to your advices.
Hi
"Johnny" wrote:
> Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
> I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> prompted the following message after a few step.
> "To change existing instance of SQL2005 to a different edition of SQL2005,
> you must run SQL2005 Setup from the command prompt and include the
> SKUUPGRADE=1 parameter".
> Followed the instruction to run setup with the parameter, upgraded
> successfully but it got error when I started SSMS.
> "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
> its dependencies. The system could not find the file specified."
> End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> Looking forward to your advices.
You don't say what options you used on the command prompt, at a guess you
didn't specify UPGRADE=SQL_Engine as shown in the example on
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
also upgraded the tools as well, which for some reason didn't work?
You could have re-installed the tools on their own using the reinstallmode
parameter
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
ADDLOCAL parameter set to the Client_components
You will now need to re-apply service pack 2 and subsequent hotfixes.
John
|||Hi John,
I wish to clarify that upgrading the edition has to specify
UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
the client tools is no need for the edition upgrade (only sql_engine is
required.)
Johnny
"John Bell" wrote:
> Hi
> "Johnny" wrote:
>
> You don't say what options you used on the command prompt, at a guess you
> didn't specify UPGRADE=SQL_Engine as shown in the example on
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
> also upgraded the tools as well, which for some reason didn't work?
> You could have re-installed the tools on their own using the reinstallmode
> parameter
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
> ADDLOCAL parameter set to the Client_components
> You will now need to re-apply service pack 2 and subsequent hotfixes.
> John
|||Hi
"Johnny" wrote:
> Hi John,
> I wish to clarify that upgrading the edition has to specify
> UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
> the client tools is no need for the edition upgrade (only sql_engine is
> required.)
> Johnny
> "John Bell" wrote:
Had you service packed the original installation, and were they re-applied
after the upgrade?
John
|||Applied the service pack 2 before and after the edition upgrade.
Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
well for edition upgrade, isn't it ?
Ta.
"John Bell" wrote:
> Hi
> "Johnny" wrote:
>
> Had you service packed the original installation, and were they re-applied
> after the upgrade?
> John
>
|||Hi
"Johnny" wrote:
> Applied the service pack 2 before and after the edition upgrade.
> Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
> well for edition upgrade, isn't it ?
> Ta.
>
I believe so, but I have not found anywhere to confirm this.
John
Migrate SQL2005 from Standard to Enterprise edition
Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
prompted the following message after a few step.
"To change existing instance of SQL2005 to a different edition of SQL2005,
you must run SQL2005 Setup from the command prompt and include the
SKUUPGRADE=1 parameter".
Followed the instruction to run setup with the parameter, upgraded
successfully but it got error when I started SSMS.
"could not load file or assembly Microsoft.Sqlserver.Management.Reports,
Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
its dependencies. The system could not find the file specified."
End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
Looking forward to your advices.Hi
"Johnny" wrote:
> Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
> I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> prompted the following message after a few step.
> "To change existing instance of SQL2005 to a different edition of SQL2005,
> you must run SQL2005 Setup from the command prompt and include the
> SKUUPGRADE=1 parameter".
> Followed the instruction to run setup with the parameter, upgraded
> successfully but it got error when I started SSMS.
> "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
> its dependencies. The system could not find the file specified."
> End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> Looking forward to your advices.
You don't say what options you used on the command prompt, at a guess you
didn't specify UPGRADE=SQL_Engine as shown in the example on
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
also upgraded the tools as well, which for some reason didn't work?
You could have re-installed the tools on their own using the reinstallmode
parameter
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
ADDLOCAL parameter set to the Client_components
You will now need to re-apply service pack 2 and subsequent hotfixes.
John|||Hi John,
I wish to clarify that upgrading the edition has to specify
UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
the client tools is no need for the edition upgrade (only sql_engine is
required.)
Johnny
"John Bell" wrote:
> Hi
> "Johnny" wrote:
> > Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
> >
> > I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> > prompted the following message after a few step.
> >
> > "To change existing instance of SQL2005 to a different edition of SQL2005,
> > you must run SQL2005 Setup from the command prompt and include the
> > SKUUPGRADE=1 parameter".
> >
> > Followed the instruction to run setup with the parameter, upgraded
> > successfully but it got error when I started SSMS.
> >
> > "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> > Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
> > its dependencies. The system could not find the file specified."
> >
> > End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> > Looking forward to your advices.
> You don't say what options you used on the command prompt, at a guess you
> didn't specify UPGRADE=SQL_Engine as shown in the example on
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
> also upgraded the tools as well, which for some reason didn't work?
> You could have re-installed the tools on their own using the reinstallmode
> parameter
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
> ADDLOCAL parameter set to the Client_components
> You will now need to re-apply service pack 2 and subsequent hotfixes.
> John|||Hi
"Johnny" wrote:
> Hi John,
> I wish to clarify that upgrading the edition has to specify
> UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
> the client tools is no need for the edition upgrade (only sql_engine is
> required.)
> Johnny
> "John Bell" wrote:
Had you service packed the original installation, and were they re-applied
after the upgrade?
John|||Applied the service pack 2 before and after the edition upgrade.
Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
well for edition upgrade, isn't it ?
Ta.
"John Bell" wrote:
> Hi
> "Johnny" wrote:
> > Hi John,
> >
> > I wish to clarify that upgrading the edition has to specify
> > UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
> > the client tools is no need for the edition upgrade (only sql_engine is
> > required.)
> >
> > Johnny
> > "John Bell" wrote:
> Had you service packed the original installation, and were they re-applied
> after the upgrade?
> John
>|||Hi
"Johnny" wrote:
> Applied the service pack 2 before and after the edition upgrade.
> Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
> well for edition upgrade, isn't it ?
> Ta.
>
I believe so, but I have not found anywhere to confirm this.
Johnsql
I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
prompted the following message after a few step.
"To change existing instance of SQL2005 to a different edition of SQL2005,
you must run SQL2005 Setup from the command prompt and include the
SKUUPGRADE=1 parameter".
Followed the instruction to run setup with the parameter, upgraded
successfully but it got error when I started SSMS.
"could not load file or assembly Microsoft.Sqlserver.Management.Reports,
Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
its dependencies. The system could not find the file specified."
End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
Looking forward to your advices.Hi
"Johnny" wrote:
> Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
> I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> prompted the following message after a few step.
> "To change existing instance of SQL2005 to a different edition of SQL2005,
> you must run SQL2005 Setup from the command prompt and include the
> SKUUPGRADE=1 parameter".
> Followed the instruction to run setup with the parameter, upgraded
> successfully but it got error when I started SSMS.
> "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
> its dependencies. The system could not find the file specified."
> End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> Looking forward to your advices.
You don't say what options you used on the command prompt, at a guess you
didn't specify UPGRADE=SQL_Engine as shown in the example on
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
also upgraded the tools as well, which for some reason didn't work?
You could have re-installed the tools on their own using the reinstallmode
parameter
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
ADDLOCAL parameter set to the Client_components
You will now need to re-apply service pack 2 and subsequent hotfixes.
John|||Hi John,
I wish to clarify that upgrading the edition has to specify
UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
the client tools is no need for the edition upgrade (only sql_engine is
required.)
Johnny
"John Bell" wrote:
> Hi
> "Johnny" wrote:
> > Has anyone tried to migrate SQL2005 from Standard (SP2) to Enterprise edition ?
> >
> > I have trouble with it. I loaded the SQL2005 Enterprise edition CD, it
> > prompted the following message after a few step.
> >
> > "To change existing instance of SQL2005 to a different edition of SQL2005,
> > you must run SQL2005 Setup from the command prompt and include the
> > SKUUPGRADE=1 parameter".
> >
> > Followed the instruction to run setup with the parameter, upgraded
> > successfully but it got error when I started SSMS.
> >
> > "could not load file or assembly Microsoft.Sqlserver.Management.Reports,
> > Version=9.0.242.0, Culture=Nature, PublicKeyToken=89845dcd8080cc91' or one of
> > its dependencies. The system could not find the file specified."
> >
> > End up, I got to uninstall SQL2005 and reinstall with Enterprise Edition.
> > Looking forward to your advices.
> You don't say what options you used on the command prompt, at a guess you
> didn't specify UPGRADE=SQL_Engine as shown in the example on
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade and have
> also upgraded the tools as well, which for some reason didn't work?
> You could have re-installed the tools on their own using the reinstallmode
> parameter
> http://msdn2.microsoft.com/en-us/library/ms144259.aspx#reinstallmode and the
> ADDLOCAL parameter set to the Client_components
> You will now need to re-apply service pack 2 and subsequent hotfixes.
> John|||Hi
"Johnny" wrote:
> Hi John,
> I wish to clarify that upgrading the edition has to specify
> UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
> the client tools is no need for the edition upgrade (only sql_engine is
> required.)
> Johnny
> "John Bell" wrote:
Had you service packed the original installation, and were they re-applied
after the upgrade?
John|||Applied the service pack 2 before and after the edition upgrade.
Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
well for edition upgrade, isn't it ?
Ta.
"John Bell" wrote:
> Hi
> "Johnny" wrote:
> > Hi John,
> >
> > I wish to clarify that upgrading the edition has to specify
> > UPGRADE=SQL_ENGINE as well which I did not specify before. That means that
> > the client tools is no need for the edition upgrade (only sql_engine is
> > required.)
> >
> > Johnny
> > "John Bell" wrote:
> Had you service packed the original installation, and were they re-applied
> after the upgrade?
> John
>|||Hi
"Johnny" wrote:
> Applied the service pack 2 before and after the edition upgrade.
> Back to my previous question. I needed to specify UPGRADE=SQL_ENGINE as
> well for edition upgrade, isn't it ?
> Ta.
>
I believe so, but I have not found anywhere to confirm this.
Johnsql
Wednesday, March 21, 2012
Migrate Higher version of MDAC
Hi Friends,
I have a system with windows 2000 advanced server SP4 OS. In that system, How can i upgrade MDAC version from 2.5 to 2.8 sp2.
This is use of,
I am going to install sql server 2005 express edition advanced services SP2.
Please help me, how can i upgrade higher version of MDAC?
Thanks in Advance
Rameshkumar Thirumalaisamy
You just download the version you want from the Microsoft Download Center and install it. I believe that MDAC 2.8 is currently at SP1, not SP2 as you've indicated. You can find MDAC 2.8 SP1 here.
Mike
|||Actually, if you install the SQL Server 2005 on the machine you can connect using the SNAC provider instead of using the MDAC stack. Although you should be up-to-date in the case another application needs parts of the MDAC stack.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||When installing SQL Server 2005 (any edition) on Windows 2000 there actually is a minimum version of MDAC that is required, although I can't recall what version is required, but 2.8 SP1 will certainly qualify since it's the newest. I don't know why this requirement exists only on Windows 2000.
Mike
Migrate Higher version of MDAC
Hi Friends,
I have a system with windows 2000 advanced server SP4 OS. In that system, How can i upgrade MDAC version from 2.5 to 2.8 sp2.
This is use of,
I am going to install sql server 2005 express edition advanced services SP2.
Please help me, how can i upgrade higher version of MDAC?
Thanks in Advance
Rameshkumar Thirumalaisamy
You just download the version you want from the Microsoft Download Center and install it. I believe that MDAC 2.8 is currently at SP1, not SP2 as you've indicated. You can find MDAC 2.8 SP1 here.
Mike
|||Actually, if you install the SQL Server 2005 on the machine you can connect using the SNAC provider instead of using the MDAC stack. Although you should be up-to-date in the case another application needs parts of the MDAC stack.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||When installing SQL Server 2005 (any edition) on Windows 2000 there actually is a minimum version of MDAC that is required, although I can't recall what version is required, but 2.8 SP1 will certainly qualify since it's the newest. I don't know why this requirement exists only on Windows 2000.
Mike
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 Kitchin
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
>
|||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 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...
>
|||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 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...
>
|||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 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...
>
|||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...
>
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
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
>
|||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 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...
>
|||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 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...
>
|||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 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...
>
|||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...
>
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...
>
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...
>
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
>>>
>>>
>>>
>>
>>
>>
>>
>
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
>>>
>>>
>>>
>>
>>
>>
>>
>
Wednesday, March 7, 2012
Microsoft Visual Studio is unable to load this document after SQL Server SP2 install.
Hello all,
Over the weekend, I loaded the SQL Server SP2 on to my production database after having it on my dev server for two weeks with out any problems.
After I did this, I went to check all my SSIS packages and found them not able to load.
I get this message “Microsoft Visual Studio is unable to load this document: The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors”. This occurs when CPackage::LoadFromXML fails.
Now when I look at the errors I see:
Error 1 Error loading Master_Full_Weekly.dtsx: Error loading value "<DTS:LogProvider xmlns : DTS="www.microsoft.com/SqlServer/Dts" > <DTS : Property DTS:Name="ConfigString">SQL_TPMTSH_Datamart</DTS: Property><DTS : Property DTS:Name="DelayValidation">0</DTS: Property><DTS: Property DTS:Name="ObjectName">DTS Log Provider for SQL Serve" from node "DTS: LogProvider". D:\ETL\Projects\TPM\TPMTSH\Master_Full_Weekly.dtsx 1 1
Does anyone know what this is about and how I can go about fixing it?Anyone?|||
Could you try to register xml modules:
regsvr32 msxml3.dll
regsvr32 msxml6.dll
HTH.
Friday, February 24, 2012
Microsoft user group
Sorry for this post but can anyone direct me to Windows XP
Home edition user group ? I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks.....
Users group or newsgroup?
For newsgroups, try:
microsoft.public.windowsxp.help_and_support
-Sue
On Mon, 15 Nov 2004 11:20:44 -0800, "Dan"
<anonymous@.discussions.microsoft.com> wrote:
>Sorry for this post but can anyone direct me to Windows XP
>Home edition user group ? I Think I locked myself out of
>my computer unless Installing SP2 messed me up which is
>not installed at all......
>Thanks.....
Home edition user group ? I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks.....
Users group or newsgroup?
For newsgroups, try:
microsoft.public.windowsxp.help_and_support
-Sue
On Mon, 15 Nov 2004 11:20:44 -0800, "Dan"
<anonymous@.discussions.microsoft.com> wrote:
>Sorry for this post but can anyone direct me to Windows XP
>Home edition user group ? I Think I locked myself out of
>my computer unless Installing SP2 messed me up which is
>not installed at all......
>Thanks.....
Microsoft user group
Sorry for this post but can anyone direct me to Windows XP
Home edition user group ? I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks.....
"Dan" <anonymous@.discussions.microsoft.com> wrote in news:5be201c4cb48
$325c2540$a401280a@.phx.gbl:
> Sorry for this post but can anyone direct me to Windows XP
> Home edition user group ? I Think I locked myself out of
> my computer unless Installing SP2 messed me up which is
> not installed at all......
> Thanks.....
>
alt.os.windows-xp
Home edition user group ? I Think I locked myself out of
my computer unless Installing SP2 messed me up which is
not installed at all......
Thanks.....
"Dan" <anonymous@.discussions.microsoft.com> wrote in news:5be201c4cb48
$325c2540$a401280a@.phx.gbl:
> Sorry for this post but can anyone direct me to Windows XP
> Home edition user group ? I Think I locked myself out of
> my computer unless Installing SP2 messed me up which is
> not installed at all......
> Thanks.....
>
alt.os.windows-xp
Microsoft Update & SP1
Hi,
I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pro
+ SP2 by using the Microsoft Update service.
No issues were noted during the upgrade process, and EXEC master..xp_msver
returns 9.00.2047.00 in the ProductVersion column.
However, subsequent running of Microsoft Update still shows SQL Server 2005
SP1 (KB913090) as needing to be installed.
Is this a bug with Microsoft Update, or does it mean that SP1 isn't
installed properly?
Any assistance gratefully received.
MarkHi Mark
It looks like you are on SP1 on that instance! Do you have multiple
instances on this machine? Did you check the installation's log files
(hopefully this is going to be in the same place as a manual installation!).
Have you rebooted since the upgrade?
If you are confident that it is upgraded then you could choose the option to
not show the update again when the update details are expanded.
John
"Mark Rae" wrote:
> Hi,
> I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pr
o
> + SP2 by using the Microsoft Update service.
> No issues were noted during the upgrade process, and EXEC master..xp_msver
> returns 9.00.2047.00 in the ProductVersion column.
> However, subsequent running of Microsoft Update still shows SQL Server 200
5
> SP1 (KB913090) as needing to be installed.
> Is this a bug with Microsoft Update, or does it mean that SP1 isn't
> installed properly?
> Any assistance gratefully received.
> Mark
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:34B375F1-9334-4141-B282-2FB06C8D4A44@.microsoft.com...
> Do you have multiple instances on this machine?
No.
> Did you check the installation's log files (hopefully this is going to be
> in the same place as a manual installation!).
Yes.
> Have you rebooted since the upgrade?
Yes. And reapplied the upgrade. And rebooted after that.
> If you are confident that it is upgraded
I'm as confident as I can be, I suppose. Other than master..xp_msver /
SELECT @.@.VERSION, is there anything else I can try...?
> then you could choose the option to not show the update again
> when the update details are expanded.
Yes, but I was loathe to do that until I'd at least asked in here. I've done
a Google search, and there doesn't seem to be anything about this specific
issue. Lots about SP1, of course, but nothing relating to problems with
applying it through Microsoft Update.
I should receive SP1 on DVD any day now as part of my MSDN Universal
subscription, so I'll reinstall it from that and then remove the option from
Microsoft Update if that doesn't fix it.
I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pro
+ SP2 by using the Microsoft Update service.
No issues were noted during the upgrade process, and EXEC master..xp_msver
returns 9.00.2047.00 in the ProductVersion column.
However, subsequent running of Microsoft Update still shows SQL Server 2005
SP1 (KB913090) as needing to be installed.
Is this a bug with Microsoft Update, or does it mean that SP1 isn't
installed properly?
Any assistance gratefully received.
MarkHi Mark
It looks like you are on SP1 on that instance! Do you have multiple
instances on this machine? Did you check the installation's log files
(hopefully this is going to be in the same place as a manual installation!).
Have you rebooted since the upgrade?
If you are confident that it is upgraded then you could choose the option to
not show the update again when the update details are expanded.
John
"Mark Rae" wrote:
> Hi,
> I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pr
o
> + SP2 by using the Microsoft Update service.
> No issues were noted during the upgrade process, and EXEC master..xp_msver
> returns 9.00.2047.00 in the ProductVersion column.
> However, subsequent running of Microsoft Update still shows SQL Server 200
5
> SP1 (KB913090) as needing to be installed.
> Is this a bug with Microsoft Update, or does it mean that SP1 isn't
> installed properly?
> Any assistance gratefully received.
> Mark
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:34B375F1-9334-4141-B282-2FB06C8D4A44@.microsoft.com...
> Do you have multiple instances on this machine?
No.
> Did you check the installation's log files (hopefully this is going to be
> in the same place as a manual installation!).
Yes.
> Have you rebooted since the upgrade?
Yes. And reapplied the upgrade. And rebooted after that.
> If you are confident that it is upgraded
I'm as confident as I can be, I suppose. Other than master..xp_msver /
SELECT @.@.VERSION, is there anything else I can try...?
> then you could choose the option to not show the update again
> when the update details are expanded.
Yes, but I was loathe to do that until I'd at least asked in here. I've done
a Google search, and there doesn't seem to be anything about this specific
issue. Lots about SP1, of course, but nothing relating to problems with
applying it through Microsoft Update.
I should receive SP1 on DVD any day now as part of my MSDN Universal
subscription, so I'll reinstall it from that and then remove the option from
Microsoft Update if that doesn't fix it.
Microsoft Update & SP1
Hi,
I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pro
+ SP2 by using the Microsoft Update service.
No issues were noted during the upgrade process, and EXEC master..xp_msver
returns 9.00.2047.00 in the ProductVersion column.
However, subsequent running of Microsoft Update still shows SQL Server 2005
SP1 (KB913090) as needing to be installed.
Is this a bug with Microsoft Update, or does it mean that SP1 isn't
installed properly?
Any assistance gratefully received.
MarkHi Mark
It looks like you are on SP1 on that instance! Do you have multiple
instances on this machine? Did you check the installation's log files
(hopefully this is going to be in the same place as a manual installation!).
Have you rebooted since the upgrade?
If you are confident that it is upgraded then you could choose the option to
not show the update again when the update details are expanded.
John
"Mark Rae" wrote:
> Hi,
> I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pro
> + SP2 by using the Microsoft Update service.
> No issues were noted during the upgrade process, and EXEC master..xp_msver
> returns 9.00.2047.00 in the ProductVersion column.
> However, subsequent running of Microsoft Update still shows SQL Server 2005
> SP1 (KB913090) as needing to be installed.
> Is this a bug with Microsoft Update, or does it mean that SP1 isn't
> installed properly?
> Any assistance gratefully received.
> Mark
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:34B375F1-9334-4141-B282-2FB06C8D4A44@.microsoft.com...
> Do you have multiple instances on this machine?
No.
> Did you check the installation's log files (hopefully this is going to be
> in the same place as a manual installation!).
Yes.
> Have you rebooted since the upgrade?
Yes. And reapplied the upgrade. And rebooted after that.
> If you are confident that it is upgraded
I'm as confident as I can be, I suppose. Other than master..xp_msver /
SELECT @.@.VERSION, is there anything else I can try...?
> then you could choose the option to not show the update again
> when the update details are expanded.
Yes, but I was loathe to do that until I'd at least asked in here. I've done
a Google search, and there doesn't seem to be anything about this specific
issue. Lots about SP1, of course, but nothing relating to problems with
applying it through Microsoft Update.
I should receive SP1 on DVD any day now as part of my MSDN Universal
subscription, so I'll reinstall it from that and then remove the option from
Microsoft Update if that doesn't fix it.
I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pro
+ SP2 by using the Microsoft Update service.
No issues were noted during the upgrade process, and EXEC master..xp_msver
returns 9.00.2047.00 in the ProductVersion column.
However, subsequent running of Microsoft Update still shows SQL Server 2005
SP1 (KB913090) as needing to be installed.
Is this a bug with Microsoft Update, or does it mean that SP1 isn't
installed properly?
Any assistance gratefully received.
MarkHi Mark
It looks like you are on SP1 on that instance! Do you have multiple
instances on this machine? Did you check the installation's log files
(hopefully this is going to be in the same place as a manual installation!).
Have you rebooted since the upgrade?
If you are confident that it is upgraded then you could choose the option to
not show the update again when the update details are expanded.
John
"Mark Rae" wrote:
> Hi,
> I recently upgraded to SQL Server 2005 SP1 (developer edition) on WinXP Pro
> + SP2 by using the Microsoft Update service.
> No issues were noted during the upgrade process, and EXEC master..xp_msver
> returns 9.00.2047.00 in the ProductVersion column.
> However, subsequent running of Microsoft Update still shows SQL Server 2005
> SP1 (KB913090) as needing to be installed.
> Is this a bug with Microsoft Update, or does it mean that SP1 isn't
> installed properly?
> Any assistance gratefully received.
> Mark
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:34B375F1-9334-4141-B282-2FB06C8D4A44@.microsoft.com...
> Do you have multiple instances on this machine?
No.
> Did you check the installation's log files (hopefully this is going to be
> in the same place as a manual installation!).
Yes.
> Have you rebooted since the upgrade?
Yes. And reapplied the upgrade. And rebooted after that.
> If you are confident that it is upgraded
I'm as confident as I can be, I suppose. Other than master..xp_msver /
SELECT @.@.VERSION, is there anything else I can try...?
> then you could choose the option to not show the update again
> when the update details are expanded.
Yes, but I was loathe to do that until I'd at least asked in here. I've done
a Google search, and there doesn't seem to be anything about this specific
issue. Lots about SP1, of course, but nothing relating to problems with
applying it through Microsoft Update.
I should receive SP1 on DVD any day now as part of my MSDN Universal
subscription, so I'll reinstall it from that and then remove the option from
Microsoft Update if that doesn't fix it.
Subscribe to:
Posts (Atom)