As a planned activity, we asked to move data and log files from UAT server to another location of DEV environment in the domain. After file transfer we wanted to attach the database in sql server to check integrity of database. While attaching the database, we got below error.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)….
C:\PAYROLLSYS.mdf is not a primary database file. (.Net SqlClient Data Provider) Microsoft SQL Server, Error: 5171
We have to bring back database online for development team in any cost.
What we did finally?
1. We created new empty database on dev server with same name.
2. After that we stopped Sql server services and replaced old mdf with new files.
3. Start Sql server services, now we can database in the list with no status.
4. Put database in Emergency / Single user mode using ALTER DATABASE Command.
ALTER DATABASE PAYROLLSYS SET EMERGENCY
ALTER DATABASE PAYROLLSYS SET SINGLE_USER
5.Run the DBCC CHECKDB PAYROLLSYS (database, REPAIR_ALLOW_DATA_LOSS) using master database that will allow us to repair damaged database with some data loss.
DBCC CHECKDB (PAYROLLSYS, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
6. Bring back database online using below command
ALTER DATABASE PAYROLLSYS SET MULTI_USER;
Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PAYROLLSYS_log.ldf’ does not match the primary file. It may befrom a different database or the log may have been rebuilt previously.
The Service Broker in database “PAYROLLSYS” will be disabled because the Service Broker GUID in the database(5E890F99-8E0C-4046-A2DE-E2E34DPAYROLLSYSAC31) does not match the one in sys.databases (759165DA-F1A9-4C87-9DF3-7D0D656EDC94).
Warning: The log for database ‘PAYROLLSYS’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken,and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDBto validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the databaseavailable for use, you will need to reset database options and delete any extra log files.
Finally we got the success to bring back database online for development team.