go backarticles

Articles of SQLschool.gr Team

The undocumented ATTACH_FORCE_REBUILD_LOG

Antonios Chatzipavlis

Στην δουλειά του DBA υπάρχουν στιγμές που πραγματικά οι παλμοί ανεβαίνουν επικίνδυνα. Συνήθως αυτό συμβαίνει ότι υπάρχει το ενδεχόμενο απώλειας δεδομένων.

Θα πει κάποιος πως μπορεί να γίνει αυτό καθώς ο σωστός DBA έχει backups. Αυτό είναι σωστό αρκεί να μιλάμε για οργανισμό που έχει DBA πραγματικό αλλιώς όλα μπορούν να συμβούν.

Πρόσφατα επικοινώνησε μια εταιρεία μαζί που για κάποιους όχι και τόσο καλούς λόγους είχε χάσει τα πάντα από τα πάντα. Κάπου υπήρχε ένα system backup που μέσα σε αυτό ήταν τα data αρχεία μιας database (to log ήταν damaged).

Στην προσπάθεια τους να κάνουν attach αυτή την database με το CREATE DATABASE FOR ATTACH το λάθος που επιστρέφονταν ήταν το παρακάτω:

File activation failure. The physical file name "…" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'XXXXXX'. CREATE DATABASE is aborted.

Ακόμα και όταν δοκίμαζαν το CREATE DATABASE FOR ATTACH_REBUILD_LOG το ίδιο λάθος επιστρέφονταν.

Αν παρατηρήσετε το μήνυμα λάθους λέει ακριβώς το πρόβλημα

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Η λύση που υπάρχει σε αυτή την περίπτωση είναι να χρησιμοποιηθεί η δυστυχώς undocumented CREATE DATABASE FOR ATTACH_FORCE_REBUILD_LOG.

SQL Script

CREATE DATABASE demo
ON ( FILENAME  = ‘…..’ )
FOR ATTACH_FORCE_REBUILD_LOG;

//Antonios Chatzipavlis


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.



Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
sql school greece logo
© 2010-2021 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.