Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for November, 2010

Visual studio 2010 team edition database deployment and SQL replication support

Posted by rahmanagoro on November 2, 2010

I have been using Microsoft Visual Studio 2010 within our shop, we use it towards the management and version control of SQL databases, these databases are stored as projects under visual studio. It’s within an agile environment and releases can happen almost every weekend or during the week. We also use SQL transaction replication in order to maintain sets of reporting database servers.

In the last couple of weeks, I tried to do a deployment onto a publisher database and ran into and error with visual studio, it was trying to drop and object on the database whilst the object was being replicated. This obviously throws and exception on SQL

It’s official that Microsoft visual studio does not support SQL replication and the only known workaround to the problem is to change the deployment properties to ignore verification checks. This can either be done from the GUI or altering an XML file, which is what the GUI does.

You can get to the deployment properties by navigating to the following location from visual studio.

Project >> Properties >> Database deployment

Posted in Database development, Day to day queries, Management, Scripts, SQL 2008, SQL replication, Tips | Leave a Comment »

Restoring a database when an application or user is still connected

Posted by rahmanagoro on November 2, 2010

Restoring a database whilst application connections persists to the database. I have a .NET application which continually needs to query the database to fetch data therefore opening a connection to the database, the problem is that I need to refresh the data on testing with recent data from production. If one tries to restore the database, you get the error.

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In order to get around this problem, what I tend to do is set the database offline, do the restore and set it back online. This is to prevent the application from continuously connection to the database thereby failing the restore. The scripts are shown on this post.

Posted in Database development, Scripts, SQL High Availability | Leave a Comment »