How do I move my database?
I am using VWD 2005 Express, with SQL 2005 Express. My web hosting company supports ASP 2.0 and SQL Server 2005, but have put SQL on a database server, which is separate from the application server. I successfully published a "Hello world" test application, but my database application failed after displaying the logon page, presumably when it first tried to connect to the database.
VWD has created my database within the application folder (app\App_Data\ASPNETDB.MDF) and created the configuration string in web.config: -
<
addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />This all works fine on my local development server, but naturally fails when the application is copied to the web host, and the database can no longer be run from an application sub-folder. With SQL 2000 the problem would have been simple to fix. I would have created a database in the host DB server. I would then have backed up the development database, and restored it into the target database. I would then have changed the connect string, and all would have been OK.
How do I get the development database into the target database with SQL 2005 express? I can't use backup/restore, since there is no backup within VWD 2005 Express, and although there is a backup within Management Studio Express (MSE) I would need to get the database into MSE first. I tried opening the MDF file, but MSE wouldn't recognise this. I can't find any way of exporting the database - the equivalent of SQL 2000's DTS is not available within MSE!!!!!!!!! I tried installing an eval. version of the full Management Studio, but the install failed because "there is no upgrade path".
Once all that's sorted (if it can be sorted - I may have to go back to SQL 2000), where can I find out what to write in the configuration string?
Thank you, Robert Barnes
hi,
User Instances only run on SQLExpress and not on "higher" editions of SQL Server 2005.. and I'm not that sure web hosting companies are offering this service as it could be very "expensive" in resources... if your web hosting company provide this feature/service, you should be able to perform the required path... if you are only licensed to use "standard" SQL Server behavior, you have to resort on "traditional" SQL Server database usage and not via User Instances..
regards
So how do I move the SQL 2005 Express database that I created into one of the versions that are licenced? I have a properly licenced version of SQL 2000 (and I have not used any of the new features in the development), and my web host has a properly licenced version of SQL 2005, but I can find no way of getting the database from my development environment into either of these. At the moment the only option that I can think of is to start the development again, using VWD 2005 Express but with a SQL 2000 database.
 Robert Barnes wrote: |
| < add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />This all works fine on my local development server, but naturally fails when the application is copied to the web host, and the database can no longer be run from an application sub-folder. With SQL 2000 the problem would have been simple to fix. I would have created a database in the host DB server. I would then have backed up the development database, and restored it into the target database. I would then have changed the connect string, and all would have been OK. |
|
It is the same with SQL Server 2005... With the express system you are using user instances to develop under. What you can do is use the management studio tools and attach your database file to the server, this will set it up so that you can back up the database..... Then all you have to do is to back up the database ftp it to the hosting database server and restore the database to the host server... Then the last this you need to do is change the connection string so that it will now talk to the server.
<add name="ConnectionString" connectionString="Data Source=<DBServerName>;initial catalog=<DatabaseName>;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
If using SQL Logons...
<add name="ConnectionString" connectionString="Data Source=<DBServerName>;initial catalog=<DatabaseName>;User=<UserId>;Password=<Password>" providerName="System.Data.SqlClient" />
Thank you - I think. I successfully installed SQL Server Management Studio Express, but how do I attach the database file? MSE connected OK to the SQLExpress server and I was able to create new databased OK. There's nothing in the help under 'attach', so I tried File\Open and navigated to the ... ASPNETDB.MDF created for the application, but this produced a message "There is no editor available for ....".
Regards, Robert
Thank you. Unfortunately I'm still having difficulties.
I am running the same version of the software:-
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
(just to make sure I downloaded it again, but nothing changed).
I right clicked on the Database node, and clicked Attach. I then clicked <Add>
=> problem 1: the folder view would not expand enough to get to the ASPNETDB.MDF file, but would only expand as far as "C:\Documents and Settings\Robertb.ROBERTB". I got passed this problem by pasting the full path, "C:\Documents and Settings\Robertb.ROBERTB\My Documents\Visual Studio 2005\WebSites\NZGDB\App_Data\ASPNETDB.MDF" into the file name textbox.
=> problem 2. This appeared to locate the file: both aspnetdb.mdf and aspnetdb.ldf appeared in the lower part of the dialog. However, they both had a message "not found". When I clicked <OK>, a messagebox said "An error occured when attaching the file. Check the hyperlink in the message box for details"
The hyperlink said "Attach Database failed for server ROBERTB\SQLEXPRESS .... Access denied ... Error 5133". A further message said "Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists"
I do, and it does. So I still can't manage it with MSE to take a backup to move it.
Is this because I'm using an instance of MSE with the server (ROBERTB\SQLEXPRESS) that is already managing this database, and this would all work if I attached to my laptop from another SQL and MSE instance?
Regards, Robert
hi,
| Robert Barnes wrote: |
| .. Is this because I'm using an instance of MSE with the server (ROBERTB\SQLEXPRESS) that is already managing this database, and this would all work if I attached to my laptop from another SQL and MSE instance? |
|
the database you want to attach must not be already in use by another SQL Server instance as SQL Server requires single user access to the managed files...
regards
I have tried with VWD open or closed.
I have tried with VWD closed after detaching the database in VWD (and closing it).
I even tried copying the .MDF to another location and trying to attach that. I didn't expect that to work, and I was not surprised.
I then tried this: -
In VWD:
Detach the database
Modify the connection to point to a database in the SQL Server Express instance
(My application is now pointing at a dummy database, so I didn't expect it to run untill I fixed this - but see below)
Close VWD
In MSSMSE: rightclick on Database, Attach, <Add>, copy in full path to the .MDF, click <OK>
=> Failure with the same messages as before
I was surprised to see that when I re-opened VWD, the previous database connection was restored without my doing anything.
hi,
| Robert Barnes wrote: |
| I have tried with VWD open or closed. I have tried with VWD closed after detaching the database in VWD (and closing it). I even tried copying the .MDF to another location and trying to attach that. I didn't expect that to work, and I was not surprised. |
|
if you move the database's file to another location where the account running the SQLExpress service has been granted enougth permissions you should be able to attach the database...
what are your SQL Server permissions?
| Robert Barnes wrote: |
| I then tried this: -
In VWD:
Detach the database
Modify the connection to point to a database in the SQL Server Express instance
(My application is now pointing at a dummy database, so I didn't expect it to run untill I fixed this - but see below)
Close VWD
In MSSMSE: rightclick on Database, Attach, <Add>, copy in full path to the .MDF, click <OK>
=> Failure with the same messages as before I was surprised to see that when I re-opened VWD, the previous database connection was restored without my doing anything. |
|
I'm confused... are you still using a User Instance via VDW? User Instances can (but not assumed) have enougth permissions to access databases external to the application path as they run with the permission of the current logged user.. if your account can access the file system you are pointing to, and you have the AttachDBFileName=hard_coded_path extended property set, you can obviously attach the database.. and this should be the case even if not running a user instance but with the extended property set if the account running SQLExpress has been granted permissions on the specified path...
regards
>I'm confused... are you still using a User Instance via VDW? User Instances can (but not assumed) have enougth permissions to access databases external to the application path as they run with the permission of the current logged user..
Everything I have done has been with my normal "home" profile, and my usual logon and password. I have not used any other profile or logon since the start of the project, even temporarily. I have full administrator rights on this laptop. I've done everything "out of the box", without changing any defaults.
As I started developing the application, the database was created from VWD from "Add new item", selecting SQL Database, and following the dialogs. At that stage I did not have SQL Server 2005 Express separately installed (although I did have SQL 2000). VWD set up the database file, and the SQL Server instance "Robertb/sqlexpress", and generated the connection string within The permissions were left as Windows defaults.
A few weeks ago, when I realised that I couldn't publish the test application because of the database, I downloaded SQL MSQ Express, again leaving everything at default values. So I should have no issues with permissions/authorities.
Using Explore I'm able to navigate to the database file at C:\Documents and Settings\Robertb.ROBERTB\My Documents\Visual Studio 2005\WebSites\NZGDB\App_Data\ASPNETDB.MDF. This produces a message warning me not to open it, and of course I haven't tried to open it from Explore.
In web.config the connection string has value: -
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
Should I change this?
Hi Robert,
Did you able to sort out this database connection with your host. I have the same problem. Please help me if you have the solution.
I am using the following connection string.
<
add name="GoalConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />what kind of changes I need to access my ASPNETDB.mdf .
please help me.
Thanks,
Siva
Siva, I'm not sure if I've met a dead end. I'm going to visit my host company this afternoon, put my laptop on their LAN, and try to access my database with their copy of MS Sql Server 2005 Enterprise Studio. I'm hoping that a full copy (not express) running on another computer, will do the trick. If that doesn't work, then the only thing that I can think of is to rebuild the database by hand, either with SQL Server 2000 or with 2005 Enterprise Studio Express. I'll let you know how I get on.
Cheers, Robert
Siva, we got a bit further, but we still haven't succeeded in moving the database to SQL Server 2005.
We succeeded in getting both my copy of Ms SQL Server 2005 Management Studio Express, and their copy of Management Studio to recognise my database after we created a folder \db within C:\Inetpub, and copied the folder ...\ASPNETDB into it. I'm not sure if the particular folder, C:\Inetpub, is significant, but my technie frient used it because this is where the local internet publisher looks. My Management Studio display then showed my 2005 Express server, my 2000 server, and their 2005 server. We were then able to backup my database, and restore it to another 2005 Express database on my laptop. However, when we tried to do this to their SQL 2005, we got a message about incorrect SQL levels. If I remember correctly, SQL Express 2005 is level 611, but SQL 2005 is level 539.
An approach that should work is to generate an SQL script. If this were a professional development and I could pass the cost on I'd use Redgate (http://www.red-gate.com/products/index.htm). I've used the excellent SQL compare and SQL data compare before, they're essential (IMHO) in managing SQL development. However as this is a hobby development we're experimenting with a MYSQL tool that generated a script containing some errors.
Hope this helps.
Cheers, Robert
