Simons SQL Blog : Backup and Recovery

Do you do backups? Do you test them?

simonsabin — Thu, 11 Feb 2010 10:12:17 GMT

I recently read a story about a hosting company that was trying to restore a server and was having problems. http://www.theregister.co.uk/2010/02/10/hostv_outage/ To me this highlights one of the most common problems in disaster recovery, not that people aren't doing backups (that is the other common problem), but that they aren't testing the backup.

For any backup it is vital that you are testing the backup. What might go wrong you may ask. Well in my experience because backups are generally processing large amounts of data that means they are using memory and a lot of disk and using a lot of throughput, these are situations that your normal every day use may not be experiencing. You can therefore be experiencing issues with corrupt data being put in your backups without you knowing.

Whats more and this is my biggest experience, when using 3rd party vendors to manage backups the liklihood of problems with restoring is much much higher. IMHO it is therefore essential that you keep ALL versions of a 3rd party products software that you have used to do backups with. Too many times have I tried to restore a backup and found it not work because I've been on a new version, although having said that I've also found that I can't restore even with the currrent version.

The minimum you should be doing is to regularly restore from your backup and run a checkdb to ensure that your backups work.You should also be using checksum detection to check page checksums as the backup is processing and also to calculate a checksum for the backup.

If you don't test your backups your backup process could be pointless, and the last thing you want to do is find out when your database has just crashed and your website is down and loosing money.

Data Data everywhere even when you think its gone

simonsabin — Sun, 12 Aug 2007 18:09:28 GMT

The biggest problem I have faced in IT over the last decade has been loss of data.

I have had the problem at work where servers have crashed and lost data, more often than not due to failure of disks. I have also personally felt the pain in a hard disk losing its partition information, and frequently my wife's laptop dying and windows not being able to boot.

Whilst many businesses have been aware of the criticality of data for a while, data is becoming more critical to everyone. Many people don’t have address books anymore, and if they do, do they contain mobiles, IM addresses, email address etc. If you lose data the inconvenience and the pain is often great.

What always surprises me is,

1. The ease in which you can recover data

2. The inability of IT departments to do so

The most regular issue I have had is laptop hard drives becoming corrupt. This happened to my wife on a regular basis, at which point the IT departments said, “nothing we can do love, you’ve lost everything”. You take their word the first time. On the second occurrence I said thats tosh. Went off and bought a 2.5” USB hard drive chassis (£15) popped the drive in, ran chkdsk and heh presto it all worked a treat. I’ve done this a few times for friends as well. So simply put if you laptop dies (doesn’t boot up) and you have another PC, go and buy a USB hard drive case, pop your laptops hard drive in (remember where all the screws go) and run chkdsk.

When I lost my partition (with all my wedding photos on it) I hunted for a product that would read the disk. I tried a few, but many didn’t cut it, they all have a slightly different feature set. I happened upon Stellar and their product Phoenix http://www.stellarinfo.com/disk-recovery.htm I downloaded and had my data recovered in a few hours. Very simple and only for £50

My latest data issue wasn’t that recent. I had some photos done and was given them on a CD. At the time I tried to read them and couldn’t, I thought it was because the CD wasn’t finalised so my CD writer software finalised it. In hindsight I realise the issue was that the CD had been written on a Mac and not a PC and so used HFS and not FAT or NTFS. At eh time I scoured the web trying to find a means of reading the CD and none worked. Today I came across the CD again and did another hunt. This time I found ISOBuster (http://www.smart-projects.net/cdrecovery.php) I downloaded the free version and was pleasantly surprised that it could read the CD including the HFS session and whats more I could extract the files, for free. On looking ISOBuster does a huge amount more, and I would strongly recommend the product.

The morale of the story is that the data is always there, and it’s not too difficult or expensive to recover.

Remember even if you think your data no longer exists on your machine you are probably wrong. The standard way of removing data is to completely rewrite the disk. From my limited knowledge 7 times is the best compromise between time and security.

So if you are ever going to give your PC away, make sure you don’t give you hard disk. Of get it professionally cleaned.

SQL Server The Truth - Rebuilding a clustered index does not rebuild the non clustered indexes

simonsabin — Fri, 20 Jul 2007 09:32:49 GMT

It used to be fact that the leaf page of a non-clustered index pointed to the row for the page, because it pointed to the file, page and row if the file or page of the row changed the nonclustered index had to be updated.

Now however the non-clustered index has the Unique Clustering Key value for the main data row. I specifically say Unique because you can create a non unique clustered index. In this case SQL generates an additional bit of data to make a unique key for each row in the table. (One reason why clustering on a non-unique column is bad).

What this means is that the data row can move about all over the place with out affecting the non-clustered index.

So what happens when you, create a clustered index, reindex a clustered index, recreate (using with drop existing) and drop a clustered index. This question came up in a recent presentation I was sure of the first and last, but not of the middle 2 so I did a repro and was pleasantly suprised.

The first and last both result in the non-clustered index being changed, the middle two however don't.

The example below puts the index on a different filegroup and looks at the IO for each filegroup after the operation. File 3 relates to the nonclustered index and you can see that the index creation causes IO, so does the creation of the clustered index (changing the row pointers to key values). But the reindex and the create with drop existing doesn't.

So in summary if you need to rebuild a clustered don't drop it and recreate, instead use alter index, dbreindex, or with drop_existing.

if not exists (select * from sysfilegroups where groupname = 'indexes')

begin

alter database blogcode add filegroup indexes

alter database blogcode add file (name=indexes, filename='c:\indexes.mdf' , size=10) to filegroup indexes

end

declare @dbid int

drop table simon

create table simon(

clusteredCol int identity(1,1)

,col2 uniqueidentifier )

insert into simon(col2)

select top 20000 newId()

from syscolumns a

cross join syscolumns b

select identity(int,1,1) id,fileId,cast('start' as varchar(30)) as action,numberreads,numberwrites into #stats

from ::fn_virtualfilestats(db_id(),null)

create nonclustered index IX_simon_col2 on simon(Col2) on indexes