none
Automatically created database back-up at publishing time RRS feed

  • Question

  • Each time I publish my application, LS (I presume) creates a back-up of the production database and puts it in C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup.
    How can I turn off this behaviour?  
    I do not need the feature since I have a daily back-up scheme for production data in place. Since the database - that is on another physical drive - is rather large it slows down the elapsed publishing time.
    If the feature cannot be turned off, how can I specify another location for the back-up since I want to keep my system drive (C:) as free as possible from user data.

    Thanks in advance.

    Alan


    VFP9Belgium
    Monday, October 31, 2011 10:47 AM

Answers

  • I don't know if it was only introduced in R2 maybe, but you can right-click the database, select "Facets".

    That was quite an old post you referred to.


    Yann - LightSwitch Central

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Tuesday, November 1, 2011 10:00 AM
    Moderator

All replies

  • This is LightSwitch simply erring on the side of "caution", rather than relying on people to do a backup before publishing.

    As far as I know, this is dependent on the backup location of the SQL Server, not a choice that LS makes, at least not as far as *I* know.

    I think you'll need to use SSMS to change the default backup location for the SQL Express instance.


    Yann - LightSwitch Central

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Monday, October 31, 2011 11:45 AM
    Moderator
  • This is LightSwitch simply erring on the side of "caution", rather than relying on people to do a backup before publishing.

    As far as I know, this is dependent on the backup location of the SQL Server, not a choice that LS makes, at least not as far as *I* know.

    I think you'll need to use SSMS to change the default backup location for the SQL Express instance.


    Yann - LightSwitch Central

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.


    That is correct it will be using the default locations, this is the biggest pain in my day to day life, when you deploy a SQL Server solution you should install with the data files, and backups into a different directory..... but then change those defaults in the settings so that applications use the new settings.
    http://www.virtualrealm.com.au - SQL Server DBA, MVP and Lightswitch Enthusiast.
    Tuesday, November 1, 2011 12:28 AM
  • Thanks Yann

    Unless I am missing something, SSMS does not allow to change the backup file location. Via 'Server Properties' / 'Database settings' it allows to change default locations for Data and Log.

    I did some further digging and came across the following:

    How to change the default backup location?

    "You can change the backup location here - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer 
    The key name is BackupDirectory - you can change it to any other folder."

    I applied this and it works. The backup data are now off C: at least.

    It does not solve the elapsed publishing time, but it is an acceptable workaround / compromise for me.

    Regards

    Alan

    (this may be a possible dupe reply since something interfered while I wrote my reply and I started over again)


    VFP9Belgium
    • Edited by Yann DuranModerator Thursday, April 25, 2013 4:23 PM Made link clickable, & added link text
    Tuesday, November 1, 2011 9:18 AM
  • Thanks for replying Glenn.

    I am only two months into LS and SQL Server, so forgive me if I am asking for the obvious.

    Context:

    • LS + SQL Server is installed (default options) on C: from installation media
    • The LS project is in D:\Proj1
    • The LS application (including its database) is deployed to D:\App1 and it works fine
    • D:\Proj1 and D:\App1 are part of my daily back-up scheme (disk-to-disk to drive E:)

    My assumption is that by having the LS + SQL Server installation media and the E: backup I am able to recreate my system (Proj1 and App1) from scratch. After reading the replies to my initial question I am no longer sure of that; it appears that there is other 'Proj1 data' put on C:.

    Question: does that other Proj1 data need to be backed up to recreate the system and if so, where is it located?

    Thanks

    Regards

    Alan


    VFP9Belgium
    Tuesday, November 1, 2011 9:46 AM
  • I don't know if it was only introduced in R2 maybe, but you can right-click the database, select "Facets".

    That was quite an old post you referred to.


    Yann - LightSwitch Central

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Tuesday, November 1, 2011 10:00 AM
    Moderator
  • Yann

    Thanks. Very helpful 

    Regards

    Alan


    VFP9Belgium
    Tuesday, November 1, 2011 10:26 AM
  • You're welcome!

    Yann - LightSwitch Central

    (plus ça change, plus c'est la même chose!)

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    This will help people find the answers that they're looking for more quickly.

    Tuesday, November 1, 2011 12:17 PM
    Moderator
  • Hey guys,

    I use an external database(s) for my apps and a commercial ASP hosting site for production and user testing.

    FYI: Authentication has already been created.

    1. I am sometimes seeing that LS will try to backup during a VS Publish and that other times it will not backup.  I would like to understand what the criteria is for the LS Publish to generate backups.  I wonder if the backup only occurs if there has been a change in the database since the last Publish.
    2. The database that is attempted to backup seems to be specified in the web.config rather than the Publish connections strings.  I debug with a test database and then publish to a live database.
    3. The Publish will fail if a remote database is specified when the Publish does try to backup the database (which of course fails because remote backup is not supported by SQL).

    I would appreciate confirmation, correction, and/or additional information on this.

    I will let you know if I discover more.

     

    Tuesday, November 1, 2011 10:40 PM
  • You might want to vote for an existing idea at Visual Studio UserVoice site.

    Deployment option to enable/disable automated database backup

    Bodo


    • Edited by bvuHH Thursday, April 25, 2013 12:57 PM
    Thursday, April 25, 2013 12:55 PM
  • +1 from me.

    More hookpoints on db deployment are desirable !


    paul van bladel

    Thursday, April 25, 2013 2:45 PM
  • I also +1'd.

    While the current behaviour exists to assist "non-developer" users by automatically backing the database up for them, advanced users have situations (as described) that would benefit greatly from having the option to turn it off.

    I have no problem with the default being "on", there just needs to be an option to turn it "off".

    I don't think that specifying a location for the backups is feasible, unless LightSwitch takes full  responsibility for creating the backup itself, rather than using the SQL Server's backup functionality. The last thing a DBA needs is for any application to be changing their SQL Server's settings.


    Yann Duran
         - Co-Author of Pro Visual Studio LightSwitch 2011
         - Author of the  LightSwitch Central Blog

    FREE Download: Luminous Tools for LightSwitch
    (a Visual Studio productivity extension for LightSwitch)
     
    Click Mark as Answer, if someone's reply answers your question
    Click  Vote as Helpful, if someone's reply is helpful
     
    By doing this you'll help everyone find answers faster.

    Thursday, April 25, 2013 4:34 PM
    Moderator
  • I'm not sure if this solution has been mentioned before, but I needed a workaround to this as it was filling up my server with backups to the point the drive was full!

    Anyway, the way I now deploy is to select "Create a script to install and configure the database" and then deploy as normal.  You'll get a .sql script in the output directory.
    Edit this and look for the BACKUP line and comment it out:
    --BACKUP DATABASE [$(DatabaseName)] TO DISK = @fn

    Then, go into SSMS and start a new query.  Go to the Query>SQLCMD Mode to turn command mode on, or it won't work.  Then paste the .sql script in and run it.

    Now, the only problem with this is if the script fails, your database will be stuck in single-user mode... if this happens, in the same query window type this command in:
    EXEC sp_dboption 'yourdatabasename', 'single user', 'FALSE'
    GO

    You can then work out why the query failed and fix it before re-running the script.
    I find that the script will often fail if you've set your new version of the database to delete a column in an existing table (and the table already has records in it).  To avoid this, I usually delete the column manually in SSMS before deploying the application.
    Another thing that can affect it, if you change a relationship or a constraint and your existing database values would make the new relationships invalid.  Again, a bit of detective work and manual intervention can fix this before re-running the script.


    This is far from ideal, so I wish the LS team would make it an option... duly voted for!


    Free Visual Studio LightSwitch extensions: Elyl's Extensions

    Friday, April 26, 2013 8:07 AM