Bill (fluffydeadangel) (3/18/2013)
1. Is the "setup" file is still good to use if my sql server is patched to SP2 after installation?If this is one of the system database files that are in other locations on your server... yes. it will upgrade it self to SP2 when you start it. You can use this to get SQL Server up long enough to restore master from your last good backup.
2. What else I can try to recover system files if I have the backup files?
(last ditch effort short of reinstall from scratch) Please only do this if you're familiar with the process or at least all of the moving pieces.
If you have a server where master/msdb/model/tempdb are all setup in the same location as your server you're having an issue with (if you only install with defaults and all) copy your existing files into a folder marked old, place these new files where the old ones were... then start up SQL server, normally around here. 'C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn'
in command line run ''C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn\sqlservr -m' If that's where your sqlservr.exe is located (adjust accordingly)
for a named instance use
''C:\Program Files\Microsoft SQL Server\MSSQL_10_50\MSSQL\Binn\sqlservr -m -s<Instance Name>"
next open another command prompt window and run sqlcmd.
'RESTORE DATABASE master FROM <backup_device> WITH REPLACE'
when you restore master, you should be able to restore the rest of the system databases as well from backup.
I hope this leads you at least in the general area.
Thanks Bill for the reply! More questions for you 🙂
1. What do you mean by "If this is one of the system database files that are in other locations on your server... "?
I found out that I have to copy SQL install folder locally to rebuild system databases. Use the setup file from C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release does not work
2. I am following msdn document to rebuild resource databases and it says sp need to be applied afterwards. I got error saying there is nothing to be patched when I tried to apply the patch. So my question is if my SQL server 2008R2 is patched to SP2 and I need to rebuild the resource database. How do I apply the patch to resource database? Can I just copy it from other machine that has the same SQL version?
"Rebuild the resource Database
--------------------------------------------------------------------------------
The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
Launch the SQL Server 2012 Setup program (setup.exe) from the distribution media.
In the left navigation area, click Maintenance, and then click Repair.
Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
On the Select Instance page, select the instance to repair, and then click Next.
The repair rules will run to validate the operation. To continue, click Next.
From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
"
Thanks
Clare