Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be the
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:
>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > Hi.
> >
> > I am in the process of "ramping-up" (read: spending more money)
> > on a home project. To date, I have done the web and database
> > development on the same machine. I have been advised to put the
> > DB stuff on its own box. My question is, what kind of box should this
> > be? What are the essentials when buying hardware for a DB connection
> > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > etc).
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> >
> > Thanks for any tips and advice you have.
> >
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Waiting for more infos about your database, i suggest another point of view:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server,
> it's strongly recommended to protect the dbserver (this probably would be the
> meaning of "...its own box"). At the box should added security (firewalling
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
> > "pbd22" <dush...@.gmail.com> wrote in message
> >news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > > Hi.
> > > I am in the process of "ramping-up" (read: spending more money)
> > > on a home project. To date, I have done the web and database
> > > development on the same machine. I have been advised to put the
> > > DB stuff on its own box. My question is, what kind of box should this
> > > be? What are the essentials when buying hardware for a DB connection
> > > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > > etc).
> > "It depends" a lot on the nature of your data.
> > However, databases tend to like lots of RAM and fast disk channels.
> > I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> > as 10, or 5/50 for data) for data and logs.
> > And as much RAM as your version of SQL Server can use.
> > But beyond that, impossible to really advise w/o knowing more about your
> > database setup.
> > > Thanks for any tips and advice you have.
> > --
> > Greg Moore
> > SQL Server DBA Consulting Remote and Onsite available!
> > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:
> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > Waiting for more infos about your database, i suggest another point of view:
> > I suppose that "home project" means something developed for your pleasure.
> > If not, and especially if you intend expose on the Network your Web Server,
> > it's strongly recommended to protect the dbserver (this probably would be the
> > meaning of "...its own box"). At the box should added security (firewalling
> > and so on...)
> > Gilberto
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >
> >
> > > "pbd22" <dush...@.gmail.com> wrote in message
> > >news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > > > Hi.
> >
> > > > I am in the process of "ramping-up" (read: spending more money)
> > > > on a home project. To date, I have done the web and database
> > > > development on the same machine. I have been advised to put the
> > > > DB stuff on its own box. My question is, what kind of box should this
> > > > be? What are the essentials when buying hardware for a DB connection
> > > > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > > > etc).
> >
> > > "It depends" a lot on the nature of your data.
> >
> > > However, databases tend to like lots of RAM and fast disk channels.
> >
> > > I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> > > as 10, or 5/50 for data) for data and logs.
> >
> > > And as much RAM as your version of SQL Server can use.
> >
> > > But beyond that, impossible to really advise w/o knowing more about your
> > > database setup.
> >
> > > > Thanks for any tips and advice you have.
> >
> > > --
> > > Greg Moore
> > > SQL Server DBA Consulting Remote and Onsite available!
> > > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Wednesday, March 21, 2012
Database Server Hardware Architecture
Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be th
e
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:
>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> Waiting for more infos about your database, i suggest another point of vie
w:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server
,
> it's strongly recommended to protect the dbserver (this probably would be
the
> meaning of "...its own box"). At the box should added security (firewallin
g
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
>
>
>
>
>
>
>
>
>
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:
> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be th
e
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:
>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> Waiting for more infos about your database, i suggest another point of vie
w:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server
,
> it's strongly recommended to protect the dbserver (this probably would be
the
> meaning of "...its own box"). At the box should added security (firewallin
g
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
>
>
>
>
>
>
>
>
>
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:
> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.
Database Server Hardware Architecture
Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have.
"pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegro ups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have.
"pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegro ups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.
Wednesday, March 7, 2012
database restore date
How can I find when current database was restored?The restorehistory table (in msdb database) contains one row for each
restore operation.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Vodochnik" <vodochnik@.list.ru> wrote in message
news:8a63d75a.0306301506.1d86f9f0@.posting.google.com...
How can I find when current database was restored?
restore operation.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Vodochnik" <vodochnik@.list.ru> wrote in message
news:8a63d75a.0306301506.1d86f9f0@.posting.google.com...
How can I find when current database was restored?
Saturday, February 25, 2012
Database recovery with data file only
I had database whose log file and backup files are unavailable due to a media failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message â'Device Activation error. Physical file name â'C:\...\xxx.ldfâ' may be incorrect.
Please advice me if anything can be done.
Thanking You"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net...
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
--
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> >
>|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > A word of warning regarding the technique proposed by Hari. Forcibly
> > rebuilding the transaction log results in a database with questionable
> > integrity. Data may be physically corrupt or logically inconsistent
> because
> > normal database recovery did not take place.
> >
> > A preferable method is to restore from backup. If the log must be
rebuilt
> > because no backup is available, I suggest data be exported and then
> imported
> > into a clean database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > I had database whose log file and backup files are unavailable due to
a
> > media failure. I had the data file which is up to date.
> > >
> > > Is it possible to recover the database?
> > >
> > > I had tried using attach database utility but failed with an error
> message
> > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > incorrect.
> > >
> > > Please advice me if anything can be done.
> > >
> > > Thanking You
> > >
> >
> >
>|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> > Hi Dan,
> >
> > I accept what you say regarding data integrity.
> > I suggested /recommended this method only because ken do not have the
> > database Backup as well as
> > no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> > approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that
database
> is
> > fine or not.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > > A word of warning regarding the technique proposed by Hari. Forcibly
> > > rebuilding the transaction log results in a database with questionable
> > > integrity. Data may be physically corrupt or logically inconsistent
> > because
> > > normal database recovery did not take place.
> > >
> > > A preferable method is to restore from backup. If the log must be
> rebuilt
> > > because no backup is available, I suggest data be exported and then
> > imported
> > > into a clean database.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > > I had database whose log file and backup files are unavailable due
to
> a
> > > media failure. I had the data file which is up to date.
> > > >
> > > > Is it possible to recover the database?
> > > >
> > > > I had tried using attach database utility but failed with an error
> > message
> > > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > > incorrect.
> > > >
> > > > Please advice me if anything can be done.
> > > >
> > > > Thanking You
> > > >
> > >
> > >
> >
> >
>|||There are many approaches that seem "easier and faster" but
they aren't necessarily good ideas and can actually not
really be "easier and faster" in the long run.
Note Paul's response. I remembered that Sybase used to (or
still does, I don't know) have the command and if it failed
once or twice, you essentially ended up with a useless data
file and couldn't execute the command anymore. There are
even easier sql commands posted up here that users have
problems getting right the first or second time - and all of
us have had those days where typing a simple select doesn't
work. For those reasons, it's probably better for a user to
call support and have someone from PSS walk them through the
process carefully. Ever since it's been posted on
newsgroups, I've seen it abused and misused by companies.
If they end up with nothing but a useless data file, it may
have actually have been "easier and faster" for them to get
the backups read off the failed media from a company that
specializes in that and restore the database from those
files.
-Sue
On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
<hari_prasad_k@.hotmail.com> wrote:
>Hi Dan,
>I accept what you say regarding data integrity.
>I suggested /recommended this method only because ken do not have the
>database Backup as well as
>no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
>approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database is
>fine or not.|||One more note - the command (not the functionality) has been removed in SQL
Server 2005. Also, in SQL Server 2005, the fact that the functionality was
used is persisted permanently in the database so PSS can tell whether any
problems a user is seeing is because of misuse of the functionality.
In SQL Server 2005, emergency mode is documented and there's a new
documented way of recovering from this situation using DBCC CHECKDB.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8j54e0175e35lc2v53m8j771hq1lm8rn32@.4ax.com...
> There are many approaches that seem "easier and faster" but
> they aren't necessarily good ideas and can actually not
> really be "easier and faster" in the long run.
> Note Paul's response. I remembered that Sybase used to (or
> still does, I don't know) have the command and if it failed
> once or twice, you essentially ended up with a useless data
> file and couldn't execute the command anymore. There are
> even easier sql commands posted up here that users have
> problems getting right the first or second time - and all of
> us have had those days where typing a simple select doesn't
> work. For those reasons, it's probably better for a user to
> call support and have someone from PSS walk them through the
> process carefully. Ever since it's been posted on
> newsgroups, I've seen it abused and misused by companies.
> If they end up with nothing but a useless data file, it may
> have actually have been "easier and faster" for them to get
> the backups read off the failed media from a company that
> specializes in that and restore the database from those
> files.
> -Sue
> On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
> <hari_prasad_k@.hotmail.com> wrote:
> >Hi Dan,
> >
> >I accept what you say regarding data integrity.
> >I suggested /recommended this method only because ken do not have the
> >database Backup as well as
> >no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> >approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that database
is
> >fine or not.
>
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message â'Device Activation error. Physical file name â'C:\...\xxx.ldfâ' may be incorrect.
Please advice me if anything can be done.
Thanking You"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net...
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
--
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > I had database whose log file and backup files are unavailable due to a
> media failure. I had the data file which is up to date.
> >
> > Is it possible to recover the database?
> >
> > I had tried using attach database utility but failed with an error
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> >
> > Please advice me if anything can be done.
> >
> > Thanking You
> >
>|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > A word of warning regarding the technique proposed by Hari. Forcibly
> > rebuilding the transaction log results in a database with questionable
> > integrity. Data may be physically corrupt or logically inconsistent
> because
> > normal database recovery did not take place.
> >
> > A preferable method is to restore from backup. If the log must be
rebuilt
> > because no backup is available, I suggest data be exported and then
> imported
> > into a clean database.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > I had database whose log file and backup files are unavailable due to
a
> > media failure. I had the data file which is up to date.
> > >
> > > Is it possible to recover the database?
> > >
> > > I had tried using attach database utility but failed with an error
> message
> > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > incorrect.
> > >
> > > Please advice me if anything can be done.
> > >
> > > Thanking You
> > >
> >
> >
>|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> > Hi Dan,
> >
> > I accept what you say regarding data integrity.
> > I suggested /recommended this method only because ken do not have the
> > database Backup as well as
> > no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> > approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that
database
> is
> > fine or not.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > > A word of warning regarding the technique proposed by Hari. Forcibly
> > > rebuilding the transaction log results in a database with questionable
> > > integrity. Data may be physically corrupt or logically inconsistent
> > because
> > > normal database recovery did not take place.
> > >
> > > A preferable method is to restore from backup. If the log must be
> rebuilt
> > > because no backup is available, I suggest data be exported and then
> > imported
> > > into a clean database.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> > > news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> > > > I had database whose log file and backup files are unavailable due
to
> a
> > > media failure. I had the data file which is up to date.
> > > >
> > > > Is it possible to recover the database?
> > > >
> > > > I had tried using attach database utility but failed with an error
> > message
> > > "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> > > incorrect.
> > > >
> > > > Please advice me if anything can be done.
> > > >
> > > > Thanking You
> > > >
> > >
> > >
> >
> >
>|||There are many approaches that seem "easier and faster" but
they aren't necessarily good ideas and can actually not
really be "easier and faster" in the long run.
Note Paul's response. I remembered that Sybase used to (or
still does, I don't know) have the command and if it failed
once or twice, you essentially ended up with a useless data
file and couldn't execute the command anymore. There are
even easier sql commands posted up here that users have
problems getting right the first or second time - and all of
us have had those days where typing a simple select doesn't
work. For those reasons, it's probably better for a user to
call support and have someone from PSS walk them through the
process carefully. Ever since it's been posted on
newsgroups, I've seen it abused and misused by companies.
If they end up with nothing but a useless data file, it may
have actually have been "easier and faster" for them to get
the backups read off the failed media from a company that
specializes in that and restore the database from those
files.
-Sue
On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
<hari_prasad_k@.hotmail.com> wrote:
>Hi Dan,
>I accept what you say regarding data integrity.
>I suggested /recommended this method only because ken do not have the
>database Backup as well as
>no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
>approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database is
>fine or not.|||One more note - the command (not the functionality) has been removed in SQL
Server 2005. Also, in SQL Server 2005, the fact that the functionality was
used is persisted permanently in the database so PSS can tell whether any
problems a user is seeing is because of misuse of the functionality.
In SQL Server 2005, emergency mode is documented and there's a new
documented way of recovering from this situation using DBCC CHECKDB.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8j54e0175e35lc2v53m8j771hq1lm8rn32@.4ax.com...
> There are many approaches that seem "easier and faster" but
> they aren't necessarily good ideas and can actually not
> really be "easier and faster" in the long run.
> Note Paul's response. I remembered that Sybase used to (or
> still does, I don't know) have the command and if it failed
> once or twice, you essentially ended up with a useless data
> file and couldn't execute the command anymore. There are
> even easier sql commands posted up here that users have
> problems getting right the first or second time - and all of
> us have had those days where typing a simple select doesn't
> work. For those reasons, it's probably better for a user to
> call support and have someone from PSS walk them through the
> process carefully. Ever since it's been posted on
> newsgroups, I've seen it abused and misused by companies.
> If they end up with nothing but a useless data file, it may
> have actually have been "easier and faster" for them to get
> the backups read off the failed media from a company that
> specializes in that and restore the database from those
> files.
> -Sue
> On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
> <hari_prasad_k@.hotmail.com> wrote:
> >Hi Dan,
> >
> >I accept what you say regarding data integrity.
> >I suggested /recommended this method only because ken do not have the
> >database Backup as well as
> >no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> >approach. After bringing
> > up the database ken can execute a DBCC CHECKDB and confirm that database
is
> >fine or not.
>
Database recovery with data file only
I had database whose log file and backup files are unavailable due to a media failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message “Device Activation error. Physical file name “C:\...\xxx.ldf” may be incorrect.
Please advice me if anything can be done.
Thanking You
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net... [vbcol=seagreen]
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to[vbcol=seagreen]
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to
> a
>
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message “Device Activation error. Physical file name “C:\...\xxx.ldf” may be incorrect.
Please advice me if anything can be done.
Thanking You
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net... [vbcol=seagreen]
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported[vbcol=seagreen]
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is[vbcol=seagreen]
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a
> message
>
|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to[vbcol=seagreen]
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to
> a
>
Database recovery with data file only
I had database whose log file and backup files are unavailable due to a medi
a failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message
Device Activation error. Physical file name “C:\...\xxx.ldf” may be i
ncorrect.
Please advice me if anything can be done.
Thanking You"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net...
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message[vbcol=seagreen]
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message[vbcol=seagreen]
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a[vbcol=seagreen]
> message
>|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to[vbcol=seagreen]
> a
>|||There are many approaches that seem "easier and faster" but
they aren't necessarily good ideas and can actually not
really be "easier and faster" in the long run.
Note Paul's response. I remembered that Sybase used to (or
still does, I don't know) have the command and if it failed
once or twice, you essentially ended up with a useless data
file and couldn't execute the command anymore. There are
even easier sql commands posted up here that users have
problems getting right the first or second time - and all of
us have had those days where typing a simple select doesn't
work. For those reasons, it's probably better for a user to
call support and have someone from PSS walk them through the
process carefully. Ever since it's been posted on
newsgroups, I've seen it abused and misused by companies.
If they end up with nothing but a useless data file, it may
have actually have been "easier and faster" for them to get
the backups read off the failed media from a company that
specializes in that and restore the database from those
files.
-Sue
On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
<hari_prasad_k@.hotmail.com> wrote:
>Hi Dan,
>I accept what you say regarding data integrity.
>I suggested /recommended this method only because ken do not have the
>database Backup as well as
>no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
>approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database i
s
>fine or not.|||One more note - the command (not the functionality) has been removed in SQL
Server 2005. Also, in SQL Server 2005, the fact that the functionality was
used is persisted permanently in the database so PSS can tell whether any
problems a user is seeing is because of misuse of the functionality.
In SQL Server 2005, emergency mode is documented and there's a new
documented way of recovering from this situation using DBCC CHECKDB.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8j54e0175e35lc2v53m8j771hq1lm8rn32@.
4ax.com...
> There are many approaches that seem "easier and faster" but
> they aren't necessarily good ideas and can actually not
> really be "easier and faster" in the long run.
> Note Paul's response. I remembered that Sybase used to (or
> still does, I don't know) have the command and if it failed
> once or twice, you essentially ended up with a useless data
> file and couldn't execute the command anymore. There are
> even easier sql commands posted up here that users have
> problems getting right the first or second time - and all of
> us have had those days where typing a simple select doesn't
> work. For those reasons, it's probably better for a user to
> call support and have someone from PSS walk them through the
> process carefully. Ever since it's been posted on
> newsgroups, I've seen it abused and misused by companies.
> If they end up with nothing but a useless data file, it may
> have actually have been "easier and faster" for them to get
> the backups read off the failed media from a company that
> specializes in that and restore the database from those
> files.
> -Sue
> On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
> <hari_prasad_k@.hotmail.com> wrote:
>
is[vbcol=seagreen]
>
a failure. I had the data file which is up to date.
Is it possible to recover the database?
I had tried using attach database utility but failed with an error message
Device Activation error. Physical file name “C:\...\xxx.ldf” may be i
ncorrect.
Please advice me if anything can be done.
Thanking You"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
exec sp_attach_single_file_db creates the ldf file for you
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Hi,
If you have mutiple LDF files as well as if the database is not detached you
may not be able to use sp_attach_single_file_db command.
In this case you can follow the below procedure to bring the database up.
But few of the steps are purely undocuemnted.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
Thanks
Hari
MCDBA
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:1vbDc.45061$ly2.28055@.doctor.cableinet.net...
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message[vbcol=seagreen]
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
> exec sp_attach_single_file_db creates the ldf file for you
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>|||A word of warning regarding the technique proposed by Hari. Forcibly
rebuilding the transaction log results in a database with questionable
integrity. Data may be physically corrupt or logically inconsistent because
normal database recovery did not take place.
A preferable method is to restore from backup. If the log must be rebuilt
because no backup is available, I suggest data be exported and then imported
into a clean database.
Hope this helps.
Dan Guzman
SQL Server MVP
Hope this helps.
Dan Guzman
SQL Server MVP
"Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> I had database whose log file and backup files are unavailable due to a
media failure. I had the data file which is up to date.
> Is it possible to recover the database?
> I had tried using attach database utility but failed with an error message
"Device Activation error. Physical file name "C:\...\xxx.ldf" may be
incorrect.
> Please advice me if anything can be done.
> Thanking You
>|||Hi Dan,
I accept what you say regarding data integrity.
I suggested /recommended this method only because ken do not have the
database Backup as well as
no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
approach. After bringing
up the database ken can execute a DBCC CHECKDB and confirm that database is
fine or not.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> A word of warning regarding the technique proposed by Hari. Forcibly
> rebuilding the transaction log results in a database with questionable
> integrity. Data may be physically corrupt or logically inconsistent
because
> normal database recovery did not take place.
> A preferable method is to restore from backup. If the log must be rebuilt
> because no backup is available, I suggest data be exported and then
imported
> into a clean database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ken.net" <Ken.net@.discussions.microsoft.com> wrote in message
> news:577A1B53-8E9E-4AF8-9F44-F4DF18D8D78D@.microsoft.com...
> media failure. I had the data file which is up to date.
message[vbcol=seagreen]
> "Device Activation error. Physical file name "C:\...\xxx.ldf" may be
> incorrect.
>|||Although DBCC CHECKDB can detect physical corruption, there could be logical
errors as well, such as orphaned data and uncommitted data. I wanted Ken to
fully understand the implications of rebuilding the log.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Dan,
> I accept what you say regarding data integrity.
> I suggested /recommended this method only because ken do not have the
> database Backup as well as
> no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
> approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database
is
> fine or not.
> --
> Thanks
> Hari
> MCDBA
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:ev$$EMZXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> because
rebuilt[vbcol=seagreen]
> imported
a[vbcol=seagreen]
> message
>|||Basically using that command breaks your business logic as there's no
guarantee of any constraints (implied or explicit) being true any more.
Also, the use of the command is unsupported and its use is tracked by the
server.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O5DUi6dXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Although DBCC CHECKDB can detect physical corruption, there could be
logical
> errors as well, such as orphaned data and uncommitted data. I wanted Ken
to
> fully understand the implications of rebuilding the log.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:u$KcfydXEHA.556@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> is
> rebuilt
to[vbcol=seagreen]
> a
>|||There are many approaches that seem "easier and faster" but
they aren't necessarily good ideas and can actually not
really be "easier and faster" in the long run.
Note Paul's response. I remembered that Sybase used to (or
still does, I don't know) have the command and if it failed
once or twice, you essentially ended up with a useless data
file and couldn't execute the command anymore. There are
even easier sql commands posted up here that users have
problems getting right the first or second time - and all of
us have had those days where typing a simple select doesn't
work. For those reasons, it's probably better for a user to
call support and have someone from PSS walk them through the
process carefully. Ever since it's been posted on
newsgroups, I've seen it abused and misused by companies.
If they end up with nothing but a useless data file, it may
have actually have been "easier and faster" for them to get
the backups read off the failed media from a company that
specializes in that and restore the database from those
files.
-Sue
On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
<hari_prasad_k@.hotmail.com> wrote:
>Hi Dan,
>I accept what you say regarding data integrity.
>I suggested /recommended this method only because ken do not have the
>database Backup as well as
>no LDF files. In this case DBCC REBUILD_LOG will be a easy and faster
>approach. After bringing
> up the database ken can execute a DBCC CHECKDB and confirm that database i
s
>fine or not.|||One more note - the command (not the functionality) has been removed in SQL
Server 2005. Also, in SQL Server 2005, the fact that the functionality was
used is persisted permanently in the database so PSS can tell whether any
problems a user is seeing is because of misuse of the functionality.
In SQL Server 2005, emergency mode is documented and there's a new
documented way of recovering from this situation using DBCC CHECKDB.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8j54e0175e35lc2v53m8j771hq1lm8rn32@.
4ax.com...
> There are many approaches that seem "easier and faster" but
> they aren't necessarily good ideas and can actually not
> really be "easier and faster" in the long run.
> Note Paul's response. I remembered that Sybase used to (or
> still does, I don't know) have the command and if it failed
> once or twice, you essentially ended up with a useless data
> file and couldn't execute the command anymore. There are
> even easier sql commands posted up here that users have
> problems getting right the first or second time - and all of
> us have had those days where typing a simple select doesn't
> work. For those reasons, it's probably better for a user to
> call support and have someone from PSS walk them through the
> process carefully. Ever since it's been posted on
> newsgroups, I've seen it abused and misused by companies.
> If they end up with nothing but a useless data file, it may
> have actually have been "easier and faster" for them to get
> the backups read off the failed media from a company that
> specializes in that and restore the database from those
> files.
> -Sue
> On Tue, 29 Jun 2004 18:46:10 +0530, "Hari"
> <hari_prasad_k@.hotmail.com> wrote:
>
is[vbcol=seagreen]
>
Sunday, February 19, 2012
Database Queries Using DATE
I am doing a database query in Java, and it seems to work fine using any of
the fields except for the date. I am assuming that this has something to do
with my syntax, since the DATE type has the most complicated syntax. My
query looks like the following:
SELECT * FROM Employee WHERE BDATE='4/20/1981'
BDATE is the field name, which is obviously of type DATE, and the error I
recieve is:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Dat
a type
mismatch in criteria expression.
I am assuming that when it says "Data type mismatch" it is interpreting
'4/20/1981' as a string. But because that is the format I used in the INSERT
statements when I added the records to the table. If this is the problem,
what should my query string? Thanks.
--
Nathan Sokalski
njsokalski@.hotmail.com> SELECT * FROM Employee WHERE BDATE='4/20/1981'
Ugh. Use a standard format. If you were looking for April 11th, Access or
your client app could easily misinterpret it as November 4th. If you use a
standard and unambiguous format, that won't happen.
> java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] D
ata type
> mismatch in criteria expression.
If you are querying Access, why are you posting to Java and SQL Server
groups? Learn how to use the tool you're using. Access requires dates to
be delimited by #, not ' ... try this, assuming your "date field" has no
time information:
SELECT * FROM Employee WHERE BDATE = '1981-04-20'
And BTW, don't use SELECT * in production code...
Followups set to microsoft.public.access.queries only.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
the fields except for the date. I am assuming that this has something to do
with my syntax, since the DATE type has the most complicated syntax. My
query looks like the following:
SELECT * FROM Employee WHERE BDATE='4/20/1981'
BDATE is the field name, which is obviously of type DATE, and the error I
recieve is:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Dat
a type
mismatch in criteria expression.
I am assuming that when it says "Data type mismatch" it is interpreting
'4/20/1981' as a string. But because that is the format I used in the INSERT
statements when I added the records to the table. If this is the problem,
what should my query string? Thanks.
--
Nathan Sokalski
njsokalski@.hotmail.com> SELECT * FROM Employee WHERE BDATE='4/20/1981'
Ugh. Use a standard format. If you were looking for April 11th, Access or
your client app could easily misinterpret it as November 4th. If you use a
standard and unambiguous format, that won't happen.
> java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] D
ata type
> mismatch in criteria expression.
If you are querying Access, why are you posting to Java and SQL Server
groups? Learn how to use the tool you're using. Access requires dates to
be delimited by #, not ' ... try this, assuming your "date field" has no
time information:
SELECT * FROM Employee WHERE BDATE = '1981-04-20'
And BTW, don't use SELECT * in production code...
Followups set to microsoft.public.access.queries only.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Subscribe to:
Posts (Atom)