October 21, 2016 at 4:21 am
We test our user db backups by restoring them down to our non-prod backups on an almost daily basis. This morning as my coworker was fixing an issue with one backup / restore, it occurred to me that we don't similarly test our system database backups.
Does anyone know of a good method of testing system db backups without screwing up an instance?
I've never been able to restore master (for instance) as another database name. It's always failed when I tried it in 2005 and I don't think I ever tried in 2008. But I should absolutely be testing the system dbs otherwise we could lose a lot of information (especially msdb) if something goes pear-shaped.
Thoughts?
October 21, 2016 at 5:03 am
Restoring the [master] databases using another name is possible. In the past I myself have performed it on SQL2005 (if I recall correct) and SQL2008 (for sure) and a collegue of mine recently did it on SQL2012. I don't expect issues with restoring other system databases too. You could run into trouble if you perform an additional CHECKDB, because SQL somehow recognizes it as not being a 'normal' user database (especially the [master] restore). But when the restore completes you know the backup file is not corrupt :-). Corruption within the database can be checked on the original system databases.
October 21, 2016 at 5:06 am
you absolutely can restore any of the databases as any name, works fine.
When restoring master though, as hanshi says its not recognised as a user database, will likely be down to the information stored in the databases boot page
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2016 at 5:11 am
Okay. Maybe it was the checks that screwed me up the last time I tried.
Thanks for the verification. I appreciate it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply