Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to move database into app_data? Expand / Collapse
Author
Message
Posted Saturday, August 23, 2008 1:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 3, 2012 3:52 AM
Points: 11, Visits: 34
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



Post #557797
Posted Saturday, August 23, 2008 3:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:47 PM
Points: 5,571, Visits: 24,784
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.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #557804
Posted Saturday, August 23, 2008 4:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 3, 2012 3:52 AM
Points: 11, Visits: 34
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.

Post #557806
Posted Sunday, August 24, 2008 11:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #557857
Posted Monday, August 25, 2008 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 3, 2012 3:52 AM
Points: 11, Visits: 34
PaulB,

Worked like a charm and exactly what I was looking for. Thanks very much! :D

Adrian
Post #558069
Posted Monday, August 25, 2008 8:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #558177
Posted Monday, December 22, 2008 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:47 PM
Points: 3, Visits: 30
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.
Post #624026
Posted Monday, December 22, 2008 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 7,119, Visits: 15,005
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?
Post #624098
Posted Monday, December 22, 2008 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:47 PM
Points: 3, Visits: 30
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?
Post #624103
Posted Monday, December 22, 2008 11:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 7,119, Visits: 15,005
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?
Post #624111
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse