SQL Server Restore database with exclusive access

   1: ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

   2:  

   3: RESTORE DATABASE MyDatabase FROM DISK = 'X:\Path\mybackup.bak'

In effect this rolls back any pending transactions, and gives you exclusive access to the database.

NHibernates SchemaUpdate feature

Ayende wrote about Setting Up Zero Friction Projects - Data Access, and mentioned the new SchemaUpdate feature in NHibernate.

The idea being that the ddl is automagically created from the nhibernate hbm.xml files, and executed against the delvelopers local database.

Something like:

1. Developer A changes the Order.hbm.xml where he adds a new field Discount.
2. In ApplicationStart the ddl alter script is generated and executed against Developer A's local database, the next time the developer starts the application.
3. Developer eventually does a check-in of the Order.hbm.xml, along with the other changes.
4. Developer B does a get latest.
5. When he starts the application, ApplicationStart executes the change dll, and now Developer B's local database is up to speed.

At what cost? Well, almost zero :-)

I have created a sample C# VS2008 Solution, that can be downloaded here.

How to restore to another database

That is take a backup of database A and restore the .bak file to database B.

By far the easiest way is to do it in script:

restore database myDatabase from disk = 'C:\mybackup.bak' with replace

No more opening of SSMS (yawn), replacing file paths and remember to set a overwrite flag.

How to compare 2 tables in SQL Server

Jeff has a great post titled The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

No joins or select in. Just pure UNION magic.

How to clear SQL Server query cache

If you execute the same query two or more times in a row, the time it takes to complete might vary. If you are the only user on the database, it is because of the build in query cache.

The cache can be cleared with the following script:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE