Archive for Tools
Restore SQL Database
Posted by: | CommentsRestoring a SQL Server database from a backup is easy to accomplish in the SQL Server IDE. Well, sometimes. In the real world, I often need to restore a development database, but the restore fails because I cannot gain exclusive access to the database. Some developers will detach the database and reattach it to grab exclusive access. If there are processes running frequently against the database, that may not work. Neither will killing all connections always work. The solution I’ve found is a short snippet of SQL to grab exclusive access, do the restore and then restore multi-user access. It looks like this:
USE master — Must be connected to a different database
-- Get exclusive access ALTER DATABASE XXXXXX_Stage SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- Restore from disk -- Note WITH REPLACE http://msdn.microsoft.com/en-us/library/ms178615.aspx RESTORE DATABASE XXXXXX_Stage FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\XXXXXX_Stage _20100505.bak' WITH REPLACE -- Restore multi user access ALTER DATABASE XXXXXX_Stage SET MULTI_USER WITH ROLLBACK IMMEDIATE;
Note the WITH ROLLBACK clauses. You should take a production database down and make sure all transactions have completed. Then do a restore through the IDE. If you use the code above, you may lose some transactions or logs. This usually doesn’t matter in a test environment.
Have fun!
Technorati Tags: SQL Server,SQL,Code Sample,Database,Server,backup
Snippet Designed for VS 2008 and VS 2010
Posted by: | CommentsI love code snippets. Way back in the early VB days, I created add-ins for the IDE that allowed me to create and save an array of short snippets of code and automatically insert them into the code window. I like to use snippets for things that I either don’t use often enough to remember, or to quickly enter templates for things I use a lot to save time. Visual Studio .Net has a Snippet Manager and lets you create snippets in XML, but there is no built-in editor. There are a couple of editors available, some stand-alone and at least one called Snippet Designer that is integrated into the IDE. Read More→
