SQL Remote Backup

Source: Internet
Author: User
Tags filetime webdisk

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.