none
Unable to back up my SQL Server 2005 database

    Question

  • I am new to SQL Server and so far have been finding it useable but opaque and confusing. 

    I created a SQL Server database by upgrading my Access database, then I added ASPNET files to it.  I have been using the database successfully for developing a website using Visual Studio 2008.  I now need to modify the database.  Prior to making any modifications, I wanted to be sure I had a good backup. 

    I am trying to use SQL Server Management Studio Express to do the backup.  When I try to do anything with my database in this application, I get various error messages (5173-unable to attach database, 945-inaccessible file) or nothing happens.  I am not able to expand the database (nothing there).  When I right click on the database, the backup and restore options are greyed out in the dropdown menu.

    The database does not seem to be corrupt.  I can view it, expand it, query it, and use it to run my website in VS2008.  I closed VS2008 before trying to back it up as I assumed that having it in use would prevent a backup.

    Looking for solutions to the problems I'm having in SQL Server Management Studio, I find various complex and inscrutable solutions that mostly seem to involve the log file.  Everyone seems to have a different method and I am hesitant to try any of them for fear of making things worse--since I presumably do not have a backup.  (I gather that simply and easily copying the file the way I would copy an Access db would not serve as a backup.)

    What is the log file anyway and why do I need it?  I cannot open it in any readable form and it just seems to be there to cause trouble.  I just want to back up the database.  Can anyone tell me how to do that?


    LouatMonaco

    Tuesday, September 24, 2013 7:19 PM

Answers

  • This why we have Google

    you can not be a developer or dba if you dont know how to use goolge. the forums are great place to get spesific help but we cant post a book with 100 pages :-), Moreover English is not my native language and it takes me long time to write a long message. try to use Google inorder to get more information about USER INSTANCE

    https://www.google.co.il/#q=sql+express+user+instance

    You can start here: http://msdn.microsoft.com/en-us/library/ms254504.aspx

    Basically your data base is not attach to the main SQL Server instance probably, but to a USER INSTANCE. also known as DYNAMIC INSTANCE. (1) if this is not a live server then just stop the VS, and wait a while for the DB to be detach from the instance (this is the idea of dynamic instance) and then you can jusr copy the files as they are. (2) another sulotion is just to use VSS to copy the file without detach it from the server (look at Google what VSS mean). i use my own VSS application (took me almost 1 hour to develop it...). By the way In about two months I'm supposed to give a lecture on SQL VSS subject at the offices of Microsoft Israel. Feel free to visit Israel and come to lecture if you want :-) (3) you can sue the SSMS to connect the user instance (look google for this). (4) the best way in live application is to use Service Broker in order to backup SQL Server EXPRESS databases.

    Each solution will send you to learn something probably :-) 
    this is why we cant be professional without Google :-)

    This forum is for SQL EXPRESS specific and you can find there a lot of thread on user instance and specific EXPRESS issues: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlexpress

    I hope this is helpful as start point

    * i recommended first try option 1-4 by the order 1 to 4

    * i recommended not to use USER INSTANCE at all and to start use the EXPRESS as any Database attach to the main server instance, as in live this is a better way for many reasons


    signature

    Tuesday, September 24, 2013 9:18 PM
    Moderator

All replies

  • what version of SQL are you using?

    * i have a feeling that you are using SQL EXPRESS and the problem is connected to "USER INSTANCE"


    signature

    Tuesday, September 24, 2013 8:34 PM
    Moderator
  • I am using SQL Server Express.  What is "USER INSTANCE?"  Thanks.

    LouatMonaco

    Tuesday, September 24, 2013 8:43 PM
  • This why we have Google

    you can not be a developer or dba if you dont know how to use goolge. the forums are great place to get spesific help but we cant post a book with 100 pages :-), Moreover English is not my native language and it takes me long time to write a long message. try to use Google inorder to get more information about USER INSTANCE

    https://www.google.co.il/#q=sql+express+user+instance

    You can start here: http://msdn.microsoft.com/en-us/library/ms254504.aspx

    Basically your data base is not attach to the main SQL Server instance probably, but to a USER INSTANCE. also known as DYNAMIC INSTANCE. (1) if this is not a live server then just stop the VS, and wait a while for the DB to be detach from the instance (this is the idea of dynamic instance) and then you can jusr copy the files as they are. (2) another sulotion is just to use VSS to copy the file without detach it from the server (look at Google what VSS mean). i use my own VSS application (took me almost 1 hour to develop it...). By the way In about two months I'm supposed to give a lecture on SQL VSS subject at the offices of Microsoft Israel. Feel free to visit Israel and come to lecture if you want :-) (3) you can sue the SSMS to connect the user instance (look google for this). (4) the best way in live application is to use Service Broker in order to backup SQL Server EXPRESS databases.

    Each solution will send you to learn something probably :-) 
    this is why we cant be professional without Google :-)

    This forum is for SQL EXPRESS specific and you can find there a lot of thread on user instance and specific EXPRESS issues: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlexpress

    I hope this is helpful as start point

    * i recommended first try option 1-4 by the order 1 to 4

    * i recommended not to use USER INSTANCE at all and to start use the EXPRESS as any Database attach to the main server instance, as in live this is a better way for many reasons


    signature

    Tuesday, September 24, 2013 9:18 PM
    Moderator
  • You are right of course.  I should have Googled USER INSTANCE.  On the other hand, I think I got more clear information and a better understanding out of your brief explanation than I might have gotten out of Googling all day.

    I checked my web config file.  I am running the database as a user instance, by following the instructions in a tutorial.  Tutorials can't cover everything and this one didn't discuss that topic, so I will need to look into that more deeply.

    If I understand you correctly, to get a backup I can simply copy my database after exiting from Visual Studio and giving it time to detach.  I think that will be the simplest solution for me for right now.

    You have answered my question and on top of that given me a good start on understanding some of the confusing things about SQL Server.  I can't thank you enough.

    If I were in Israel I would definitely attend your presentation.  Sadly, I am in Kansas, but I wish you well.


    LouatMonaco

    Wednesday, September 25, 2013 6:42 PM
  • Hi

    Yes, you got me correctly :-)
    option 1 should work.

    I'm glad I could help, and good luck on keeping learning :-)


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, September 25, 2013 8:29 PM
    Moderator