Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • keilo 568 posts 1023 karma points
    Nov 02, 2015 @ 21:37
    keilo
    1

    Support for Remote DB Backup

    With version 1.3, backing up a db on the same server as umbraco server works just fine.

    However, if the DB is on another system it attempts to save on path as per local system.

    What I would like to propose is to be able to set the sql backup store path (so one can adjust it according to the DB server path), and retrieve path.

    DB Server backup path: d:\backup\ (relative to the db server)

    where i can map this as shared folder or other means, to retrieve it back on the web(umbraco) server.

    The logic to retrieve the db backup will not be straightforward as it will depend to each implementation, but at the least the db-snapshot can take place on the remote db path.

    After the developer can customize the event on how to retrieve it (if required).

    I tried to look up the source by opening the 1.3 dll in reflector but there seems to be a SystemFile class which Im not very clear about (cant compile to do tests on proof-of-concept experiementation). Would be great if the source is shared so others can contribute.

    In my test case the Umbraco was running on system A

    DB was on system B. I created the same folder on the db server to be able to execute backup, and tried to map that as shared folder, and copy/stream within the system A. Not implying its a solution, it was just an experimentation on how one get this working with remote db backups.

    [UPDATE] here is the exception thrown if user attempts to backup db where it resides on other machine, sqlserver tries to write it on the remote machine relative folder and cant find it

    2015-11-03 00:26:15,263 [83] ERROR AllianceMedia.SQLBackUp.UserControls.SQLBackUp - [Thread 9] Error thrown by SQLBackUp package
    System.Data.SqlClient.SqlException (0x80131904): Cannot open backup device 'D:\WWWroot\DEV\App_Data\SQLBackUp\DEV_20151103_002615.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15100).
    BACKUP DATABASE is terminating abnormally.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at AllianceMedia.SQLBackUp.UserControls.SQLBackUp.btnRun_Click(Object sender, EventArgs e)
    ClientConnectionId:1c9ac32e-904c-49d5-b605-4a51f013a45b
    Error Number:3201,State:1,Class:16
    

    I believe there is a post in another thread, where user asking if it supports remote db, linking here for reference:

    https://our.umbraco.org/projects/developer-tools/sql-backup/bug-reports-and-issues/58153-Web-server-SQL-server-scenarios

  • Adam Maidment 54 posts 163 karma points
    Nov 04, 2015 @ 07:48
    Adam Maidment
    1

    OK so to clarify:

    Lets say we have WebServer (running Umbraco) and SQLServer (remote SQL server). You propose the following;

    1. Run SQLBackUp on WebServer
    2. SQLBackUp detects SQLServer via ConnString config and saves backup file to folder on SQLServer.

    I think the issue here would be for SQLBackUp to understand the file structure on SQLServer and acquire appropriate permissions to create a folder, in an approriate location, in which to save the .bak file.

    Would it not be feasible if the SQLServer backup was saved on WebServer (in the app_data folder) as it is presently?

  • keilo 568 posts 1023 karma points
    Nov 04, 2015 @ 08:38
    keilo
    0

    Hi Adam

    Im no expert but I believe when the backup sql exececuted from App Server to the DB server (thats on another host), the MSSQL tries to save the file on the remote server file structure.

    Thats what I understand from my limited testing, and observing the exception:

    [Thread 9] Error thrown by SQLBackUp package
    System.Data.SqlClient.SqlException (0x80131904): Cannot open backup device 'D:\WWWroot\DEV\App_Data\SQLBackUp\DEV_20151103_002615.bak'. 
    

    Hence I was suggesting the backup store folder can be a configurable string, so user can enter the remote SQL Server's relative path to store the database. For retrieval of that file, another path or link can be entered. Obviously it'll be upto developer to link the remote path to the retrieval path (by folder share/mapping or secure-link retrieval).

    cheers!

  • Adam Maidment 54 posts 163 karma points
    Nov 04, 2015 @ 07:50
    Adam Maidment
    1

    FYI the SystemFile class is just handling file information so that is can be displayed via the DataView. Will get the source up in due course.

  • keilo 568 posts 1023 karma points
    Nov 04, 2015 @ 08:40
    keilo
    0

    Thats great! Looking forward Adam.

    I wanted to try quick change of db store folder when i hit the remote db backup exception, using reflector i couldnt compile it back due to errors thrown in that class.

  • keilo 568 posts 1023 karma points
    Nov 08, 2015 @ 09:38
    keilo
    0

    I have done three tests on different environments, where the sql db is not on the same server as umbraco and the sql backup command fires it on remote system on all. Would it be possible to set the store-backup folder in a config file in the next version?

    The retrieval (from UI) of the remote file would be a challenge, for simplicity I would propose to set the retrieval folder in a config file too. Then it would be up to the developer to link the retrieval folder.

    Perhaps in the future version, the retrieval function can be set as url call where one can customize and add their specific code to retrieve the file/url.

  • keilo 568 posts 1023 karma points
    Nov 25, 2015 @ 17:33
    keilo
    0

    Hi Adam

    Any news on when the source will be available? I would like to get the remote db backup working, by changing the store routine; even though its hacky.

    Keep us posted.

  • Adam Maidment 54 posts 163 karma points
    Nov 26, 2015 @ 08:26
    Adam Maidment
    0

    Hi Keilo,

    Sorry. I've been tied up with higher priority (read paid) projects. I was hoping to get it all into Git and NuGet but time is slipping away from me.

    I'll put up a source dump over the weekend for you.

    I'll be interested to get your feedback on your hack as it might be something we could integrate as part of the next version.

    Cheers

  • keilo 568 posts 1023 karma points
    Nov 26, 2015 @ 17:51
    keilo
    0

    Thanks for update Adam. I'll definitely share my findings with remote db backup integration.

Please Sign in or register to post replies

Write your reply to:

Draft