June 19, 2012 at 3:16 am
We are preparing for upgrading to SQL2012 and in the past have always backed up the mssqlsystemresource.mdf and .ldf files.
We have quite a simple database with no changes to the default schema, so will i still need to back these files up?
I have tested our backups many times and never copied these files back, as it works with the default files. I also read this post:http://www.sqlservercentral.com/Forums/Topic859279-263-1.aspx which says it isn't necessary. However, the MS documentation explains how to back it up and most posts i read are about how to use xp_cmdshell to do it, so I'm just not sure what to do!
The only down side of backing it up is that i have to enable xp_cmdshell, so I'm not too fussed, but I'd just like to know in what situation it is necessary.
Many thanks
June 29, 2012 at 11:30 am
It is better to back it up and not need it than to need it and not have a backup. Live by those words and back it up every time.
June 29, 2012 at 12:52 pm
the resource database only changes when you patch SQL server so you only need to make a copy of it then (flat file copy). So do this manually at that point, its not worth doing via T_SQL on a regular basis.
As the files are in use by SQL I don't see how that would work anyway.
---------------------------------------------------------------------
June 29, 2012 at 1:01 pm
george sibbald (6/29/2012)
the resource database only changes when you patch SQL server so you only need to make a copy of it then (flat file copy). So do this manually at that point, its not worth doing via T_SQL on a regular basis.As the files are in use by SQL I don't see how that would work anyway.
Or upgrade it which is what the OP is aksing about, backing it up when they upgrade.
And the resource database files are not locked by SQL Server. You can copy them while SQL is running.
June 29, 2012 at 1:23 pm
Robert Davis (6/29/2012)
george sibbald (6/29/2012)
the resource database only changes when you patch SQL server so you only need to make a copy of it then (flat file copy). So do this manually at that point, its not worth doing via T_SQL on a regular basis.As the files are in use by SQL I don't see how that would work anyway.
Or upgrade it which is what the OP is aksing about, backing it up when they upgrade.
I was trying to be generic by using the term 'patch' - applying a hotfix, CU, service pack, any time you change SQL version, those are the points it should be backed up.
And the resource database files are not locked by SQL Server. You can copy them while SQL is running.
thanks, never even occurred to me to try, I always do flat file copies of master, model and msdb at the same time so have SQL down.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply