Remote database backup

Declare @ strsql varchar (1000), -- execute the statement
@ Strdirname varchar (50), -- create a folder name
@ Strcmd varchar (50), -- execute command name
@ Strsend varchar (1000), -- mail sending statement
@ Strdate varchar (50) -- mail sending date
Set @ strsql = 'backup database pubs to disk = 'd: \ backup \ ERP \'
Set @ strdirname = Replace (substring (convert (varchar (20), getdate (), 120 ),'-','')
Set @ strcmd = 'md D: \ backup \ ERP \'
Set @ strcmd = @ strcmd + @ strdirname
-- Obtain the date of the current day in the format of yyyy-mm-dd
Set @ strdate = substring (convert (varchar (50), getdate (), 120)
Set @ strsend = 'sys _ Sendmail ''1000 @ hm.com'', '2017 @ hm.com '', ''www'', ''backup notification '', ''' + @ strdate + 'the database is backed up successfully. Thank you for your use! '''
Exec xp_cmdshell @ strcmd
Set @ strsql = @ strsql + @ strdirname + '\ pubs. dat ''with init, nounload, noskip, noformat'
Print @ strsql
Exec (@ strsql)
Backup database pubs to disk = 'd: \ backup \ ERP \ pubs. dat 'with init, nounload, noskip, noformat
-- Upload data to the ERP server over FTP
Exec xp_cmdshell 'ftp-S: "D: \ backup \ ERP \ ftp.txt "'
-- Send an email after the operation is successful
Exec (@ strsend)
If @ error <> 0
Begin
Raiserror ('database backup error, please check settings)
Set @ strsend = 'sys _ Sendmail ''1000 @ hm.com'', '2017 @ hm.com '', ''www'', ''backup notification '', ''' + @ strdate + 'the database backup failed. Check the settings! '''
Exec (@ strsend)
End

Bytes -----------------------------------------------------------------------------------------------------------

The following example uses C # for backup.

/// < Summary >
/// Back up a database to a local disk
/// </ Summary >
Public Bool Backup (String backupfile)
{
Try
{
// Step 1: create a temporary folder on the server
Executesql (@ "master .. xp_cmdshell ' MD c: \ Temp ' ");
Executesql (@ "master .. xp_cmdshell ' Del c: \ temp \ *. */Q ' ");
// Step 2: Back up the database to the server directory
Executesql (@" Backup   Database " + Databasename () + @" To   Disk = ' C: \ temp \ hssy ' ");
// Step 3: Backup Directory of the Shared Server
Executesql (@ "master .. xp_cmdshell ' NET Share sqldataback = c: \ Temp ' ");
// Step 4: copy the backup file on the server to the local device
File . Copy (@"\\" + Serverip () + @ "\ Sqldataback \ hssy", backupfile, true );

Return True;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
// Step 5: Cancel sharing the server shared directory
Executesql (@ "master .. xp_cmdshell ' NET Share sqldataback/delete ' ");
}
}

///   < Summary >
/// Recover a database from a local disk
///   </ Summary >
Public Bool Restore (String restorefile)
{
Try
{
// Step 1: Shut down the user process to prevent other users from using the database, resulting in data recovery failure
Killserveruser ();
// Step 1: create a temporary folder on the server
Executesql (@ "master .. xp_cmdshell ' MD c: \ Temp ' ");
Executesql (@ "master .. xp_cmdshell ' Del c: \ temp \ *. */Q ' ");
// Step 2: Restore directory of the Shared Server
Executesql (@ "master .. xp_cmdshell ' NET Share sqlrestore = c: \ Temp ' ");
// Step 3: copy the backup file on the server to the local device
File . Copy (restorefile ,@"\\" + Serverip () + @ "\ Sqlrestore \ hssy", true );
// Step 4: Cancel sharing the server shared directory
Executesql (@ "master .. xp_cmdshell ' NET Share sqldataback/delete ' ");
// Step 5: Restore the database to the server directory
Executesql (@" Restore   Database " + Databasename () + @" From   Disk = ' C: \ temp \ hssy ' ");
Return True;
}
Catch (system. Data. sqlclient. sqlexception E)
{
Throw new exception (E. Message );
}
Finally
{
// Step 6: Cancel sharing the server shared directory
// Dbhelpersql. executesql (@ "master .. xp_mongoshell ' NET Share sqldataback/delete ' ");
}
}