How to bring the database online when one or more data files is accidentally deleted

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 taillog 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 taillog 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  
 
 

 

Advertisements

The user instance login flag is not supported on this version of SQL Server

“The user instance login flag is not supported on this version of SQL Server. The connection will be closed.”

When we use “UserInstance=TRUE” in connection string on instances which is not SQL Server express we will end with this error. “UserInstance=TRUE”  is supported only in express

Can high number of connection cause SQL Server to refuse connection?

sp_configure “user connections” will decide the maximum number of simultaneous user connections allowed on SQL Server.Default value for user connections is 32767. So unless we hange the value it wouldnt cause problems.

Connections will be refused if we exceeded max worker threads. Look at the SQL Server error log and identify if there are deadlocked scheduler dumps. If there is follow the steps in http://mssqlwiki.com/2010/06/15/how-to-analyze-deadlocked-schedulers-dumps/ identify on what resource your sessions are waiting (Not excatly session. It is thread or session with threads associated with it).

When you have the issue look at sysprocesses for session with Kpid and identify where they are waiting and check if the active sessions (Sessions with KPID) exceeded max worker threads.

sp_send_dbmail blocked and waittype is preemptive_os_getprocaddress

This will happen if you use user transaction when calling sp_send_dbmail. Do not call sp_send_dbmail with in user transaction.If you open a transaction and
send a mail with some attachment in first connection and dont commit . Open a second connection and send a mail using sp_send_dbmail it will not be queued and wait
till the first connection commits.You will also see preemptive_os_getprocaddress wait for second connection