Did you check your MSDB.dbo.suspect_pages after a database restore?

In SQL Server 2005, the default behavior of a RESTORE command is to simply continue even if there are corrupted pages in your backups. The only way to find out if there are corruptions is when a user gives you a call saying that they could not query some records and probably gets an error similar to the one below

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0xf93a020c; actual: 0xf93a820c).
It occurred during a read of page (1:69) in database ID 10 at offset 0x0000000013c000 in file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SalesDB.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.

The first thing you need to do after a database restore is to check the MSDB.dbo.suspect_pages to see if there are suspect pages that need to be repaired. Books Online describes the structure of this system table and gives you an idea on what to restore should there be suspect pages. Bear in mind though that this system table can only hold up to a maximum of 1000 records and everything get cycled out so you need to do your own housekeeping.

The Enterprise Edition gives you an option to do a page-level restore should you have corrupt databases. A more low-level discussion on fixing damaged pages can be found on the SQL Server Storage Engine blog

You don't trust your database backups? Use mirrored backup media sets in SQL Server 2005

What could be more frustrating than knowing that your database backups went missing? This is specifically true if you are dealing with transaction log backups which are dependent on log sequence numbers. You don't want to lose a single transaction log backup in the chain. In previous versions of SQL Server, we just execute a copy command (or even ROBOCOPY) to copy the transaction log backups to a different location. In SQL Server 2005, we have the MIRROR TO clause. This specifies a set of one or more backup devices that will mirror the backups devices specified in the TO clause, which could be a tape, disk or network location.

To use the MIRROR TO clause, see the sample script below

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksDB2.BAK'
MIRROR to DISK = 'F:\AdventureWorksDB.BAK'
WITH INIT, FORMAT, STATS=10
GO

This creates a mirrored copy of the database backup of the AdventureWorks database to the F:\ drive. Although this creates a mirrored copy of the backup, it will definitely take quite some time to complete as it is writing on all the mirrored media set, thereby, increasing database your backup window. I did a test on this by using the MIRROR TO DISK clause with the AdventureWorks database and it takes like 10 times longer to do a backup with this option - the speed, of course, would be dependent on where you mirror your backups and its IO performance

I'd probably still stick to using the copy command in my backups as long as I get the same result. For more information on using mirrored backup media sets, check out this MSDN article

You need to backup the mssqlsystemresource database files

So, you think you have all the necessary database backups you need? Well, think again. This might be your mindset after moving from SQL Server 2000 to SQL Server 2005. SQL Server 2005 includes a read-only, hidden database that contains all the system objects. It comes in the form of mssqlsystemresource.mdf and mssqlsystemresource.ldf files located in :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ by default. This database is required by the master database and will definitely affect how your SQL Server service behaves during restarts. If you move the master database, you should also move these files no the same location. Since it is a hidden database, SQL Server won't be able to backup this database as part of your database maintenance plan. One approach you could use is to xcopy it to the location of your database backups on a regular basis.

MSDN has a documentation on what this is all about but let me tell you one thing - you MUSt include this in your backup procedures. If you want to have an idea of how critical these files are, stop your SQL Server 2005 service and rename the mssqlsystemresource.mdf file. You won't be able to restart your SQL Server service afterwards

Microsoft Source Code Analyzer for SQL Injection

With the recent mass SQL injection attacks, Microsoft has developed a new static code analysis tool for finding SQL Injection vulnerabilities in ASP code. Web developers can run the tool on their ASP source code to identify the root cause of the attack and address them to reduce their exposure to future attacks. You can download the tool from the Microsoft Download Center and use it on your own infrastructure to test. If you find any bug or what not, just go to this site to report it.

Now, this doesn't mean only web applications are prone to SQL injection attacks. Even Windows-based applications are so make sure you check them as well.