As shown in the previous post that in a situation where a data file is missing or could not be brought online, there is no backup available and has been determined that the data resides in the data file is no longer needed and does not need to be restored, one way to resolve this is making the file/filegroup defunct allows certain database operations like backup to be completed successfully.
Warning. The only way to bring back offline or defunct file online is restore from a backup that was taken prior to making the data file offline/defunct.
As making a data file/filegroup defunct resolve immediate issue, however it may generate problem during SQL Server upgrade. Let's see some example. First we create a database with primary and secondary filegroup on a SQL Server 2008 instance,
Note: If you have performed the steps as in the previous blog post, you may need to delete the example database (testdb), its data files and backups for the steps below.
USE master; GO --Create database CREATE DATABASE testdb ON PRIMARY ( NAME = testdb_data, FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb.mdf' ), FILEGROUP FG1 ( NAME = testdb_file1, FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_file1.ndf' ) LOG ON ( NAME = testdb_log, FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_log.ldf' ); GO
Creates a table on Primary filegroup and index on the secondary filegroup
USE testdb;
GO
--Create table on primary filegroup
CREATE TABLE tbl1
(
col1 int IDENTITY (1,1) CONSTRAINT PK_tbl1_col1 PRIMARY KEY,
col2 varchar(50)
);
GO
--Create index on FG1 filegroup
CREATE INDEX IX_tbl1_col2
ON tbl1
(
col2
) ON FG1;
GO
--Insert data into table and index
INSERT INTO tbl1
VALUES ('ABC');
GO
At the moment, the database has all the data file online. Let's backup the database for later use.
--Backup for later use BACKUP DATABASE testdb TO DISK = 'testdb_good_backup.bak';
Make the secondary data file defunct.
--Make the data file defunct ALTER DATABASE testdb MODIFY FILE ( NAME = testdb_file1, OFFLINE ); GO ALTER DATABASE testdb REMOVE FILEGROUP FG1; GO
This query below shows that the data file is now in defunct state
SELECT f.name file_group, d.name file_name, d.state_desc file_state FROM sys.filegroups f JOIN sys.database_files d ON f.data_space_id = d.data_space_id; GO
Take a backup for the database with the defunct file
BACKUP DATABASE testdb TO DISK = 'testdb_defunct_backup.bak'; GO
In the newer version of SQL Server instance, in this case SQL Server 2014, restore the database from the backup with the defunct file.
USE master; GO RESTORE DATABASE testdb FROM DISK = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\testdb_defunct_backup.bak' WITH MOVE 'testdb_data' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb.mdf', MOVE 'testdb_file1' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_file1.ndf', MOVE 'testdb_log' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_log.ldf'; GO
The metadata of the index in defunct file doesn't affect the restore process.The database restored and upgraded successfully. Of course it will return error if the index is queried on the new version instance, just like in the original SQL Server instance.
Let's take another example. This time with the full text index. First, restore the database back to the status before the secondary data file becomes defunct. We will restore from the good backup file.
USE master; GO ALTER DATABASE testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO --Restore the database back to prior defunct RESTORE DATABASE testdb FROM DISK = 'testdb_good_backup.bak' WITH REPLACE; GO
Create fulltext catalog and fulltext index on filegroup FG1
USE testdb; GO --Create fulltext catalog CREATE FULLTEXT CATALOG catalog1 AS DEFAULT; GO --Create fulltext index on filegroup FG1 CREATE FULLTEXT INDEX ON tbl1 (col2) KEY INDEX PK_tbl1_col1 ON (FILEGROUP FG1); GO
Now make the data file defunct like how it was done previously.
-Make the data file defunct ALTER DATABASE testdb MODIFY FILE ( NAME = testdb_file1, OFFLINE ); GO ALTER DATABASE testdb REMOVE FILEGROUP FG1; GO
Take another backup. The backup consist of defunct data file with fulltext index.
BACKUP DATABASE testdb TO DISK = 'testdb_defunct_fulltext_backup.bak'; GO
In the new version of SQL Server instance, drop the testdb database and restore from the backup just taken.
USE master; GO ALTER DATABASE testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP DATABASE testdb; GO RESTORE DATABASE testdb FROM DISK = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\testdb_defunct_fulltext_backup.bak' WITH MOVE 'testdb_data' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb.mdf', MOVE 'testdb_file1' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_file1.ndf', MOVE 'testdb_log' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_log.ldf'; GO
The restore operation failed with error.
Processed 184 pages for database 'testdb', file 'testdb_data' on file 1. Processed 0 pages for database 'testdb', file 'testdb_file1' on file 1. Processed 7 pages for database 'testdb', file 'testdb_log' on file 1. Converting database 'testdb' from version 661 to the current version 782. Database 'testdb' running the upgrade step from version 661 to version 668. Database 'testdb' running the upgrade step from version 668 to version 669. Database 'testdb' running the upgrade step from version 669 to version 670. Database 'testdb' running the upgrade step from version 670 to version 671. Database 'testdb' running the upgrade step from version 671 to version 672. Msg 667, Level 16, State 1, Line 24
The index "" for table "" (RowsetId 72057594039369728) resides on a filegroup ("FG1")
that cannot be accessed because it is offline, is being restored, or is defunct. Msg 3013, Level 16, State 1, Line 24 RESTORE DATABASE is terminating abnormally.
and also this error in Windows application log and SQL Server log,
During upgrade, database raised exception 3602, severity 25, state 53, address 000007FED2F36365. Use the exception number to determine the cause.If we pay closer attention, we can see the the restore step completed successfully. The failure is during the conversion (upgrade) step.
With the query below on the original SQL Server instance, it shows that the object is one of the ifts (integrated full text search) internal table.
SELECT p.partition_id, o.name [object_name], o.type_desc, f.name [filegroup] FROM sys.allocation_units a JOIN sys.partitions p ON a.container_id = p.hobt_id JOIN sys.filegroups f ON a.data_space_id = f.data_space_id JOIN sys.objects o ON o.object_id = p.object_id WHERE p.partition_id = '72057594039369728'
Observation
As the data file becomes defunct, metadata of the objects in the defunct data file retained. SQL Server upgrade process attempts to upgrade objects (eg. fulltext index) to newer version including the ones in the defunct data file (as the metadata is retained), it leads to the upgrade failure. However, even the fulltext index was determined as no longer needed, it couldn't be removed as the data file is in defunct state and on the other hand it causes error during the upgrade. In this predicament, the only solution appears to be extract everything (minus the ones in defunct data file) from the existing database to a new database.
This issue is observed on upgrade from SQL Server 2008 to SQL Server 2012 and SQL Server 2014. Restore from SQL Server 2008 to SQL Server 2008 R2 doesn't have any problem as there is no upgrade step involved during the restore operation between these two versions.
No comments:
Post a Comment