次の方法で共有

Database Corruption Part 2 :: How to attach a database using the Create-Replace method

  • [アーティクル]

Ok, so, as promised, here’s my next post on Database Corruption. In this post, we will have a look at how to attach a database that you have detached and can now no longer attach back to SQL Server. This may happen if the database was already corrupt before it was detached; or if the database files have become corrupt after the detach.

There are quite a few names that have been given to this method. “Create-Copy method”, “Copy-Create method”, “Copy-Replace method” and “Create-Replace method” are just some of them. I would, however, personally like to use the name “Create-Replace method”, as that is exactly what we will do in this process.

In this method, we will follow the following step:
=> Create a new blank database.
=> Replace the files of the new database by the older files.

However, before we do that, we must keep in mind that information related to the database files is stored in two places:
1. sys.sysaltfiles of the master database
2. sys.sysfiles of the user database
We must also keep in mind that information in these two tables have to be exactly the same. Why I am talking in these lines is, this information is already present in the MDF file of the database we are trying to attach, and after the attach, information regarding the file layout will also be available in the sys.sysaltfiles of the master database. And, these information will need to match.

Hence, it is essential for us to be able to retrieve the information from the Detached Database. This can be achieved using the command DBCC CHECKPRIMARYFILE.

To proceed, connect to the SQL Server instance, and run the following query:
     DBCC CHECKPRIMARYFILE ('Full Path to MDF file', 3)
This will give us three bits of very essential information:
1. The file ID
2. The logical file name
3. The physical file name

Note: This command would execute only against a detached MDF file.

Now that we have all this information at our disposal, we will use this information to create a new database. Essentially, we will have to create a new database that has the same database structure as the old database we will attach. However, before creating the new database, please make sure you rename the original database files by appending .Old to their filenames.

Once the database is created, stop the SQL Server instance. This will cause SQL Server to release the handles on the physical files. These files can now be renamed or deleted. For the time being, lets not delete the database files, but rename them by appending .New to their file names.

Now, rename the .Old files back to their original names. Essentially, we have now replaced the files for the new database with the files of the older database. Start the SQL Server service at this point. Your old database should now be attached.

At this point, you might find your database to be in suspect mode. This is normal for a database that is already corrupt. We will see how to deal with suspect databases in my following posts.

However, if the database comes online fine, please make sure that you run DBCC CHECKDB against the affected database to check its consistency.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Comments