How to attach a db_file with problems?

A database goes "SUSPECT" the client deatach de DB. later it tries to
reatach it but he gets an error. I tried to copy the file to other disk but
I get an error from windows saying that a cyclic error in the disk prevent
the copy. Is there any way to attach a file with problems? just in order to
save at least some of the data?You can create a new database with the same name, using the same data file
and log file names. Then shut down SQL Server and copy your "bad" files
over the top of these new files. When SQL Server starts the database will
once again be suspect. You can then put the database in emergency mode
(status 32768) and recycle SQL Server. At this point you should be able to
at least salvage some of the data, may be all depending on the rason it
went suspect.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
To add on, I had a identical instance where I followed the same procedure as
Rand suggested , after that,
1. The Datbase was able to open in Emergency mode (32768)
2. I was not able to take a backup.
What I did,
1. Create a new database
2. Script out all objects in seperate files
3. Executed the user defined types(UDT) and Table creation script in new
database
4. Run DTS to transfer all data from old to new database ( U can also use
BCP OUT /IN)
5. Executed the Indexe creation script
6. Executed all the other object creation script
After these steps I did a comparison of old and new database and FYI, I lost
few pieces of data which I asked the users to re enter.
Thanks
Hari
MCDBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:M3yLU1E6DHA.2768@.cpmsftngxa07.phx.gbl...

quote:

---

> You can create a new database with the same name, using the same data file
> and log file names. Then shut down SQL Server and copy your "bad" files
> over the top of these new files. When SQL Server starts the database will
> once again be suspect. You can then put the database in emergency mode
> (status 32768) and recycle SQL Server. At this point you should be able to
> at least salvage some of the data, may be all depending on the rason it
> went suspect.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

---

How to attach a db_file with problems?

A database goes "SUSPECT" the client deatach de DB. later it tries to
reatach it but he gets an error. I tried to copy the file to other disk but
I get an error from windows saying that a cyclic error in the disk prevent
the copy. Is there any way to attach a file with problems? just in order to
save at least some of the data?You can create a new database with the same name, using the same data file
and log file names. Then shut down SQL Server and copy your "bad" files
over the top of these new files. When SQL Server starts the database will
once again be suspect. You can then put the database in emergency mode
(status 32768) and recycle SQL Server. At this point you should be able to
at least salvage some of the data, may be all depending on the rason it
went suspect.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
To add on, I had a identical instance where I followed the same procedure as
Rand suggested , after that,
1. The Datbase was able to open in Emergency mode (32768)
2. I was not able to take a backup.
What I did,
1. Create a new database
2. Script out all objects in seperate files
3. Executed the user defined types(UDT) and Table creation script in new
database
4. Run DTS to transfer all data from old to new database ( U can also use
BCP OUT /IN)
5. Executed the Indexe creation script
6. Executed all the other object creation script
After these steps I did a comparison of old and new database and FYI, I lost
few pieces of data which I asked the users to re enter.
Thanks
Hari
MCDBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:M3yLU1E6DHA.2768@.cpmsftngxa07.phx.gbl...
> You can create a new database with the same name, using the same data file
> and log file names. Then shut down SQL Server and copy your "bad" files
> over the top of these new files. When SQL Server starts the database will
> once again be suspect. You can then put the database in emergency mode
> (status 32768) and recycle SQL Server. At this point you should be able to
> at least salvage some of the data, may be all depending on the rason it
> went suspect.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>sql

How to accomplish this?

I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.

I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:

Event A Created
Event A Modified
Event A Property A Created
Event B Created
Event C Created
Event B Property A Modified
Event A Property B Created
Event C Voided
Event A Property A Closed
Event A Closed

In the end I want to create two tables like so:

Table 1:
Event A / Closed
Event B / Modified

(Event C is not here because it was voided, so any prior records of C are gone).

Table 2:
Event A / Property A / Closed
Event A / Property B / Created

(Only events with properties would be in this table).

In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.

Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.

"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).

I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).

I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.

So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?

Thanks for your help.May be you need to move the 'modifies' logic to a separate dataflow after the 'creates' are done. I don't think there is a easy way of enforcing the order of parallel data pipelines in a single dataflow.|||

Hi StGeorge,

I think you need to create some mechanism by which to order properties. I trust you when you say your actual process is more complicated, so I may be headed down the path with this suggestion - but here goes:

It sounds as if you are adding a row to Table 2 on a state named Created. It also sounds as if you are removing this row from Table 2 and its parent row in Table 1 on a similar state named Void.

Would it work if you created an integer column in Table 2 called PropertyOrder? You could populate it with 1 on Created, 3 on Void, and 2 for any other property event. In SSIS, you could then load the records from the source ordered by this field. If your transactional environment is intense (lots of asynchronous transactions), you may be forced to stage the ordered data (in a table or raw file) and then act on this resultset in a subsequent data flow or Execute SQL Task to guarantee transactional integrity (which is how I interpret Table 1).

I may be way off here. If I am, please accept my apologies.

The method I propose above is roughly analogous to a method many deterministic network protocols use to guarantee packets are output in the order sent. The packets are generated synchronously, transmitted asynchronously, received in any order, cached, and reconstructed by ordered key before delivery. I think you have all the pieces except the key by which to order the records.

Hope this helps,
Andy