Post reply

Backup and Restore Confusion

  • September 30, 2005 at 12:44 am

    #110487

    Hi All, am fairly new to SQl server. Up until a few weeks ago i was using enterprise mgr on my laptop and connecting to a database on my webserver without any issues. I could backup the webserver and restore to my laptop and all seemed fine.

    I recently upgraded the laptop. Now when I attempt to restore my server database to the laptop, I cant run and Sql queries on it. Previously I could run select top 1 * from administration from within my ASP code without any issues. Now I have to prefix the tablename with davbil_community which is the database name on the server. eg. select top 1 * from davbil_community.administration

    I dont understand why things have stopped working.

    Thanks for any assistance.

    Regards

    David

  • September 30, 2005 at 3:17 am

    #594277

    It could be that the default database for the ASP was changed.

     

  • September 30, 2005 at 11:22 am

    #594380

    As Jo stated, It sounds like the connection string you are using in the asp code doesn't have davbil_community as the default database.

    Regards, Jim C

  • September 30, 2005 at 4:29 pm

    #594420

     

    Thanks for the feedback, the connection string is fine. I have a simple SQL interface to run SQL direct against the database from ASP using a textarea to enter the SQL. I can connect to the database without any issues.

    One item I didnt mention is that the database name on my laptop is not davbil_customer, this is the database name on the remote server. So  I restored from davbil_customer on a remote server to a database called ccnew on my laptop. I changed the connection string to connect to the ccnew database and then found I couldnt run any queries against it unless I prefixed the old database name.

    Even if I attempt to run the query via query analyser, it changes my SQL to include the old tablename.

    eg. from select top 1 * from administration to select top 1 * from davbil_community.administration

    The pane on the left in query analyser that shows the tables even has the tablenames as davbil_customer.tablename this is what twigged me to why my queries were not working.

    So if I connect to the database from asp and run select top 1 * from administration

    I get an error

    Microsoft OLE DB Provider for SQL Server (0x80040E37)

    Invalid object name 'adminstration'

    If I run select top 1 * from davbil_community.administration it gives the expected results. So the connection string is fine, it is something to do with the structure of the database after I restored the backup.

    This is most confusing as if I run the same query on the remote server as select top 1 * from administration, I get the expected results. It is almost like the restore renamed the tables to prefix the remote database name.

    Just in case it sheds any light on the subject, here are my connection strings.

    LaptopConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccnew;Data Source={sql server instance};User ID=customer;Password={myPassword};"

    ServerConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=davbil_customer;Password={somepassword};Initial Catalog=davbil_CustomerCommunity;Data Source={sql server instance}"

    thanks

    David

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply