How to bring the database online when one or more data files is accidentally deleted.
Follow the steps below when you don’t have other options like restoring from backup. This has to be the last step to bring the database online and remember data in file which is deleted will be lost. This might cause inconsistent data.
1. Take physical file level backup of all the database files
2) Rename all the data files and log files [Ex :N:\ Data.MDF to N:\Data.MDF_original ]
3) Create a new empty database with the same physical, logical file names and file groups
4) Now take the missing file OFFLINE
5) Take the database offline and replace the original files and try to bring the database online
The database will come online, only the missing file will be in OFFLINE STATE and will not be accessible.
If the missing file belong to separate file group we can remove the file group else you may have to export all the data from this database to new database else we cannot take FULL Database backup as one of the database file is not online for file group.
— Create New database
USE [master]
GO
CREATE DATABASE [MisTest] ON PRIMARY
( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
–Take the database offline
Alter database MisTest set offline
go
–Now rename the NDF file [to simulate the missing file scenario]
sp_configure ‘show advanced options’,1
go
reconfigure with override
go
sp_configure ‘xp_cmdshell’,1
go
reconfigure with override
go
xp_cmdshell ‘rename c:\MisTestNDF.ndf MisTestNDF_1.ndf’
go
–Try to bring the database online but it fails
Alter database MisTest set online
go
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
Msg 945, Level 14, State 2, Line 1
Database ‘MisTest’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
–Take the database offline and detach
Alter database MisTest set offline
go
USE [master]
GO
ALTER DATABASE [MisTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db@dbname = N’MisTest’
GO
–Now try to attach the database with missing file, but it fails with the below error
USE [master]
GO
CREATE DATABASE [MisTest] ON
( FILENAME = N’C:\MisTest.mdf’ ),
( FILENAME = N’C:\MisTest_log.LDF’ )
,( FILENAME = N’C:\MisTestNDF.ndf’ )
FOR ATTACH
GO
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
–Rename the original files
xp_cmdshell ‘rename c:\MisTest.mdf MisTest_1.mdf’
go
xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_1_log.ldf’
go
–Now recreate the Empty database with the same physical & logical file name
CREATE DATABASE [MisTest] ON PRIMARY
( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’ , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
–Mark the missing file as offline
Alter database MisTest
MODIFY FILE (NAME = ‘MisTestNDF’,OFFLINE)
–Take the database offline
Alter database MisTest set offline
— Replace the original file and bring the database online
xp_cmdshell ‘rename c:\MisTest.mdf MisTest_2.mdf’
go
xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_2_log.ldf’
go
xp_cmdshell ‘rename c:\MisTest_1.mdf MisTest.mdf’
go
xp_cmdshell ‘rename c:\MisTest_1_log.ldf MisTest_log.ldf’
go
Alter database MisTest set online
go
— The only problem is that we cannot take the backup of the database as the file is offline
backup database Mistest to disk = ‘c:\mistest.bak’
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The only possible backup operation is a tail–log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
–We cannot even remove the file as we get this error
USE [MisTest]
GO
ALTER DATABASE [MisTest] REMOVE FILE [MisTestNDF]
GO
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
–Now try to remove the file & file group
–Try the database back up again but it would fail
backup database Mistest to disk = ‘c:\mistest.bak’
go
Msg 3636, Level 16, State 2, Line 2
An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.
Msg 3046, Level 16, State 2, Line 2
Inconsistent metadata has been encountered. The only possible backup operation is a tail–log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
USE [MisTest]
GO
ALTER DATABASE [MisTest] REMOVE FILE [MisTestNDF]
GO
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
Msg 5042, Level 16, State 7, Line 1
The filegroup ‘Secondary’ cannot be removed because it is not empty.
Ignore the above error and proceed further.
ALTER DATABASE [MisTest] REMOVE FILEGROUP [Secondary]
GO
Now we can take full database backup normally
SQLServerScribbles.COM 