SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to move database into app_data?


How to move database into app_data?

Author
Message
adrian-844207
adrian-844207
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 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 Smile

Thanks,

Adrian
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15903 Visits: 25280
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
adrian-844207
adrian-844207
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14311 Visits: 4639
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.
adrian-844207
adrian-844207
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 34
PaulB,

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

Adrian
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14311 Visits: 4639
Glad it worked the way you where looking for Wink

_____________________________________
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.
Bob Baker
Bob Baker
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29773 Visits: 19009
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?
Bob Baker
Bob Baker
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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?
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29773 Visits: 19009
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search