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:
Lets say we have WebServer (running Umbraco) and SQLServer (remote SQL server). You propose the following;
Run SQLBackUp on WebServer
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?
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).
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.
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.
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
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
OK so to clarify:
Lets say we have WebServer (running Umbraco) and SQLServer (remote SQL server). You propose the following;
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?
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:
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!
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.
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.
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.
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.
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
Thanks for update Adam. I'll definitely share my findings with remote db backup integration.
is working on a reply...