Database in 'In recovery' mode

Hi,
There was the most awful case. During a update of a DB there was a
switching-off of power supplies. After switch on of a server the base has
passed in mode 'Suspect'. To restore base by standard way it was not
possible. Substitution of files and had turned out to open changes of the
status of base data.
Has executed substitution of files of a database. Has successfully opened a
database, structure - all is accessible.
Check of a file of a database on integrity is executed successfully, errors
are not present (DBCC CHECKDB).
I can not create backup, gives out the message that are opened deferred
transactions.
Also, at change of some objects there is a lag of a server that leads to
change of the status of database 'In Recovery'. In 2 and more hours the
status has not changed.
Whether it is necessary to wait for restoration? How to commit deferred tran?
Thank you for any help.
p.s. Backups very old :(Not sure if this is going to work ... a little bit risky but worth a try (I
guess)...
It seemed that some transaction are not clearing so try to rollback the
transaction and then try to backup the database and then try to turn the
database back online, so the command would look like:
USE master
GO
-- First set it to read only and rollback any active transaction
ALTER DATABASE [DatabaseName]
SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO
-- Backup the database, hopefully it should work since there should not be
any active transaction
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePathAndName]'
WITH INIT
GO
-- Turn the database back online
ALTER DATABASE [DatabaseName]
SET ONLINE WITH ROLLBACK IMMEDIATE
GO
Hopefully that would work (but no guarantee...)
Lucas
"int64" wrote:
> Hi,
> There was the most awful case. During a update of a DB there was a
> switching-off of power supplies. After switch on of a server the base has
> passed in mode 'Suspect'. To restore base by standard way it was not
> possible. Substitution of files and had turned out to open changes of the
> status of base data.
> Has executed substitution of files of a database. Has successfully opened a
> database, structure - all is accessible.
> Check of a file of a database on integrity is executed successfully, errors
> are not present (DBCC CHECKDB).
> I can not create backup, gives out the message that are opened deferred
> transactions.
> Also, at change of some objects there is a lag of a server that leads to
> change of the status of database 'In Recovery'. In 2 and more hours the
> status has not changed.
> Whether it is necessary to wait for restoration? How to commit deferred tran?
> Thank you for any help.
> p.s. Backups very old :(
>

Database design

I m designing a data base with SQL Server 2000, and I m an inexperienced with this . I would like to know whether the structure that I have now is efficient, and if I could improve it . The database is for a website that allows you to insert an offer of a car of second hand. Here is a piece of structure of offers database:

Table 1:
Offer_Num (Key Field)
Offer_Date

Table 2:
Kind_of_car_Num
Trade_Name_Num
Model_Num
Offer_Num (Key Field)

Table 2-a:
Kind_of_car_Num (Key Field)
Name_of_Kind_of_car

Table 2-b:
Trade_Name_Num (Key Field)
Trade_Name

Table 2-c:
Model_Num (Key Field)
Model_Name

Table 3:
Car_Price
Offer_Num (Key Field)

Table 4:
Car_Colour
Offer_Num (Key Field)

Tabla 5:
Matriculation_Car
Offer_Num (Key Field)

The tables 1, 2, 3, 4 and 5 there are linked throughout the key field Offer_num like a sequence (I linked table 1 with table 2, table 2 with 3, table 3 with 4 and table 4 with 5). The type of data of the Offer_num are Identity in all tables, and table 2 are linked with table 2-a, 2-b and 2-c.

When it makes the data base of clients, I must connect all the tables by the key field "Number _of _client"? like in this example?

ThanksNot knowing the meaning of some of the data, this could be a bit dangerous for me to comment on. Let me just put on my asbestos suit, and take an educated guess or two...

Normally, the rule of thumb that I use is to figure out what "objects" I am trying to model in the database. In this case, it appears to be Offers, Cars, and possibly Clients. I generally try to make a table for each kind of object I am modelling, so I would have a table of Cars, and a table of Offers.

The objects can have several "Attributes" (color of a car, amount of an offer, etc.). Some or all of these can be made into tables of thier own. There is not really a hard and fast rule for this, so this is where the holy wars begin. If you have very few values for your attributes for a large population of objects, (and especially if you want to report on all "red" cars), then I would go ahead and give those attributes their own tables. These attributes are then mapped to cars by ids in the Cars table. This approach gives you much more control over the attribute values that are entered, which is important for some reporting. Also, if you are giving folks the ability to search this database, you want to give them a nice set base of values to look for. I would dread searching for a car in the local newspaper in a programatic way. How many ways are there to spell "Volkswagon" when you are paying by the word, anyway? But I digress.

If you have some attributes that are absolutely inseparable from an object (such as offer amount), then I would put those attributes on the Offers table as columns.

Now the fun really begins, because you can have many Offers on a single Car, with just two tables representing Cars and Offers. This is where you have to use a Mapping table (Car_ID, Offer_ID) to resolve all offers on a car. If you look in the pubs database, you can see this in the titleauthor table in the pubs database, where many titles can be produced by one author. Coincidentally, several authors can contribute on one title, and this construct still comes out fairly clean.

All of this does not really help the original question, but hopefully this will give you a little insight into the workings of database design.

Now for the disclaimers: All of these points raised are my own not-so-humble backward opinion, and should be taken as such rather than the gospel truth some folks may assume it is. There are many ways to skin a cat, but the cat skin market has pretty much bottomed out, so don't bother the cat.|||Thanks for your explanation of as you design a data base and the main elements that takings in consideration. Yes, all of this does really help the original question, and it clarify many doubts to me that nonwise nor like requesting them, thank you very much Mcrowley.

Csar

Database Design

I am creating a knowledge base. I want the user to be able to choose a category such as Hardware, Software, Etc. then I want them to be able to choose what type of software such as word, excel, ets.

It will then display in a gridview problems, solutions, submitted by, last updated, and review date.

The gridview will allow users to update solutions and I would also like the ability for technicians to be able to add new Problems/solutions.

What would be the best way to handle this?

Any tips are appreciated.

Rick

As far as the database design goes, I would make it relational using the structure similar to the one below.

Table: Categories
Columns: CatID, Category

Table: CatTypes
Columns: CatTypeID, CatID, CatType

Table: KB_FAQ
Columns: KB_ID, Problem, Solution, UserID, LastUpdated, LastReviewed

Table: KB_CatTypes
Columns: KB_ID, CatTypeID

Table: Users
Columns: UserID, UserName, FirstName, LastName, Password

|||In Visual Studion Web Developer express, where do I setup relationships?|||I figured it out. Thanks for all your help.