An up-to-the-point recovery .Is it possible with MS SQLserver?

I asked how to apply the latest log when DB is crashed.
Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE option"
But if both .mdf and .ldf are not avaible.what to do..
Informix way of backup
1)automaticaly backup its log when the log is full,(instead of based on
schedule)
2)backup its log when the server is crashed.
I hope Sql-server can handle it this way.is it possible?
Thanks!If the server has crashed how could it reliably make a backup? If you have
lost your disks or files, then you have no option but to go to a tape (or
other remote location) for a backup file(s). Which is why it is so
important to use a fault tolerant disk subsystem.
If you haven't lost the files, then you should be able to make a backup of
the log using the NO_TRUNCATE option and then use that backup and any
previous log backup files to recover to the point of the crash.
"Straight" <someone@.> wrote in message
news:uUjm5sMLEHA.340@.TK2MSFTNGP11.phx.gbl...
> I asked how to apply the latest log when DB is crashed.
> Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE option"
> But if both .mdf and .ldf are not avaible.what to do..
> Informix way of backup
> 1)automaticaly backup its log when the log is full,(instead of based on
> schedule)
> 2)backup its log when the server is crashed.
> I hope Sql-server can handle it this way.is it possible?
> Thanks!
>

An up-to-the-point recovery .Is it possible with MS SQLserver?

I asked how to apply the latest log when DB is crashed.
Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE option"
But if both .mdf and .ldf are not avaible.what to do..
Informix way of backup
1)automaticaly backup its log when the log is full,(instead of based on
schedule)
2)backup its log when the server is crashed.
I hope Sql-server can handle it this way.is it possible?
Thanks!
If the server has crashed how could it reliably make a backup? If you have
lost your disks or files, then you have no option but to go to a tape (or
other remote location) for a backup file(s). Which is why it is so
important to use a fault tolerant disk subsystem.
If you haven't lost the files, then you should be able to make a backup of
the log using the NO_TRUNCATE option and then use that backup and any
previous log backup files to recover to the point of the crash.
"Straight" <someone@.> wrote in message
news:uUjm5sMLEHA.340@.TK2MSFTNGP11.phx.gbl...
> I asked how to apply the latest log when DB is crashed.
> Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE option"
> But if both .mdf and .ldf are not avaible.what to do..
> Informix way of backup
> 1)automaticaly backup its log when the log is full,(instead of based on
> schedule)
> 2)backup its log when the server is crashed.
> I hope Sql-server can handle it this way.is it possible?
> Thanks!
>

An up-to-the-point recovery .Is it possible with MS SQLserver?

I asked how to apply the latest log when DB is crashed.
Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE option"
But if both .mdf and .ldf are not avaible.what to do..
Informix way of backup
1)automaticaly backup its log when the log is full,(instead of based on
schedule)
2)backup its log when the server is crashed.
I hope Sql-server can handle it this way.is it possible?
Thanks!If the server has crashed how could it reliably make a backup? If you have
lost your disks or files, then you have no option but to go to a tape (or
other remote location) for a backup file(s). Which is why it is so
important to use a fault tolerant disk subsystem.
If you haven't lost the files, then you should be able to make a backup of
the log using the NO_TRUNCATE option and then use that backup and any
previous log backup files to recover to the point of the crash.
"Straight" <someone@.> wrote in message
news:uUjm5sMLEHA.340@.TK2MSFTNGP11.phx.gbl...
> I asked how to apply the latest log when DB is crashed.
> Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE option"
> But if both .mdf and .ldf are not avaible.what to do..
> Informix way of backup
> 1)automaticaly backup its log when the log is full,(instead of based on
> schedule)
> 2)backup its log when the server is crashed.
> I hope Sql-server can handle it this way.is it possible?
> Thanks!
>|||If i split .mdf and .ldf on diffrent disks.
.mdf was damaged ,.ldf was fine
I restore the latest DB backup ,can i rollfoward with the intact .ldf on the
diffrent good disk ?.
How ?because i found £¢backup log with no_truncate£¢ is impossible while the
DB is not running.
Thank you!
"Don Peterson" <no1@.nunya.com> дÈëÓʼþ
news:ezcF34MLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> If the server has crashed how could it reliably make a backup? If you
have
> lost your disks or files, then you have no option but to go to a tape (or
> other remote location) for a backup file(s). Which is why it is so
> important to use a fault tolerant disk subsystem.
> If you haven't lost the files, then you should be able to make a backup of
> the log using the NO_TRUNCATE option and then use that backup and any
> previous log backup files to recover to the point of the crash.
> "Straight" <someone@.> wrote in message
> news:uUjm5sMLEHA.340@.TK2MSFTNGP11.phx.gbl...
> > I asked how to apply the latest log when DB is crashed.
> > Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE
option"
> > But if both .mdf and .ldf are not avaible.what to do..
> >
> > Informix way of backup
> > 1)automaticaly backup its log when the log is full,(instead of based on
> > schedule)
> > 2)backup its log when the server is crashed.
> >
> > I hope Sql-server can handle it this way.is it possible?
> >
> > Thanks!
> >
> >
>|||Backup log with no truncate behaves correctly at sql server 2000, in as =much as you can back up the lock even when the mdf is unavailable. =(provided you are in full recovery model - in bulk logged the mdf will =be required if any statements that operate in minimally_logged mode have =occured since the changes for these will need to be extracted by using =the bulk-change-map from the mdf). At 7 the primary filegroup mdf was =also required since the table sysfiles was stored there which has =details of what files make up the db - this was modified at 2000 by =introducing sysaltfiles in the master db to shadow the contents of =sysfiles.
So - backup log with no truncate WILL be fine provided one of master or =the primary mdf is available. To ensure this put master on different =drive to user databases, and put logs on raid 1 so at least one copy of =the log will survive a disc failure, (Logs on separate spindle to mdf as =well of course)
Mike John
"Straight" <someone@.> wrote in message =news:eS0FB4OLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> If i split .mdf and .ldf on diffrent disks.
> .mdf was damaged ,.ldf was fine
> I restore the latest DB backup ,can i rollfoward with the intact .ldf =on the
> diffrent good disk ?.
> > How ?because i found =A3=A2backup log with no_truncate=A3=A2 is =impossible while the
> DB is not running.
> > Thank you!
> > > > "Don Peterson" <no1@.nunya.com> =D0=B4=C8=EB=D3=CA=BC=FE
> news:ezcF34MLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> > If the server has crashed how could it reliably make a backup? If =you
> have
> > lost your disks or files, then you have no option but to go to a =tape (or
> > other remote location) for a backup file(s). Which is why it is so
> > important to use a fault tolerant disk subsystem.
> >
> > If you haven't lost the files, then you should be able to make a =backup of
> > the log using the NO_TRUNCATE option and then use that backup and =any
> > previous log backup files to recover to the point of the crash.
> >
> > "Straight" <someone@.> wrote in message
> > news:uUjm5sMLEHA.340@.TK2MSFTNGP11.phx.gbl...
> > > I asked how to apply the latest log when DB is crashed.
> > > Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE
> option"
> > > But if both .mdf and .ldf are not avaible.what to do..
> > >
> > > Informix way of backup
> > > 1)automaticaly backup its log when the log is full,(instead of =based on
> > > schedule)
> > > 2)backup its log when the server is crashed.
> > >
> > > I hope Sql-server can handle it this way.is it possible?
> > >
> > > Thanks!
> > >
> > >
> >
> >
> >|||Also, if the whole SQL Server goes down (cannot start it), then you can still get up to minute recovery. You
take another SQL Server installation, create a database with same file structure. Stop that SQL Server. Delete
the database file. Then "slide" in the log file from the crashed SQL Server, and then do the log backup using
the no_truncate option. There's a KB on this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:e5yHLTPLEHA.2260@.TK2MSFTNGP09.phx.gbl...
Backup log with no truncate behaves correctly at sql server 2000, in as much as you can back up the lock even
when the mdf is unavailable. (provided you are in full recovery model - in bulk logged the mdf will be
required if any statements that operate in minimally_logged mode have occured since the changes for these will
need to be extracted by using the bulk-change-map from the mdf). At 7 the primary filegroup mdf was also
required since the table sysfiles was stored there which has details of what files make up the db - this was
modified at 2000 by introducing sysaltfiles in the master db to shadow the contents of sysfiles.
So - backup log with no truncate WILL be fine provided one of master or the primary mdf is available. To
ensure this put master on different drive to user databases, and put logs on raid 1 so at least one copy of
the log will survive a disc failure, (Logs on separate spindle to mdf as well of course)
Mike John
"Straight" <someone@.> wrote in message news:eS0FB4OLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> If i split .mdf and .ldf on diffrent disks.
> .mdf was damaged ,.ldf was fine
> I restore the latest DB backup ,can i rollfoward with the intact .ldf on the
> diffrent good disk ?.
> How ?because i found £¢backup log with no_truncate£¢ is impossible while the
> DB is not running.
> Thank you!
>
> "Don Peterson" <no1@.nunya.com> дÈëÓʼþ
> news:ezcF34MLEHA.1120@.TK2MSFTNGP11.phx.gbl...
> > If the server has crashed how could it reliably make a backup? If you
> have
> > lost your disks or files, then you have no option but to go to a tape (or
> > other remote location) for a backup file(s). Which is why it is so
> > important to use a fault tolerant disk subsystem.
> >
> > If you haven't lost the files, then you should be able to make a backup of
> > the log using the NO_TRUNCATE option and then use that backup and any
> > previous log backup files to recover to the point of the crash.
> >
> > "Straight" <someone@.> wrote in message
> > news:uUjm5sMLEHA.340@.TK2MSFTNGP11.phx.gbl...
> > > I asked how to apply the latest log when DB is crashed.
> > > Tibor Karaszi suggested to "do a log backup using the NO_TRUNCATE
> option"
> > > But if both .mdf and .ldf are not avaible.what to do..
> > >
> > > Informix way of backup
> > > 1)automaticaly backup its log when the log is full,(instead of based on
> > > schedule)
> > > 2)backup its log when the server is crashed.
> > >
> > > I hope Sql-server can handle it this way.is it possible?
> > >
> > > Thanks!
> > >
> > >
> >
> >
>

Am I takin this Normalization too far?

Does this show "poor" design? It has been suggested to me to do a "Logical Model" of my data base and that will make it easier to "normalize" the tables. I tried this and come up with the following but I don't know if I am stretching it too thin. One rule of the 2NF is to ensure all tables have a primary key, and as you can see, my tbProjectTeam has a primary key, but that is made up of the entire row. Same goes for the tbDepartmentActivities.

tbEstimatedProjects
Reference (PK) | Name | City | Postal |...
------------------
1 | Some Project | Niagra Falls | N8E7J5 | ...

tbAwardedProjects
Project (PK) | Reference
--------
1001 | 1

tbProjectTeam
Project (PK)| Login (PK) | Activity (PK)
-------------
1001 | jsmith | Detailer

tbEmployees
Login (PK) | First | Last |.....
-----------
jsmith | Jim | Smith |....

tbDepartmentListing
Login | DeptCode
-------
jsmith | ENG

tbDepartments
Code | Department
--------
ENG | Engineering

tblDepartmentActivities
Code (PK) | Activity (PK)
-------
ENG | Engineering
ENG | Detailer

Am I taking this too far or is the above structure something to be expected by a "good" normalized table structure?

Mike BThere is nothing inherentyly wrong with an entire row being a primary key, especially if the table contains only two or three columns. I do suspect that your design needs some tweaking.

It looks like you are dealing with the following entities: Projects, Employees, Departments, DepartmentActivities. Other tables should establish relationships between these primary entities, such as ProjectMembers.

Do ProjectTeams exist as persistent entities to which projects are assigned, or do they merely represent the individual employees assigned to work on a given project. If they exist as teams, then you will need an entity table for them as well.

Can an employee work on more than one team?

Can more than one team work on a project?

These questions determine whether you need additional tables for establishing many-to-many relationships.|||[SIZE=1]Originally posted by blindman
It looks like you are dealing with the following entities: Projects, Employees, Departments, DepartmentActivities. Other tables should establish relationships between these primary entities, such as ProjectMembers.

ProjectMembers would be the ProjectTeams table.

Do ProjectTeams exist as persistent entities to which projects are assigned, or do they merely represent the individual employees assigned to work on a given project. If they exist as teams, then you will need an entity table for them as well.

Represents the individual employees assigned to an activity on a given project within their respected department.
Example:

Project | Login | Activity
----------
1001 | jsmith | Engineer
1001 | jsmith | Detailer
1001 | mblack | Manager
....
....
1926 | jsmith | Manager // Must have been promoted!

There are no set teams, so a team entity would not exist. Any thoughts?

Mike B|||Then what is tbAwardedProjects for? Does it just indicate whether a project's status has changed from "Estimated" to "Awarded"? If so, this should be a field in the project table, and not a separate table.|||Originally posted by blindman
Then what is tbAwardedProjects for? Does it just indicate whether a project's status has changed from "Estimated" to "Awarded"? If so, this should be a field in the project table, and not a separate table.

Point well taken. This I did because the "project number (eg.1001)" is only assigned if the project is awarded. This project number must be unique. Since it must be unique and a unique value cannot be null, awarded projects need to be their own entity. This system is 2 parts (Cost Control / Cost Estimate). The cost estimate uses the reference as a key and the cost control uses the project number as a key. Make sence?

Mike B|||I'd recommend creating an internal ID that is assigned to every project (either Identity or GUID) and then make your ProjectNumber a separate field. ProjectNumber would be defined as Unique and indexed, but would not be the primary key and could thus allow NULL values. Then you can dispense with the "Awarded" column, because the mere presence of a ProjectNumber value would indicate that the project has been awarded.|||Originally posted by blindman
I'd recommend creating an internal ID that is assigned to every project (either Identity or GUID) and then make your ProjectNumber a separate field. ProjectNumber would be defined as Unique and indexed, but would not be the primary key and could thus allow NULL values. Then you can dispense with the "Awarded" column, because the mere presence of a ProjectNumber value would indicate that the project has been awarded.

Hmmm, that worked, thank you. I tried that in the SQL Server Diagram Editor and it wouldn't save the table as it at first, but then I tried a fresh database and it worked! Thanks...

Mike B