September 26, 2011 at 2:13 pm
Any suggestions on how to document SQL Server backups so that you can easily and quickly find info on a specific database when you have a system of many SQL Server instance? Do you just use a spreadsheet or something tied to SQL Server like Extended Properties?
We use DPM for backups of everything and SQL Server DB's is just one of several backup types our DPM system handles. I'm interested in suggestions (if anyone has some) on a good way to document the backup details so that it’s easy to find backup info on something. I know how to quickly get this info but if something happened and I was no longer here and a new person came in what would be the best format/method of documenting the backup stagey in place across all the SQL Servers?
Hopefully that’s enough info to properly convey what I'm looking for.
Thanks
Kindest Regards,
Just say No to Facebook!September 26, 2011 at 2:23 pm
What do you mean by document? Or what do you need?
I've done a few things. First, always name the backup for the database, type, and date/time of the backup.
Adventureworks_full_201109260100.bak
That allows you to easily track a backup for a database. That doesn't help you with dev v qa v prod, but you could add that. I'd assume you can do something similar with notes and tokens for the DPM stuff.
The other thing I've done is keep my own admin database on servers. In there I track backups separately from msdb, along with config, patches, etc.
September 26, 2011 at 2:30 pm
Steve Jones - SSC Editor (9/26/2011)
What do you mean by document? Or what do you need?I've done a few things. First, always name the backup for the database, type, and date/time of the backup.
Adventureworks_full_201109260100.bak
That allows you to easily track a backup for a database. That doesn't help you with dev v qa v prod, but you could add that. I'd assume you can do something similar with notes and tokens for the DPM stuff.
The other thing I've done is keep my own admin database on servers. In there I track backups separately from msdb, along with config, patches, etc.
To literally document, put in writing somewhere be it a spreadsheet or word doc or SharePoint List the details of the backup strategy for each db on each server that is being backed up.
The fact we use DPM and not native SQL Server Backup to do our Backups may complicate this somewhat and make the question harder to address. The backup details like location of the backups, how often they are done and retained as well as when recovery testing is done is all in place an working. What I'd like is an easy way to get the Backup/Recovery details on one or more DB without having to remote into the DPM System and go thru its slow as molasses process to get these details. It would be easy if every DB had the same Backup/Recovery management but they don't and shouldn't. With a dozen servers running and each having anywhere from 1 to half a dozen DB's on them it’s not easy (at least for me) to remember the complete details for every DB. And even if I could remember it would be a good idea to have those details documented.
Does that make more sense?
Kindest Regards,
Just say No to Facebook!September 26, 2011 at 2:38 pm
The problem with documentation is that it will perpetually be out of date. And at the point that you need to reference something is when the process will have changed.
Ultimately you need self-documetation, or some automated method of tracking what's occurred. It won't necessarily get you a "this database gets a full backup on Tue/Thur/Sat and a diff on Mon/Wed/Fri/Sun" listing, but you could build something that grabs the last backup data every day and stores it. At least then you'd have the history to see when the backups were made and of what type.
msdb stores this, but it doesn't necessarily survive purges, restores, etc. DPM probably stores it as well and maybe you can pull the data back to a central db once a day and run your own reports or formatting.
September 26, 2011 at 3:30 pm
Steve Jones - SSC Editor (9/26/2011)
The problem with documentation is that it will perpetually be out of date. And at the point that you need to reference something is when the process will have changed.Ultimately you need self-documetation, or some automated method of tracking what's occurred. It won't necessarily get you a "this database gets a full backup on Tue/Thur/Sat and a diff on Mon/Wed/Fri/Sun" listing, but you could build something that grabs the last backup data every day and stores it. At least then you'd have the history to see when the backups were made and of what type.
msdb stores this, but it doesn't necessarily survive purges, restores, etc. DPM probably stores it as well and maybe you can pull the data back to a central db once a day and run your own reports or formatting.
The details I'm talking about recording save for when the most recent backup was done have not changed for any of the DB's since we set them up in DPM to be backed up. I take it by your reply taht there isn;t something out there that most folks use for this kind of thing then ey? Documentation is always tough save for a few well defined things like documenting a DB Schema.
Thanks
Kindest Regards,
Just say No to Facebook!September 26, 2011 at 7:53 pm
Nothing I know of. Most companies I've worked on have had 2 or 3 schemes they used (often for dbs in full and simple), but there are times you have more. It's easy to think things don't change, or forget they did change.
If they haven't changed, are they not standard then? You can write something up, but in my experience, it's easy to lose documentation. You put it on the network and then people don't know what it is or where it is. I've stuck with storing the details in a db because DBAs seem comfortable with that and don't mind querying for it.
Maybe someone else will chime in with something different, but I've not seen db or server documentation survive over time if it wasn't self recording and updating.
September 27, 2011 at 10:04 am
Steve Jones - SSC Editor (9/26/2011)
Nothing I know of. Most companies I've worked on have had 2 or 3 schemes they used (often for dbs in full and simple), but there are times you have more. It's easy to think things don't change, or forget they did change.If they haven't changed, are they not standard then? You can write something up, but in my experience, it's easy to lose documentation. You put it on the network and then people don't know what it is or where it is. I've stuck with storing the details in a db because DBAs seem comfortable with that and don't mind querying for it.
Maybe someone else will chime in with something different, but I've not seen db or server documentation survive over time if it wasn't self recording and updating.
Steve –
Because we use DPM and our IT guys manage the DB backups with DPM (along with all other backups from the file server to Exchange) I don’t get to set the backup parameters. I tell them what DB’s on what servers need to be backed up and what level of recovery I need on each (i.e do I need real-time or something else ) and they handled implementing that in DPM. I could go on and on about the short comings of DPM (I’ve already re-written this 3 times to avoid being verbose) but to put in as short a description as possible, the limitations in DPM result in our having DB’s set to a Recovery that is less than ideal and that differs greatly amongst all the DB’s we have to have a Recovery plan for.
On the flip side....DPM is really great when it comes to Recovery time. The process of recovering a DB even our mission critical real time accounting DB that is close to 300GB, its wayyyy easy. I can recover it to any SQL Server on my network within 1 to 2 hours and that’s a full recovery and not simply replicating the most recent changes to a backup copy of the DB. Now maybe that’s not as good as I think it is but our executives are very happy with my being able to fully recover the DB within less than 2 hours from a full crash. I’m told by our network admin that once some networking upgrades are in place this time will go down and by more than just a few percentage points.
DPM has its pros and its cons and unfortunately the majority of its cons seem to fall on deal with DB backups. It’s great when it comes time to recover something and a pain when it’s time to implement the backup side. BTW – this pain is augmented when you’re dealing with SharePoint 2010 on SQL Server. UGGGHHH! If it tells you anything about DPM, all the tables and other DB objects in the Database that DPM uses to store its own data (info about what its backing up) all follow the old Microsoft Access naming standard. The tables all begin with tbl_ and all the Stored Procedures begin with prc_ and so on.
I guess I’ll create something myself for this.
Thanks for the replies
Kindest Regards,
Just say No to Facebook!Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply