Tuesday, December 11, 2012

Terminating Database Restoring process

You may occasionally want to terminate an ongoing restoration process of your database backup.
In MS SQL 2008 R2, I don't find there's an easy way of doing so, currently I don't believe there's a cancel selection or w/e for this kind of purpose. So in this case, we'll have to do it manually and brutally.

To start with:
Stop your SQL Server Agent service

Then stop SQL Server Service.

Delete/rename the .Bak file you were using to restore your database.
Delete the log files or db files has been created from the restore

Start both services.

Relog into SSMS and check, you probably will still see 'DB(Restoring.....)'

To get rid of this, do a manual restore from Wizard, using any .bak file you want name it to 'DB'.

Once done, this should go away.

Wednesday, December 5, 2012


A reminder,

Merge ...
WHEN Matched And (Search condition) THEN
Update Set ****

Update can only be use once in Merge statement, so if there're multiple conditions, you will need to specify all of them in (Search Condition).

WHEN Not Matched cannot use Update, Only Delete/Insert can be used.

To update Product RRP with source table, conditions: update using source if source RRP less than current RRP, or if current RRP is 0 but source isn't.

Merge dbo.Products ip
USING (Select vendor_code, min(RRP) as Rp FROM zab_Supplierpricelist Where RRP > 0 and vendor_code > ''
Group by  vendor_code) as ms
ON ip.zextpartno = ms.vendor_code AND ip.status in ('A','R') WHEN Matched and ((ip.ZRRP > ms.Rp) OR (ip.ZRRP =0 AND ms.RP > 0)) THEN