locked
Any software to autobackup MsSQL server (As SQL Script) RRS feed

  • Question

  • User283528319 posted

    Hi all,

    Is there any preferably free software to backup MsSQL server to an SQL script and send it to you via email?

    thank you

    (BTW preferably not backup and restore and instead export as SQL script and import as SQL script)

    Saturday, October 12, 2019 7:39 AM

Answers

  • User475983607 posted

    could you give little detail about writing T-SQL to do the lob I want

    Seems odd that you can generate backup DDL using Delphi and MySQL but not Delphi to MS SQL.  The SQL scripts are very similar.  

    It might be difficult to find a custom "free" solution for MS SQL that will match your custom Delphi code.  Keep in mind that the community has no idea how your custom code works.

    But, SSMS has the ability to generate schema and data scripts.  By default SSMS exports schema only but you can change that in the advanced options. 

    Tasks -> Generate Scripts -> Next -> In the Set Scripting Options dialog -> Advanced.  Find "types of data to script" and change "schema only" to "schema and data".

    https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-ver15

    The script can be published to a web service.  From there you get to write code in the service method to attach and send an email.

    I believe you can do the same uses SSIS.  Basically create an SSIS package to generate the script.  IMHO, SQL has everything you need to backup and restore but if you want a custom solution then you'll need to do a little research and perhaps investigate MS SQL tool chain. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 13, 2019 1:01 PM

All replies

  • User-1451719302 posted

    Have you tried scheduled backup by using a SQL Server Agent job?

    Saturday, October 12, 2019 9:23 AM
  • User283528319 posted

    yeah but it is backup-restore solution

    I need a solution to get whole database as SQL script... Preferably zipped and sent by email

    ( I have done it /I wrote a program to do it for MySQL ) but I need it for MsSQL now

    Saturday, October 12, 2019 1:07 PM
  • User475983607 posted

    You're describing the MySql Admin backup feature.  MS SQL uses backup/restore which creates a single .bak file.   Is there some reason why you can't use the standard MS SQL backup and restore and need to create a custom solution?

    If you really want to mimic MySQL Admin then do a Google search. 

    Saturday, October 12, 2019 4:02 PM
  • User283528319 posted

    You're describing the MySql Admin backup feature.

    No I am not describing it.

    I used 3. parti component from devexpress and implemented it in my Delphi program to write that piece of software.

     Is there some reason why you can't use the standard MS SQL backup and restore and need to create a custom solution?

    Yeah there is. I don't trust something that I can not reach and change it via Notepad++, like .bak file.

    I want to see and if needed change the restoration file.

    thk.

    Saturday, October 12, 2019 4:42 PM
  • User475983607 posted

    I used 3. parti component from devexpress and implemented it in my Delphi program to write that piece of software.

    I'm a little confused, what is stopping you from modifying your code to generate T-SQL?

    Yeah there is. I don't trust something that I can not reach and change it via Notepad++, like .bak file.

    I want to see and if needed change the restoration file.

    You check the script for errors and make changes by hand?  How in the world do you find the errors or the time?   I've used SQL backup and restore for over 20 years and never had an issue with missing data or an incorrect schema.

    Saturday, October 12, 2019 10:53 PM
  • User283528319 posted

    mgebhard

    I'm a little confused, what is stopping you from modifying your code to generate T-SQL?

    thanks. could you give little detail about writing T-SQL to do the lob I want

    Sunday, October 13, 2019 5:26 AM
  • User475983607 posted

    could you give little detail about writing T-SQL to do the lob I want

    Seems odd that you can generate backup DDL using Delphi and MySQL but not Delphi to MS SQL.  The SQL scripts are very similar.  

    It might be difficult to find a custom "free" solution for MS SQL that will match your custom Delphi code.  Keep in mind that the community has no idea how your custom code works.

    But, SSMS has the ability to generate schema and data scripts.  By default SSMS exports schema only but you can change that in the advanced options. 

    Tasks -> Generate Scripts -> Next -> In the Set Scripting Options dialog -> Advanced.  Find "types of data to script" and change "schema only" to "schema and data".

    https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-ver15

    The script can be published to a web service.  From there you get to write code in the service method to attach and send an email.

    I believe you can do the same uses SSIS.  Basically create an SSIS package to generate the script.  IMHO, SQL has everything you need to backup and restore but if you want a custom solution then you'll need to do a little research and perhaps investigate MS SQL tool chain. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 13, 2019 1:01 PM
  • User283528319 posted

    fatihbarut

    could you give little detail about writing T-SQL to do the lob I want

    Seems odd that you can generate backup DDL using Delphi and MySQL but not Delphi to MS SQL.  The SQL scripts are very similar.  

    It might be difficult to find a custom "free" solution for MS SQL that will match your custom Delphi code.  Keep in mind that the community has no idea how your custom code works.

    But, SSMS has the ability to generate schema and data scripts.  By default SSMS exports schema only but you can change that in the advanced options. 

    Tasks -> Generate Scripts -> Next -> In the Set Scripting Options dialog -> Advanced.  Find "types of data to script" and change "schema only" to "schema and data".

    https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-ver15

    The script can be published to a web service.  From there you get to write code in the service method to attach and send an email.

    I believe you can do the same uses SSIS.  Basically create an SSIS package to generate the script.  IMHO, SQL has everything you need to backup and restore but if you want a custom solution then you'll need to do a little research and perhaps investigate MS SQL tool chain. 

    Do you know what? you are right about writing another piece of software.

    But do you know where is the problem? It is Unidac. It doesn't provide any option for SQL server (while it provides for MySQL)

    Therefore Unidac backup files can't be used for restoration (without lots of manual manipulations, disabling foreign keys etc.).

    yellcrycry

    Monday, October 14, 2019 2:41 PM
  • User475983607 posted

    Do you know what? you are right about writing another piece of software.

    But do you know where is the problem? It is Unidac. It doesn't provide any option for SQL server (while it provides for MySQL)

    Therefore Unidac backup files can't be used for restoration (without lots of manual manipulations, disabling foreign keys etc.).

    The docs indicate you can use the standard OLEDB to connect to SQl Server and compatible to SQL 2017

    https://www.devart.com/unidac/docs/using-sqlserver.htm

    https://www.devart.com/unidac/docs/database_connect.htm

    Monday, October 14, 2019 2:53 PM