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 FOR

    SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName

FETCH NEXT FROM CurDBName INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

    --Execute Backup

    SET @FileName = 'F:\DBBackup\' + @DBName + '_' + @CurrentTime

    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +

     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'

    EXEC(@SQL)

    --Get Next DataBase

    FETCH NEXT FROM CurDBName INTO @DBName

END

CLOSE CurDBName

DEALLOCATE CurDBName

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,'') + '

BACKUP DATABASE '+ QUOTENAME(name,'[]')

+ ' TO DISK = ''F:\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)

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

DELETE FROM [dbo].[sp_MSforeachdb_Filter]

create 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) = null

as

    set 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 int

    select @retval = @@error

    if (@retval = 0)

        exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1

    if (@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

	--调用上述过程

	USE [master]

GO

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,'') + '

BACKUP DATABASE [?]

TO DISK = ''F:\DBBackup\?_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak''

WITH NOINIT, NOUNLOAD, NAME = N''?_backup'', NOSKIP, STATS = 10, NOFORMAT'

PRINT @SQL

--过滤数据库

EXEC [sp_MSforeachdb_Filter] @command1=@SQL,

@whereand=" and [name] not in('tempdb','master','model','msdb') "

--查看源码

EXEC sp_helptext N'sp_MSforeachdb';