So, how do you uninstall Windows PowerShell v1.0?

This was a long-awaited post as I have been concentrating more on disaster recovery stuff for SQL Server

If you guys wanted to install PowerShell v2.0 CTP, the first thing you need to do is uninstall v1.0. Apparently, though, you won't find any of those usual uninstall process like using the Add/Remove Programs console or the Uninstall Windows PowerShell from your Program Files. Have no fear. You can always use your registry. Simply look for the UninstallCommand key from HKLM\Software\Microsoft\Updates\Windows PowerShell 1.0 The path would really much depend on your operating system's setup like what your service pack you are currently running. In my case, I am running Windows XP Service Pack 3 so the so the registry key for me would be HKLM\Software\Microsoft\Updates\Windows PowerShell 1.0\SP3\KB926139-v2 Run the Value Data from the UninstallCommand key to uninstall Windows PowerShell v1.0. Once you're done, you can now install v2.0. You'll be surprised that it now creates an entry in the Add/Remove Programs console. Remember, this is still in CTP so be careful not to deploy your scripts in production until its released or just bite the bullet

Another reason to be at TechEd Asia 2008: After Hours

There's more to it that the regular technical sessions to go to during TechEd Asia 2008. After Hours lets you be casual with the speakers and other TechEd Asia delegates. See great demos, watch your peers being launched as a conference speaker in a competition, or simply listen to talks about certification and IT trends. This fun-packed activities will make your attendance at TechEd Asia 2008 an unforgettable experience. Visit TechEd Asia 2008 After Hours for more information so you can plan your schedule way ahead of time

So, if you haven't registered yet, it's not too late. Register now!

Broken BACKUP and RESTORE sequence?

Have you ever had the to scratch your head because your database backups won't work even if you are practically sure that you have tested them properly? Imagine this - you have a regular FULL and DIFFERENTIAL database backups working together as part of your backup process. Your FULL backups run every Sunday and your DIFFERENTIAL backups run everyday. Then, all of a sudden on a particular Wednesday, your developers decided to restore a FULL backup of your production databases into your test environment to test a functionality. What happens? The backup sequence is broken. DIFFERENTIAL backups contain the changes since the last FULL backup. If you are following the sequence and working with the backups generated by your backup process, you would have done some tests using the Sunday FULL backup and the latest DIFFERENTIAL backup. Not in this case. You would need the FULL backup created on that specific Wednesday (which unfortunately was deleted after being restored on the test environment). Let me show you a sample code to demonstrate


USE master
GO

--FULL DATABASE BACKUP on Sunday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_FULL.BAK'
WITH INIT, FORMAT, STATS=10
GO

USE master
GO

--FULL DATABASE BACKUP ON Wednesday CREATED BY YOUR DEVELOPERS
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10
GO

USE master
GO

--DIFFERENTIAL DATABASE BACKUP AFTER THE LAST FULL BACKUP
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH DIFFERENTIAL, INIT, FORMAT, STATS=10
GO

If you're the DBA, you might be thinking that you still have your backup sequence intact and this is what you might have done should you need to restore

--RESTORE SEQUENCE STARTS
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_FULL.BAK'
WITH REPLACE, NORECOVERY, STATS=10
GO

--FAIL DUE TO INCONSISTENT RESTORE SEQUENCE
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH REPLACE, NORECOVERY, STATS=10
GO

Now, there are a couple of ways to solve this problem. One is to create a trigger on your MSDB database to trap events written on your backupset system table and check for the type column for D values. At least you get to be notified for any FULL database backups outside of your normal backup process. In SQL Server 2005, it's as easy as using the COPY_ONLY option in the BACKUP command. This specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. Make sure that your operational DBAs and developers who work on SQL Server 2005 know how to use this option whenever thay run ad-hoc backups. Here's how to use the COPY_ONLY clause on your BACKUP commands (with emphasis on COPY_ONLY)

--FULL DATABASE BACKUP on Wednesday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10, COPY_ONLY
GO

So you can recover from database snapshots...but...

SQL Server 2005 has given us the option to create database snapshots to create a point-in-time image of the database. This gives us the option to restore from a point-in-time in case of user errors, like maybe accidentally truncating a table. MSDN has provided us a procedural approach on how to restore from database snapshots. But what I am more concerned the most is that I have already lost a lot of data from the time the snapshot was created to the point in time that the error occurred. And this is where your understanding of what happened and how you implement your disaster recovery plan come into the picture. As far as Microsoft is concerned, restoring a database from a snapshot overwrites the original source database. But here's what I'd do. I'd first backup the tail of the log before I restore from the database snapshot. After restoring from the snapshot, I'd restore my backups into another copy of the database and restore the tail of the log. This will be the state of the database after the accidental error occurred. Since I am concerned about the new records that were added after the accidental error, I would retrieve those from my database copy and isert those on my restored database. This will take a lot of work as you will be comparing the tables from the original and the copy database. At least you managed to restore the records faster than restoring from a FULL backup. There is no native way to solve this but there are a lot of third-party utilities that can just rollback a specific command from the transaction log. You can use the TableDiff utility to do this although it wasn't meant to solve this type of problems