August 23, 2008 at 1:34 pm
Hi,
I am developing an ASP.NET site with Visual Studio 2008 Standard SP1 and SQL Server Express 2008 on Windows Vista SP1. Debugging is running via IIS.
Until now I've been using a regular database stored in the MSSQL\Data, but would like to move to the web project's APP_DATA dir so I can use version control with the database.
But how can I do this?
If I try to detach my existing database, move it into app_data and re-attach, SQL server moves it back into MSSQL\Data. When I try to create a new file-based database in app_data via Visual Studio via right-click -> Add new Item -> SQL Server Database it gives me this error message
>>Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251<<
This makes no sense since I am using SQL Server Express 2008.
So, how can I do this? Any suggestions and pointers to newbie-friendly tutorials would be greatly appreciated 🙂
Thanks,
Adrian
August 23, 2008 at 3:37 pm
Do not know if this is exactly your problem, but read the following posts
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3487915&SiteID=17
Seems as if many others are having a some what similar problem.
August 23, 2008 at 4:47 pm
Bitbucket,
Thanks for your reply, but I am not quite so sure that this is the same problem. They seem to have problems attaching a database, whereas I can attach it fine, but I can't find a way to keep that database in the app_data folder. Also, I am not using the SQL server RC, but the final release version.
August 24, 2008 at 11:44 am
1- Take a full backup of your database
2- Script database creation
3- Drop database
4- Alter database creation script to point to APP_DATA or whatever place you like
5- Run database creation script
6- Restore -force - your database
7- Be happy
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 25, 2008 at 5:48 am
PaulB,
Worked like a charm and exactly what I was looking for. Thanks very much! 😀
Adrian
August 25, 2008 at 8:41 am
Glad it worked the way you where looking for 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 22, 2008 at 9:07 am
Perhaps a dumb question related to an inability to enter the correct keywords in a Google search: You have the last step as Restore -force - yourdatabase. I cannot find any reference to the -force command line option. Please elaborate. I, too am trying to move a SQL Server database into a web site's App_Data folder for deployment using SQL Server Express with User Instance=true. TIA.
December 22, 2008 at 10:47 am
Why not just script the whole database (using the "all Tasks...generate scripts" functionality), and put THAT into version control. It will give you the ability to recreate it on the fly, and version control it, too. If you tell it to script the extended properties and add the DROP statements before the CREATE (in the scripting options), you will end up with a script that can be rerun as many times as you wish.
Moving your database from being continuously attached to auto attaching and auto-closing has significant implications on application performance. Unless this is something at doesn't get used much at all - I would NOT recommend moving your database, since you'll have to move it back when you're ready to deploy somewhere.
Sorry - but it sounds like you're taking a step backwards and not forwards.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 22, 2008 at 10:54 am
Thanks for the quick reply! I understand your concerns. The scenario is a Silverlight application that will be shrink-wrap deployed in small offices with 1-3 users. The attached DB will be called into from the web service living on the web site the Silverlight app is downloaded from in the small office network. I was thinking about deployment more than performance, and was thinking that once the site is hit, the web service is attached to the dB and won't be re-attached until the ASP.Net worker process re-cycles. Am I incorrect in this last assumption?
December 22, 2008 at 11:02 am
Bob Baker (12/22/2008)
Thanks for the quick reply! I understand your concerns. The scenario is a Silverlight application that will be shrink-wrap deployed in small offices with 1-3 users. The attached DB will be called into from the web service living on the web site the Silverlight app is downloaded from in the small office network. I was thinking about deployment more than performance, and was thinking that once the site is hit, the web service is attached to the dB and won't be re-attached until the ASP.Net worker process re-cycles. Am I incorrect in this last assumption?
Kind of. Here's what BOL says on this:
Unlike versions of SQL Server that run as a service, SQL Server Express instances do not need to be manually started and stopped. Each time a user logs in and connects to a user instance, the user instance is started if it is not already running. User instance databases have the AutoClose option set so that the database is automatically shut down after a period of inactivity. The sqlservr.exe process that is started is kept running for a limited time-out period after the last connection to the instance is closed, so it does not need to be restarted if another connection is opened before the time-out has expired. The user instance automatically shuts down if no new connection opens before that time-out period has expired. A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 22, 2008 at 11:18 am
Of course - if it really is for groups that small, sometimes a user instance will work. Just take a look at setting the timeout high enough, so that it's attached most of the time.
You may also want to mention something about the fact that the first call to the DB in the morning will be slow (since that will be the trigger to attach the DB again).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 22, 2008 at 3:11 pm
OK, so given that that may fit into my usage scenario, I'm back to my original question: What was meant by
6. Restore -force - yourdatabase
in the original reply to the question? PaulB?
Thanks for your digging and your patience. Best of the season to you and yours!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply