SQL Server batch full backup

I. the content involved in this article (Contents) the content involved in this article (Contents) background (Contexts) implementation code (SQLCodes) Implementation Method 1 (One) implementation method 2 (Two) implementation Method 3 (Three) Parameters

I. the content involved in this article (Contents) the content involved in this article (Contents) background (Contexts) implementation code (SQLCodes) Implementation Method 1 (One) implementation method 2 (Two) implementation Method 3 (Three) Parameters

1. Contents)

References)

Ii. Contexts III. Implementation Code (SQL Codes)

The following are three methods for batch backup of databases. You can understand the differences:

1) Implementation Method 1: Use a cursor

2) Implementation Method 2: Use the join SQL Method

3) Implementation Method 3: Use the Stored Procedure sp_MSforeachdb_Filter (based on sp_MSforeachdb)

(1) Implementation Method 1: Use a cursor

Execute the following SQL script to back up all the databases of the current database instance (except the system database );

-- ===================================================== ====== -- Author: <听风吹雨> -- Blog: <> -- Create date: <2011/12/03> -- Description: <批量备份数据库> -- ===================================================== ====== DECLARE @ FileName VARCHAR (200 ), @ CurrentTime VARCHAR (50), @ DBName VARCHAR (100), @ SQL VARCHAR (1000) SET @ CurrentTime = CONVERT (CHAR (8), GETDATE (), 112) + CAST (DATEPART (hh, GETDATE () as varchar) + CAST (DATEPART (mi, GETDATE () as varchar) DECLARE CurDBName cursor forselect name from Master .. sysDatabases where dbid> 4 OPEN CurDBNameFETCH next from CurDBName INTO @ DBNameWHILE @ FETCH_STATUS = 0BEGIN -- Execute BackupSET @ FileName = 'e: \ DBBackup \ '+ @ DBName +' _ '+ @ CurrentTimeSET @ SQL = 'backup DATABASE [' + @ DBName + '] TO DISK = ''' + @ FileName + '. bak '+ ''' with noinit, NOUNLOAD, NAME = n''' + @ DBName +' _ backup '', NOSKIP, STATS = 10, noformat' EXEC (@ SQL) -- Get Next DataBaseFETCH next from CurDBName INTO @ DBNameENDCLOSE CurDBNameDEALLOCATE CurDBName

After the preceding SQL script is executed, a similar backup file is generated in the E: \ DBBackup directory:

(Figure1: database backup file)

(2) Implementation Method 2: Use the join SQL Method

-- DECLARE @ SQL varchar (MAX) SELECT @ SQL = COALESCE (@ SQL, '') + 'backup database' + QUOTENAME (name, '[]') + 'to DISK = ''' E: \ DBBackup \ '+ name +' _ '+ CONVERT (CHAR (8), GETDATE (), 112) + CAST (DATEPART (hh, GETDATE () as varchar) + CAST (DATEPART (mi, GETDATE () as varchar) + '. bak '+ ''' with noinit, NOUNLOAD, NAME = n''' + name +' _ backup '', NOSKIP, STATS = 10, noformat' FROM sys. databases WHERE database_id> 4 AND name like '%' AND state = 0 PRINT (@ SQL) EXECUTE (@ SQL)

The generated script is shown in Figure2. To make the script more beautiful, you can add the GO statement, as shown in Figure3:

(Figure2: generated T-SQL script)

(Figure3: generated T-SQL script)

(3) Implementation Method 3: Use the Stored Procedure sp_MSforeachdb_Filter (based on sp_MSforeachdb)

By viewing the T-SQL source code of sp_MSforeachdb, we can find that the @ whereand parameter is not provided to filter the database. After referring to the sp_MSforeachtable system stored procedure ,, create the storage process sp_MSforeachdb with the @ whereand parameter on the basis of sp_MSforeachdb, so that you can run the SQL statement on the specified database;

-- ===================================================== ====== -- Author: <听风吹雨> -- Blog: <> -- Create date: <2013.05.06> -- Description: <扩展sp_msforeachdb，增加@whereand参数> -- ===================================================== ====== USE [master] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOcreate proc [dbo]. [sp_MSforeachdb_Filter] @ command1 nvarchar (2000), @ replacechar nchar (1) = n '? ', @ Command2 nvarchar (2000) = null, @ command3 nvarchar (2000) = null, @ whereand nvarchar (2000) = null, @ precommand nvarchar (2000) = null, @ postcommand nvarchar (2000) = nullasset deadlock_priority low/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set * // * @ precommand and @ postcommand may be used to force a single result set via a temp table. * // * Preprocessor won't replace within quotes so have to use str (). */declare @ inaccessible nvarchar (12), @ invalidlogin nvarchar (12), @ dbinaccessible nvarchar (12) select @ inaccessible = ltrim (str (convert (int, 0x03e0 ), 11) select @ invalidlogin = ltrim (str (convert (int, 0x40000000), 11) select @ dbinaccessible = n'0x80000000'/* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert () */if (@ precommand is not null) exec (@ precommand) declare @ origdb nvarchar (128) select @ origdb = db_name () /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. * // * Create the select */exec (n' declare hCForEachDatabase cursor global for select name from master. dbo. sysdatabases d' + N' where (d. status & '+ @ inaccessible + N' = 0)' + N' and (DATABASEPROPERTY (d. name, ''issingleuser'') = 0 and (has_dbaccess (d. name) = 1) '+ @ whereand) declare @ retval intselect @ retval =@@ errorif (@ retval = 0) exec @ retval = sys. sp_MSforeach_worker @ command1, @ replacechar, @ command2, @ command3, 1if (@ retval = 0 and @ postcommand is not null) exec (@ postcommand) declare @ tempdb nvarchar (258) SELECT @ tempdb = REPLACE (@ origdb, n'] ', n']') exec (n'use' + N' ['+ @ tempdb + N']') return @ retval

The difference between the storage process sp_MSforeachdb_Filter and sp_MSforeachdb is as follows:

(Figure4: add content 1)

(Figure5: add content 2)