I’ve seen a few posts about daily checks recently and thought I’d chip in.
What do I check and a very brief explanation why I perform this check.
Backups
- Have they all succeeded?
Disk Space
- Do we have plenty of space available for normal operations during business hours?
Database Size
- Have we seen a major increase in size (I monitor this because we manage client databases)
Read more…
Do you backup production databases? If you do, fantastic. If you don’t, well, cross your fingers and just about everything else and hope that you don’t suffer a failure of any kind!
I read a number of posts in various forums where a database has become corrupt or there is no backup and you get a vast array of answers as to why there is no backup in place. In some cases, you may find that somebody has had the production databases dumped on them because they mentioned that they saw a SQL statement one time. While it’s still not really acceptable that there may be no backup in this scenario, it is understandable. However, if you are in this situation, then I would suggest you read Books on Line and ensure your data is backed up – don’t forget the restores too…A backup is no good if you can’t restore it!
For those full time DBA’s who have no backups, you have no excuse at all. Every DBA should be able to backup and restore databases in a variety of configurations and scenario’s.
I can’t stress enough the importance of backups.
Rant over! I’ll actually put together a series of posts to walk through backups and restores and the various configurations and scenarios.
We use Quest’s SQL LiteSpeed in our environment and its a fantastic tool.
I initially started off using the maintenance plans which look very similar to the maintenance plan designer that you will be used to in SQL Server 2005. It was a very basic plan consisting only of a full backup of all databases and then delete any backups over 1 day old.
However, we would occasionally run out of disk space before the backups completed which meant some manual backups and deletions the following morning. This is where you you may ask me why I didn’t just run the delete process before I took the backups. Well, call it paranoia, but I prefer to have a full backup in place before I delete the old one.
In the end, I decided on a single scheduled job. Within the job, I would have two steps for each database. The first step would be to backup the database and the second to delete any backups over 3 days.
To do this, I use the extended procedure xp_slssqlmaint. The full syntax would be:
EXECUTE master.dbo.xp_slssqlmaint N’-MAINTDEL -DELFOLDER “S:\MSSQL\Backups\master” -DELEXTENSION “BAK” -DELUNIT “3″ -DELUNITTYPE “days” -DELUSAGE’
Well, as the title reads, I am working from home today – Yes, it is a Saturday and I should be out on the golf course (in an ideal world).
Working on a few things this weekend on our hosted environment. Firstly, moving some of ours clients to dedicated drives, altering fill factors across the board and finally moving a few clients to the alternate server (we run an active/active cluster with our client split into regions (US/Europe)).