SQL Server Backup Migration policy

Tags: SQL server/mssql server/Database/dba/xp_cmdshell/backup compression

Overview

When the backup space is not very abundant, you need to find a way to copy the backup files to a dedicated backup machine, especially the storage space is not superior to the enterprise, and then share two ways to migrate backup files.

Directory

• Overview
• Server configuration Options
  • Query Server Configuration
  • Open xp_cmdshell
  • Open allow updates
• xp_cmdshell Method
  • Perform a backup
• Dos Method
• Summary

Server configuration options
Query Server Configuration

You must first turn on the corresponding server configuration option before using the xp_cmdshell method, and query the system for each server-wide configuration option value using the following statement.

-- One row for each server-wide configuration option value in the system SELECT *  from sys.configurations ORDER  by DESC GO

Open xp_cmdshell

---Open xp_cmdshellsp_configure'Show advanced Options',1;--open sp_configure system stored procedure advanced option, default is 0GoReconfigure;---Specifies that if the configuration settings do not require the server to stop and restart, the currently running values are updated. RECONFIGURE also checks for invalid values in the new configuration value (if RECONFIGURE with override skips error checking)Gosp_configure'xp_cmdshell',0;GoReconfigure;Gosp_configure'Show advanced Options',0;Go

Open allow updates

In a server-wide configuration, if the vlues value of ' Allow updates ' is ' 1 ', the update to the system table is blocked, that is, the open xp_cmdshell is blocked, so allow updates must be enabled first

----turn on allow update of system tables feature (Prompt to change when system configuration option is not running on system catalog immediate update)sp_configure'Show advanced Options',1;GOsp_configure'Allow updates',0 ---turn on allow update of system tablesGosp_configure'Show advanced Options',0;Go----Turn Off Update system table functionsp_configure'Show advanced Options',1;GOsp_configure'Allow updates',1Gosp_configure'Show advanced Options',0;Go

xp_cmdshell method

Create a stored procedure that backs up the database, and then perform a backup by calling the stored procedure from the job.

 Use [Master] GOCREATE PROCEDURE [dbo].[Sp_bakupdatabase] (@dbname nvarchar( -)) as Declare @strCmdShell varchar( -) Declare @FullFileName Varchar( $) Declare @strPath NVARCHAR( $)Declare @ToFileName varchar( $) Declare @SQLStr varchar( -) Declare @SQLStr2 varchar( -) Declare @FlagDel INT Declare @backupSetId  as intSET @strPath = Convert(NVARCHAR( +),getdate(), -)SET @strPath = REPLACE(REPLACE(Convert(NVARCHAR( +),getdate(), -),':',"'),' ','_')SET @FullFileName='e:\'+@dbname+'\'+@dbname+'_backup_'+@strPath+'. bak' SET @ToFileName='\\192.168.1.1\E$\ Backup' SET @FlagDel=1SET @SQLStr='Copy'+@FullFileName+' '+@ToFileName SET @SQLStr2='del' +@FullFileName BackUp DataBase @dbname  to Disk= @FullFileName  withNoinit, Nounload, Noskip, Noformat, STATS= Ten, Checksum,continue_after_error---Verify that the backup set is validSelect @backupSetId =Position fromMsdb.. Backupsetwheredatabase_name=@dbname  andbackup_set_id=(Select Max(backup_set_id) fromMsdb.. Backupsetwheredatabase_name=@dbname )IF @backupSetId  is NULL begin RAISERROR(N'validation failed. The backup information for the database database could not be found. ', -,1)EndRESTOREVerifyonly from  DISK = @FullFileName  with  FILE = @backupSetId, Nounload, NorewindEXECMaster.dbo.xp_cmdshell'net use \\192.168.1.1\E$ 123456/user:192.168.1.1\user' EXECMaster.. xp_cmdshell@SQLStr IF(@FlagDel =1)BEGIN EXECMaster.. xp_cmdshell@SQLStr2END

---picture explanation

Perform a backup

 Use [master] EXEC ' adventureworks2008r2 '

Dos method

If the remote backup space is not sufficient, you need to compress the backed up database and then copy it, and then back up the database to local through a normal backup. Then use the RAR.EXE compression software to compress and copy to the remote computer, save the following batch to the bat suffix format, and then schedule the batch processing through the scheduled call of the task.

@echo offrem for comment Syntax timeout for window 2003 version only specified XP incompatible setlocal enabledelayedexpansionSet /A T=0*3600: Start for /F "delims="%%Ainch("dir/B'd:\database\person\person*'" ) Do (" C:\Program files\winrar\winrar.exe "a- as -R-EP1-Ibck-DF "\ \192.168.1.1\DataBase\person\person_.rar "-M3-Agyyyymmddhhmmss "D:\DataBase\person\p*"Rem del/Q/F "d:\DataBase\person\p*") rem timeout/T%T% /NobreakremGoto: Start

---picture explanation

Summary

If the backup space is sufficient and does not require such trouble, the second method is more stressful for the server and has a long migration time, which is not recommended.

If the article is helpful to everyone, I hope you can give a praise, thank you!!!

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server Backup Migration policy