下面的存储过程适用：
1.一次想备份多个数据库。
2.只需要一步操作，在有存储过程的条件下。
3.可以根据自己的需要修改存储过程。
/*-----------------------------
Description:
1.Backup multiple Database
2.If you want to backup database except 'master\model\msdb\tempdb',you can input '%' of the second parameter
3.parameter 1 is the path where you want to back,parameter 2 is the database name you want to backup
3.This procedure backup database with fuzzy database name Author:jiangxiaoqiang
Date:2013-09-27
Modify history:
-----------------------------*/ ALTER procedure [dbo].[sp_BackupDBBatch]
@backup_path varchar(200),
@backup_name varchar(200)
as
begin
declare @DynamicSQL varchar(256)
set @DynamicSQL='if not exist '+@backup_path+'\%Date:~0,4%%Date:~5,2%%Date:~8,2% mkdir '+@backup_path+'\%Date:~0,4%%Date:~5,2%%Date:~8,2%'
exec xp_cmdshell @DynamicSQL declare @backup_path_detail varchar(200)
set @backup_path_detail=@backup_path+'\'+CONVERT(char(8),GETDATE(),112)
--declare variable
DECLARE @dbname nvarchar(200)
--SET @backup_path='D:\DatabaseBackup'--you can define by yourself
/*
declare cursor syntax
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
*/
DECLARE db_info CURSOR --declare cursor
LOCAL
STATIC
READ_ONLY
FORWARD_ONLY
FOR --add other condition by select
SELECT name
FROM master.sys.databases with(NOLOCK)
WHERE database_id>4 and name like '%'+@backup_name+'%'--maser,model,msdb,tempdb
--here you can add the condition like:name like'NJPEC%'
OPEN db_info --open cursor
FETCH NEXT FROM db_info INTO @dbname WHILE @@FETCH_STATUS=0
BEGIN
EXEC master.dbo.backupDB @dbname,@backup_path_detail
FETCH NEXT FROM db_info INTO @dbname
END
CLOSE db_info
DEALLOCATE db_info
---------------------------END--------------------------------
--Find&list all database
/*
select *
from master.sys.databases
order by database_id;
*/
end
ALTER PROC [dbo].[backupDB]
(
--We'd better add bracket even if no parameters
@DatabaseName nvarchar(200),
@Path nvarchar(200)
)
AS
BEGIN
DECLARE @FileName varchar(200),@sql varchar(1000)
--RIGHT function:return the @Path last X word
--CONVERT function:CONVERT(data_type(length),data_to_be_converted,style)
--112 representative yymmdd format
--108 representative hh:mm:ss format
SET @FileName=@Path+(CASE WHEN RIGHT(@Path,1)<>'\' THEN '\' ELSE '' END)
+@DatabaseName+'_'+CONVERT(char(8),GETDATE(),112)+'_'
+replace(convert(char(8),getdate(),108),':','')+'.bak'
--to explain the ''':the ' before to and the last ' was a couple.
--the backup folder must be exists,and N must be uppercase.
--what's the 'N' meaning?It means the varible using code with unicode.
SET @sql='backup database '+@DatabaseName+' to disk =N'''+@FileName+''''
EXEC(@sql)
END
数据非常重要，本脚本为学习交流用，正式数据库还请制定详细备份解决方案，欢迎大家提出改进意见。