I just read Paul Randal's article in the August 2008 issue of Technet Magazine. It was quite interesting and I had seen those questions being asked several times in the SQL Server forums. I would recommend reading the article.
Paul answered the following questions:
- I accidentally attached a production database to a SQL Server® 2005 server and now I'm trying to attach it to the correct server, which is running SQL Server 2000. I've tried simply detaching the database and attaching to the SQL Server 2000 server, and I've tried doing the same thing with a backup and restore, but neither of these work. Why won't it work on my SQL Server 2000 server? This is the only copy of the database I have.
- We've recently redesigned our schema to use table partitioning on the main table—we've been told this helps improve performance. The database is stored on a single 120GB drive and the table is contained in a single filegroup. There's no sliding-window being done, just new partitions being added every week, and all data needs to be available online. Most of our queries process data from within a single week and there are a few that work across the last year. This seems to be easier to do in a single filegroup. Am I correct or is there more to it?
- We recently had a scare on one of our high-end database servers—a bad memory board was causing corruptions. We discovered these when random data started to appear in our application. We ran DBCC CHECKDB and found all sorts of corruption. Unfortunately, this was also present in our backups so we had to manually prune out the bad data.
Long story short—we replaced the bad hardware and turned on page checksums. We'd like to run regular consistency checks but we don't have a large maintenance window and our 2.4TB database takes a long time to check. What can we do?
You can read the article here: http://technet.microsoft.com/en-us/magazine/cc671180.aspx