SQL Server Performance Forum – Threads Archive
sysfiles
Hello, How can I add new rows to system table "sysfiles"? What I like to do isWe don’t have any backed up from database "APP".
There are 2 volume groups "G" and "K"
It happened Bad stripe error "G" on the RAID systems.(H/W error)
Database "APP" consists with 10 data files included master data file and 1 transaction log file. Unfortunately, there was MDF file on volume "G". So, we tried to back up w/ MDF file only by using sql server backup agent several times, but we got the I/O error messages… but, it’s really incredible, we got a successful message. We suspected w/ the exclusion of I/O failed sector. Anyway, we needed to keep our database w/ verification. So, we tried migration the MDF file to another instance.
We exported the MDF file to another instance "TEST".
We thought it’s possible for us to update the "sysfiles" virtual table. but, it’s not possible to modify w/ that according as MS. The reason we have to update the sysfiles is that the table have all of the file info for the database "APP". After importing the master database file, it changed the info. of sysfiles. Originally, sysfiles have 11 entries but now is just 2 entries. So, we need to update the sysfiles to use the MDF file imported on the recovered system. We did as below. /* Permit to change the system tables. */
sp_configure ‘allow updates’, 1
go reconfigure with override
go /* Status "Loading" to "Emergency"
begin tran
update master.dbo.sysdatabases set status = 32768 where name = ‘app’
commit tran shutdown /* Start the service again */
Insert into ….etc… We just modified the mode w/ the value of ‘allow updates’ as 1 and status as ‘Emergency’.
But we failed to insert new rows to the ‘sysfiles’. What is the best solution to recover the system?
… Actually, we recovered that system. as below resolution.
We backed up w/ all of the data files to the local disk.
We verified the backed up data on the different instance by restoring. So, we got the all of the MDF files and correct sysfiles.
After backup the files, replaced w/ RAID controller. and also reconfigured w/ volume "G".
We moved the mdf created files to the location originally.
Attached all of the mdf files to the original instance.
It was successful. It took about 2 days approximately. I think there will be a much better resolution than this. I’m waiting for your answer. Thanks in advance.
Ted Kim
Sr. System Engineer
CJ Systems
First of all refrain adding any rows to system databases manually, this may affect the SQL instance performance. At a maximum you should run DBCC checks against the database and if not recover from the last good known backup. So what is the exact problem with the database & H/w?
Findout from event viewer &* Sql error log. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
How about restoring the last known backup and then applying the trannsaction logs? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>