I am currently on a project to review some older servers (SQL 2000 and 2005) for SIMPLE recovery (and therefore missing LOG backups) and for missing backups altogether!
I haven't worked with SQL 2000 much for a while, and as such it was a little trial and error (and more than a little Google) to work out the appropriate syntax to look up recovery models in SQL 2000:
select name, databasepropertyex(name, 'Recovery') as RecoveryModel
from master.dbo.sysdatabases
order by databasepropertyex(name, 'Recovery'), name
This shows me which databases are in SIMPLE recovery and so need to be changed to FULL. I manually changed the appropriate databases from SIMPLE to FULL recovery. The next step is to add LOG backups to these databases - or is it?
For this project, it is not, because they use dump devices (remember those?) for their disk-based backups. No big deal, right? Just create new dump devices for the new log backups.
(Remember Enterprise Manager?)
The catch in this case is that the existing dump devices are large multi-part names (REMOTE_DUMP_<Servername>_<DatabaseName>_DATA_DUMP) and I wanted a way to copy-paste or programmatically add the devices based on those current models. This was further complicated by the fact that the names are not easily copy-pasteable from the properties window in Enterprise Manager:
So now I needed to find the table where this information was stored. I remembered that they were added with the system stored procedure sp_addumpdevice, but where is the data actually at?
After some further Googling of sp_addumpdevice I found that the information is stored in sysdevices - but not msdb.dbo.sysdevices where I expected it to be; it is in MASTER.dbo.sysdevices:
select *
from MASTER.dbo.sysdevices
order by name
Rather than programmatically building dynamic SQL, I used the poor man's dynamic SQL and copy-paste-replaced values into the sp_addumpdevice string in Query Analyzer and created my devices, and then created LOG backup jobs and prepared to run the first LOG backup job to test it...
Changing from SIMPLE to FULL and then taking LOG backups makes everything good, right?
First a new FULL backup is required, or there is no continuity of the restore chain (at least not until the next FULL) - look at this:
To which point can this database be restored? Unless a new FULL backup is taken after the 9am switch to FULL recovery to start the restore chain, there is no LOG backup available of the time from 5am-9am that the database was in SIMPLE recovery, and therefore no point-in-time recovery available other than back to the 5am FULL backup.
(NOTE: this part - about requiring a new FULL (or DIFF) backup after the change from SIMPLE to FULL is true in *all* versions of SQL up through the present.)
I proceeded to take a new FULL backup, run the new LOG backup job (success!) and note my changes in our change system log.
-------
A few notes on this entry - first, if you are running SQL 2005 and up, use Hallengren maintenance! It wouldn't have helped me in this situation (since I was dealing with SQL 2000) but any time I deal with backups/re-indexing/etc. I am reminded how much I prefer dealing with Ola Hallengren's awesome maintenance solution.
Second, why did I take time to write this at all? I found that the web (including MSDN/Technet) has greatly decreased the amount of information easily Google-able about SQL 2000 since it went off mainstream support a couple years ago. This was evidenced by how difficult it was to find some of the information I needed from the days of old before Management Studio and the sys schema, and unfortunately some of us still have to deal with SQL 2000 on a semi-regular basis - so hopefully this helps someone else!
Stop Sign from http://www.flickr.com/photos/donkeyhotey/6503264653/sizes/m/in/photostream/
