Periodically back up the SQL Server database and copy the backup files to another server

Requirements: Daily backup of the online official library and copy the backup files to the test server, the test server automatically restores the database backup files.

Program Description:

1th step: Create a stored procedure on the official library to back up the database and replicate to the test server, and then create the stored procedure on a daily schedule for the new job.

2nd step: Create a stored procedure on the test server database that is used to restore the database, and the new job executes the stored procedure that is created on a daily schedule.

Get ready:

Share the Backup Files folder on the official server, and give permissions to the folder everyone.

Create a shared folder on the test server, and give permissions to the folder everyone.

The official library backs up the database and copies the database code:

-- Open xp_cmdshell ' Show advanced Options ',1reconfigurego'xp_cmdshell', 1 Reconfigure Go

 Use [Master]GO/** * * * object:storedprocedure [dbo].    [Backup_db_ksedu] Script date:11/22/2016 08:41:23 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO--backing up the databaseCreate proc [dbo].[backup_db_ksedu] as  Declare @strPsw varchar( -)Declare @strUsr varchar( -) Declare @strCmdShell varchar( -)Declare @strDataBaseName varchar( -)Declare @FullFileName Varchar( $)Declare @FullFileName1 Varchar( $)Declare @FullFileName2 Varchar( $)Declare @FileFlag varchar( -) Declare @FileFlag2 varchar( -) DECLARE @FileFlag3 varchar( -)Declare @ToFileName varchar( $)  Declare @SQLStr varchar( -)Declare @SQLStr2 varchar( -)   Declare @SQLStr3 varchar( -)    Declare @FlagDel varchar( -)--define the database name of the backupSet @strDataBaseName='Database name'--define the name of the local backup fileSet @FileFlag=@strDataBaseName + '_db_' + Replace(Convert(Char( -),getdate(), the),' ',"')--define the backup file name 1 days before the local--Set @[email protected] + ' _db_ ' + replace (convert (char), GETDATE () -1,112), ', ')--define backup file names for remote servers 3 days ago--Set @[email protected] + ' _db_ ' + replace (convert (char), GETDATE () -3,112), ', ')--set the logon domain and user name for the remote serverSet @strUsr='Zongheguanli-sq\administrator'--set the remote server login passwordSet @strPsw='abc123'--set up a remote server connectionSet @strCmdShell= 'net use \\10.150.3.204\db_backup' + @strPsw + '/user:' +@strUsr--set Local backup file nameSet @FullFileName='e:\bak\'+@FileFlag+'. bak'--Set the backup file name for local 1 days ago--Set @FullFileName1 = ' D:\Backup\ ' [email protected]+ '. Bak '--Set the backup file name for the remote server 3 days ago--Set @FullFileName2 = ' \\172.17.1.10\j$\db_backup\ ' [email protected]+ '. BAK '--set up remote server to save backup files directorySet @ToFileName='\\10.150.3.204\db_backup\'--Set @ToFileName = ' E:\Backup\ '--When set to True, when the backup is deleted, the backup file is not deleted when set to False--Set @FlagDel = ' False '--set up statements to copy backup files locally from the remote serverSet @SQLStr='Copy'+@FullFileName+' '+@ToFileName--set up Delete local backup files 1 days ago--Set @SQLStr2 = ' del ' [email protected]--set up a backup file to delete a remote server 3 days ago--Set @SQLStr3 = ' del ' [email protected]--Backup Database Compression backupBACKUP DATABASE @strDataBaseName   to DISK = @FullFileName   withINIT, STATS=  -  --connecting to a remote serverexecMaster.. xp_cmdshell@strCmdShell--Copy the backup file to the remote serverexecMaster.. xp_cmdshell@SQLStr   --Delete a local backup file 1 days ago--if (@FlagDel = ' True ')--exec master: xp_cmdshell @SQLStr2----Delete 3 days ago Remote server backup file--if (@FlagDel = ' True ')--exec master: xp_cmdshell @SQLStr3

Test Library Restore Database code:

 Use [Master]GO/** * * * object:storedprocedure [dbo].    [Restore_db_ksedu] Script date:11/22/2016 08:40:12 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOALTER PROCEDURE [dbo].[restore_db_ksedu] asDeclare @strDataBaseName varchar( -)Declare @FileFlag varchar( -) --define the database name of the backupSet @strDataBaseName='Database name'Set @FileFlag='D:\db_backup\'+@strDataBaseName + '_db_' + Replace(Convert(Char( -),getdate(), the),' ',"')+'. bak'--Add this sentence to prevent the database from being used resulting in unsuccessful executionALTER DATABASE [Database name] SETOFFLINE with ROLLBACKIMMEDIATERestore Database [Database name] from Disk=@FileFlag   with file=1,REPLACE, RecoveryALTER  Database  [Database name]  SetOnline

Create job schedule: Reference Http://wenku.baidu.com/link?url=HLojwfVr1gkEuKjhk3Twsy7SxTWNMxovLg4LXpqvg_ A3r50xqe5hzt8e03uphbw8qunjex0ika39djyj0spunn1tv42uciamnduqllhjszu

Reference article: http://www.cnblogs.com/wjj327342306/p/4568177.html

Periodically back up the SQL Server database and copy the backup files to another server