SQL Server 2000 Lost Secondary Data File - Full Recovery Model
Restore a SQL Server Secondary Data File By Using a Database Backup, Differential Backup, and Transaction Log Backup
Description:
We will end up destroying a secondary data file in a production database, then using a database backup, a differential backup, and a transaction log backup to perform a full restore. The destruction of the secondary data file will simulate a loss of the disk in which the secondary data file was residing on.
(back to top)Requirements:
- As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment
- The database needs to be using the Full Recovery Model - for the exercise on changing the recovery model, click here
- You will need to have a backup device already created - for the exercise on creating a backup device, click here
- You need to have a secondary data file in the database - the Setup section will help you if one doesn't already exist.
Setup:
It is doubtful that you have more than just the primary data file for Northwind if you are using the original Northwind install. We need to add a data file for this exercise. So, in Enterprise Manager, right click on the Northwind database and choose properties as shown below.
From the "Data Files" tab, you'll see that the primary data file already is present. Type in Northwind_Secondary under the primary data file to add a secondary data file to the Northwind database. Then we're going to accept all of the defaults for this data file - click the "OK" button. We now have a secondary data file at our disposal to destroy. Expand the "Location column" and commit it to memory (or write it down). We will be deleting this data file later in the exercise.
(back to top)
GUI Steps:
1) In Query Analyzer, insert a row into the shippers table and then select from the table to make sure that our insert was accepted
2) Start the steps to perform a full database backup by going into Enterprise Manager, right clicking on the Northwind database, then choosing "All Tasks" and "Backup Database".
3) The Backup screen comes up. Choose the database name (Northwind). Name this backup Northwind Full. Make sure that the "Database - Complete" radio button is selected. Also make sure that you choose a destination (if no destination is chosen click the "Add" button to select a destination). Then choose the Append to media radio button. Finally, click on the "Options" tab to go to the next step.
4) We are going to verify our backup. On the "Options" tab check the "Verify backup upon completion" check box. Then click the "OK" button to start the backup.
5) The full backup is complete. We're now going to add another row. In Query Analyzer, insert a row into the shippers table and then select from the table to make sure our insert was accepted.
6) Prepare to perform a differential database backup, by doing exactly what we did in step 2 to bring up the backup screen. (In Enterprise Manager, right click on the Northwind Database, then choose "All Tasks" and "Backup Database").
7) The backup screen comes up. Perform a differential backup. Choose the Northwind Database and name the backup "Northwind Differential". For a backup, choose "Database - Differential". Choose your backup device and append to the device, then click on the "Options" tab so we can verify our backup.
8) Verify the backup as performed in step 4. Check the "Verify Backup Upon Completion" box, then click on the "OK" button to start the backup.
9) The full and differential backups are now complete. We're going to insert a row to store in our transaction log backup. Do the following within Query Analyzer.
10) Perform a transaction log backup, by doing exactly what we did in step 2 to bring up the backup screen. (In Enterprise Manager, right click on the Northwind Database, then choose "All Tasks" and "Backup Database").
11) Now perform the Transaction Log backup. Choose Northwind as the database, and name the backup "Northwind TRANSLOG". Select "Transaction Log" for the backup type. Then choose your backup device and append to the media. Click on the "Options" tab to verify the backup.
12) Verify the backup as performed in step 4. Check the "Verify Backup Upon Completion" box, then click on the "OK" button to start the backup.
13) Add one more row
14) Stop SQL Server by right clicking on the SQL Server icon in the systray. Choose "MSSQLServer-Stop". Then click 'YES' when you are asked if you are sure that you want to do this.
.
15) Delete the Northwind secondary data file (or files). That we set up in the setup portion of this exercise. We deleted the file located at C:\Program Files\Microsoft SQL Server\MSSQL\Data\Northwind_Secondary1_Data.ndf. The data file is now completely gone.
16) Start SQL Server by doing what was shown in step 5 only select 'MSSQLServer-Start'.
17) In Enterprise Manager, we can see that the database is suspect as shown below. In a typical restore and recovery situation, we would try to determine exactly what the issue is. We know that we are missing a secondary data file. Since we are missing a secondary data file, and not a primary data file, we will first backup the transaction log. Right click on the Northwind database then choose "All Tasks" and "Backup Database" as shown below.
18) From the "General" tab, name your backup, take all of the defaults and click the "OK" button.
19) Now we can start the restoration process. In Enterprise Manager, right click on the "Databases" folder, then choose "All Tasks" "Restore Database".
20) The "Restore Database" screen pops up. Choose "Restore as database" Northwind. Choose the "Database" radio button. In the "Parameters" section, choose Northwind for the "Show backups of database" item. Four backups are shown as indicated in the image below. By default, SQL Server wants to restore our full backup, followed by the differential backup, followed by the two transaction log backups. These are hierarchical, note that the size of a Full database backup is much larger than a differential, which is significantly larger than a transaction log backup. Normally, we would just click on the "OK" button at this point to do a full restore and recovery. But we're going to do an incremental recovery.
21) Scroll down to the TRANLOG2 backup and uncheck it. Then click on the "Options" tab.
22) On the options tab, check "Leave database read-only and able to restore additional transaction logs. This will allow us to see how the incremental restore is progressing. Click ok and the Full and differential logs are restored.
23) In query analyzer, check our status. Note that the full and differential backups have been successfully restored.
24) Restore the remaining transaction log. Repeat step 19 to bring up the "Restore Database" screen. We're going to take all of the defaults. SQL Server recommends that we now restore the transaction log, which we will do. Also, if you go to the Options tab, you will see that the radio button "Leave database operational. No additional transaction logs can be restored" has been checked. This is what we want. Hit the "OK" button on either screen and our restore is ready for access.
25) Validate that the restore is complete by selecting from the shippers table
The restore is complete.
(back to top)
T-SQL Steps:
1) In Query Analyzer, insert a row into the shippers table and then query the table to make sure that our insert was accepted
2) Still in Query Analyzer, back up the database using the following command.
3) Insert another row into the shippers table as shown below.
4) Create a differential backup to record the change that was made in the previous step.
5) Add a final row to the table.
6) Perform a transaction log backup as shown below
7) Add one more row
8) Shut down the database
9) Delete the file that we added during the setup portion of this exercise, then restart the database by right clicking on the SQL Server icon in the systray and choosing "MSSQLServer - Start"
10) In Enterprise Manager, you will notice that the database is labeled as suspect. Normally we would go through troubleshooting steps to determine what the problem is, but in our case we know that we have lost a secondary data file.
11) Since we still have a primary data file and a transaction log, we will be able to back the transaction log up so that we can preserve any transactions that were made since the last database backup. Enter the following command in SQL Server so that we will be able to restore our fourth insert.
12) Now we need to do the restore. We need to find out what files to restore from our tsqlbackup device. The position column is the key column because it will tell us where within the device each of our backups lie. Our full has position 1, followed by our differential at position 2, and our two transaction log backups at positions three and four.
Key columns to be concerned about with the RESTORE HEADERONLY command (for further information see Books Online):
- BackupName - Name that we provided our backup. Note if you are backing up files with the same name to the same device, you may have repeats in this column, the position column will tell you which is the most recent (the highest number in the position column provides the most recent backup)
- BackupType - 1=Database, 2=Transaction Log, 4=File, 5=Differential Database, 6=Differential File
- Position - Position of the backup set in the device
- BackupStartDate (not shown in image) - When backup started
- BackupFinishDate (not shown in image) - When backup finished
13) While connected to the master database in query analyzer, perform an incremental restore by issuing the following statements.
The use of STANDBY allows the database to be opened in read only mode, but still allows us to apply more transaction logs. We are using the file values provided in the query results from step 11. We have restored the full and differential backups and are able to query the results since we are in read only mode.
14) Query the database just to check our progress - you wouldn't normally do this, but we're just restoring incrementally in this exercise. You'll notice that the first three inserts have been successfully restored.
15) Finally, we restore the last transaction log.
16) Query the database to ensure everything has been restored to the way it was.
Our restore has completed successfully.
(back to top)