SQL Remote Backup

Original: SQL remote backup

Set ANSI_NULLS ONset quoted_identifier ongo --=============================================--author:dcrenl-- Create date:2013-9-5 9:54:46--description:web Server Remote Backup Database,--This method requires the sharing feature of the Web server to be turned on.--authentication method in the same LAN other machine in the operation input \\IP\C$ Requires a user name and password, if you can access this method effectively----offsite backup There are many ways to--1. After you have backed up the database, create a library on the database service, add a table, create an Image Type field,--and then use Sp_textcopy to write the backup file to the database. The backup file is read from the Web server, but the backup file read out by this method may corrupt--2. Installing SQL Server on a Web server Use SQLDMO (Interop.SQLDMO.dll) or--microsoft.sqlserver.connectioninfo.dll, microsoft.sqlserver.management.sdk.sfc.dll--(above two DLL file locations: Program Files\Microsoft Sqlserver\100\sdk\ Assemblies)--generate scripts such as database tables, stored procedures, and functions, then create a database on the SQL Server on the Web service, create a table with the generated script, and then insert the data into the database on the Web server for backup--========== ===================================create PROCEDURE [dbo]. [Remoteback] @DataDisk nvarchar (max),--The drive letter that needs to be mapped on the database server @webaddr nvarchar (max),--Web server address @webdisk nvarchar (max),-- Web server shared directory (for example: d$ or c$\windows) @Password nvarchar (max),--Web server password @username nvarchar (max),--Web server user name @dataname nvarchar (max)-The name of the database that needs to be backed up Asbeginset NocounT on;--Set Date time string DECLARE   @DateTime datetimeset @DateTime = getdate () DECLARE @FileTime nvarchar (max) set @FileTime = Con Vert (nvarchar (), year (@DateTime)) +right (CAST (month (@DateTime) +100 as varchar), 2) +right (CAST (Day (@DateTime) +100 As varchar), 2) +right (CAST (Datename (Hour,getdate ()) +100 as varchar), 2) +right (CAST (Datename (Minute,getdate ()) +100 as varchar), 2) +right (CAST (Datename (Second,getdate ()) +100 as varchar), 2)  --Open Advanced settings exec sp_configure ' show advance Options ', 1reconfigure--open xp_cmdshellexec sp_configure ' xp_cmdshell ', 1reconfigure--maps the Web server path to the database server exec (' Master .. xp_cmdshell ' net use ' + @DataDisk + ': \ \ ' + @WEBAddr + ' \ ' + @WEBDisk + ' "' + @Password + '"/user: ' + @WEBAddr + ' \ ' + @UserName + ")--start Backup and copy the path to the Web server mapped to the SQL service exec (' backup database ' + @DataName + ' todisk= ' [email protected]+ ') : \ ' + @DataName + ' _ ' + @FileTime + '. Bak ' withinit ')  --close the mapping path to exec (' master. xp_cmdshell ' net use ' + @DataDisk + ':/delete ')--close xp_cmdshellexec sp_configure ' Xp_cmdshelL ', 0reconfigure--off advanced settings exec sp_configure ' show advance options ', 0reconfigureEND

SQL Remote Backup