Attach database without log file

How can I attach a database file if my log file is not available. I only have the database file because the log file was deleted. When I try attaching the database file, I get the error that it can not find the log file.

What is strange is that if I delete the log file for another database, I can still attach the database file and it creates a new log file for me.

I am using SQL Server 2000.

Thanks

AlexanderThere is a system stored proc for doing this called Sp_attach_single_file_db.
Though this will only work if the database had previously been detached cleanly. i.e. if it was detached and then the log file was lost you should be ok. If you lost the log file before detaching, your best bet is to restore a backup.|||Ronba,

Thanks fro your reply, I later discovered the same thing that you said. I was trying to attach the single file, but the database was not previously detached cleanly.

Alexander

Attach database - error 602

Hello,

After re-installation of MS SQL Server 2000 (Developer edition), I wanted to attch I previously available instances in the Enterprise Manager.
But, when I select "Attach database", I get the error:

Error 602: Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

When I run this "DBCC CHECKTABLE", I get the message that no errors have been found.

What should I do?SQL Server is finding a problem with the db when trying to attach so it wants you to run DBCC against that db and you can't because it isn't attached. You can try attaching manually with sp_attach_single_file_db or if you have a backup of the db, restore from backup.|||

IS that database detached properly previsouly?

If not try to use RESTORE statement from the backup file and also ensure the service pack level from the previous installation should match with it.

|||No, the databases were not detached previously. But anyway, I restored a backup.
Thanks anyway...|||

I'm glad you were successful but if you don't tell us what you did it doesn't help others. Can you tell us what you did to resolve the issue?

Thanks

|||If there is a problem with DETACH/ATTACH process then if the latest backup is available then you can recover the database using the RESTORE functionality.

Attach database - error 602

Hello,

After re-installation of MS SQL Server 2000 (Developer edition), I wanted to attch I previously available instances in the Enterprise Manager.
But, when I select "Attach database", I get the error:

Error 602: Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

When I run this "DBCC CHECKTABLE", I get the message that no errors have been found.

What should I do?SQL Server is finding a problem with the db when trying to attach so it wants you to run DBCC against that db and you can't because it isn't attached. You can try attaching manually with sp_attach_single_file_db or if you have a backup of the db, restore from backup.|||

IS that database detached properly previsouly?

If not try to use RESTORE statement from the backup file and also ensure the service pack level from the previous installation should match with it.

|||No, the databases were not detached previously. But anyway, I restored a backup.
Thanks anyway...|||

I'm glad you were successful but if you don't tell us what you did it doesn't help others. Can you tell us what you did to resolve the issue?

Thanks

|||If there is a problem with DETACH/ATTACH process then if the latest backup is available then you can recover the database using the RESTORE functionality.sql

Asynchronous Mirroring and Server Failure

Hi

Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does

1. Automatic failover occur to Secondary server?

2. What does the Database state show as. Primary, disconnected?.

3. what happens to my transactions. Are they lost?

4. Does any data loss occur?

If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?

Any information is appreciated,

Thank you

AK

In Asynchronous mirroring the following occurs,
1. failover has to be forced only as it will be in High performance mode and witness server will not be present
2. safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal)
3. yes the if the principal server is down it will be shown as disconnected
4. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
5. you need to failover using the option force_service allow data loss.......you need to run the below command in mirror server,

Code Snippet

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

refer the below links,
http://sql-articles.com/articles/dbmrr.htm
sql-articles.com

Thanxx
Deepak

|||

Hi Deepak

Thanks for your answers. So my question is after failing forceover and the mirror database has been recovered what do we need to do to apply those transaction log blocks from principal that have not been recieved?. does restoring the transaction logs backups from the principal (if they are available) and applying them on recoverd mirror database is something that guarantees no data loss?

Thank you

AK

|||Hi Ankith,
Definitely data loss is bound to be present in High performance mode (Asynchronous mirroring). You cannot apply the transaction logs from principal because mirroring works on the concept of transactions and not transaction logs........transaction logs are present only in log shipping ! in mirroring all the transactions occur internally and cannot perform any restorations

Thanxx
Deepak
|||

Hi Deepak,

Great Explanation. In such a case, how can we

1.Find out how much data loss has occured. Is there any way to know this? (I am thinking not).

2.what is the best way to verify to know if data loss has occured?

3. Is there any way to mitigate or reduce the data loss if we cant prevent it?

Thanks

AK

|||Hi Ankith,
1. I don't think its possible to identify the data loss
2. Definitely data loss will occur but i am not sure
3. The only thing that comes to my mind is High availability mode which has automatic failover as witness server is present ! since you need to perform forced failover i.e you need to run the query so that failover occurs instantly ! if you want to mitigate it in asynchronous mode i think you need to configure alerts which will let you know the status of mirroring so that you can perform failover

Asynchronous Mirroring and Server Failure

Hi

Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does

1. Automatic failover occur to Secondary server?

2. What does the Database state show as. Primary, disconnected?.

3. what happens to my transactions. Are they lost?

4. Does any data loss occur?

If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?

Any information is appreciated,

Thank you

AK

In Asynchronous mirroring the following occurs,
1. failover has to be forced only as it will be in High performance mode and witness server will not be present
2. safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal)
3. yes the if the principal server is down it will be shown as disconnected
4. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
5. you need to failover using the option force_service allow data loss.......you need to run the below command in mirror server,

Code Snippet

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

refer the below links,
http://sql-articles.com/articles/dbmrr.htm
sql-articles.com

Thanxx
Deepak

|||

Hi Deepak

Thanks for your answers. So my question is after failing forceover and the mirror database has been recovered what do we need to do to apply those transaction log blocks from principal that have not been recieved?. does restoring the transaction logs backups from the principal (if they are available) and applying them on recoverd mirror database is something that guarantees no data loss?

Thank you

AK

|||Hi Ankith,
Definitely data loss is bound to be present in High performance mode (Asynchronous mirroring). You cannot apply the transaction logs from principal because mirroring works on the concept of transactions and not transaction logs........transaction logs are present only in log shipping ! in mirroring all the transactions occur internally and cannot perform any restorations

Thanxx
Deepak
|||

Hi Deepak,

Great Explanation. In such a case, how can we

1.Find out how much data loss has occured. Is there any way to know this? (I am thinking not).

2.what is the best way to verify to know if data loss has occured?

3. Is there any way to mitigate or reduce the data loss if we cant prevent it?

Thanks

AK

|||Hi Ankith,
1. I don't think its possible to identify the data loss
2. Definitely data loss will occur but i am not sure
3. The only thing that comes to my mind is High availability mode which has automatic failover as witness server is present ! since you need to perform forced failover i.e you need to run the query so that failover occurs instantly ! if you want to mitigate it in asynchronous mode i think you need to configure alerts which will let you know the status of mirroring so that you can perform failover

Asymmetry in "Execute SQL Task"?

Anyone know why the Execute SQL Task is only available under Control flow and not Data flow? (At times it's a useful data manipuation task and I wish was available as a Data flow task as well.)

Barkingdog

It could lead to some extremely inefficient data flows.

Imagine executing a SQL statement for every record in a 1,000,000 row transformation?

Or what if you decided to execute an update against the source data in your transformation?

The posiibilities for abuse are staggering...

If you need to run a SQL task doing it before or after a transformation allows you to execute SQL on the entire set... Not just a single record.

|||ssis provides the ole db command data flow transformation component for sql-based data manipulation: http://msdn2.microsoft.com/en-us/library/ms141138.aspx