Backup ALL your SQL Server 2005 databases using ONE script

I wanted to backup all my databases... I had loads, creating a step for each db was getting tedious, so I wrote this script.
Enjoy.

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1

        -- Not master, tempdb or model
    and db_name(s_mf.database_id) not in ('Master','tempdb','model')
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)   
    set @DBFileName = datename(dw, getdate()) + ' - ' +
                       replace(replace(@DBName,':','_'),'\','_')

exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:\db backup\' +
@DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Ini beberapa Modif dari Script Diatas :

• - I found several others, but I decided to combine, what I think, are quite a few good additions and changes. I created a stored procedure. You can of course strip that out for making it into a T-SQL Script job. Enjoy! 

/$$$$$$$$$$$$$$$$$$$$$$$$ COPY FROM HERE $$$$$$$$$$$$$$$$$$$$$/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Company: Manatix Technologies B.V.
-- Website: http://www.manatix.com
-- Create date: <14-09-2008>
-- Description:
-- =============================================
ALTER PROCEDURE BackupSQLDatabases
AS
BEGIN
/$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/
/* Declarations & Variables */
DECLARE @path VARCHAR (500) /* Path of the Backup Files */
DECLARE @folderdate VARCHAR (75) /* The subdir for my backups with Format YYYYMMDD */
DECLARE @cmd VARCHAR (4000) /* The command to create Subdir */
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE @PreciceDateTime varchar(255)
DECLARE @ServerName varchar(50)
DECLARE @NetworkBackupShare varchar(75)
/$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/
--Network or local disk path you wish to use, such as D:\Backup
SET @NetworkBackupShare = N'D:\Backup'
/$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/
--Create a dynamic path for the backup of the databases based on datetime
/$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$/
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
/* gives us YYYYMMDD
hour hh
minute mi, n
second ss, s
*/
SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) N'-' N'H' CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()), 112) N'M' CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112) N'S' + CONVERT(VARCHAR(20), DATEPART(second, GETDATE()), 112)
--PRINT @folderdate
/Get Server name/
SET @ServerName = (SELECT @@servername)
/* Path as C:\Backup\YYYYMMDD */
SET path = NetworkBackupShare N'\' ServerName N'\' folderdate + N'\'
/* Create the command that will do the creation of the folder*/
SET cmd = N'mkdir ' + path
--PRINT @cmd
/* Create the new directory */
EXEC master.dbo.xp_cmdshell @cmd , no_output
/* now I can direct all the backup file to the created subdirectory like,
SET filename = path [other_variable/s] ‘.BAK‘ */
/*******************************************/
--Now let's actually do the backups to the path created above
DECLARE DATABASES_CURSOR CURSOR FOR

    select

        DATABASE_NAME   = db_name(s_mf.database_id)

    from

        sys.master_files s_mf

    where

       -- ONLINE

        s_mf.state = 0 



       -- Only look at databases to which we have access

    and has_dbaccess(db_name(s_mf.database_id)) = 1 



        -- Not master, tempdb or model

    and db_name(s_mf.database_id) not in ('Master','tempdb','model')

    group by s_mf.database_id

    order by 1

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

    declare @DBFileName varchar(256)    

    set @DBFileName = --datename(dw, getdate()) + ' - ' + 

                       replace(replace(@DBName,':','_'),'\','_') + '.BAK'

        PRINT @path

    exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @path + 

        @DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' + 

        @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')



    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
/*******************************************/
EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE
END
/$$$$$$$$$$$$$$$$$$$$$$$$ COPY TO HERE $$$$$$$$$$$$$$$$$$$$$/

• Thanks for your script. here script is for toll 'litespeed', I modific your codex, thank a lot.  

DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FORWARD_ONLY FOR

    select DATABASE_NAME   = db_name(s_mf.database_id)

    from sys.master_files s_mf

    where

       -- ONLINE

        s_mf.state = 0 

       -- Only look at databases to which we have access

    and has_dbaccess(db_name(s_mf.database_id)) = 1 

        -- Not master, tempdb, model or msdb

    and db_name(s_mf.database_id) not in ('Master','tempdb','model','msdb')

    group by s_mf.database_id

    order by 1 

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

    /*declare @DBFileName varchar(256)    

    set @DBFileName = datename(dw, getdate()) + ' - ' + 

                       replace(replace(@DBName,':','_'),'\','_')*/

declare @rawScript nvarchar(4000)
set @rawScript = '
exec master.dbo.xp_backup_database
database = N[' DBName '],
@backupname = NDatabase Backup,
@desc = NFull Backup ,
@compressionlevel = 1,
filename = NC:\MSSQL\[' DBName ']{0}.bak,
@with = NSKIP,
@with = NSTATS = 10'
declare @appendDate nvarchar(1024)
set @appendDate = '_' + left(replace(replace(replace(convert(nvarchar, getdate(), 120), '-', ), ':', ), ' ', ''), 12)
declare @script nvarchar(4000)
set script = replace(rawScript, '{0}', @appendDate)
exec (@script)
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

 Atau, kalo mau Lewat command prompt, bisa kaya gini :

::BackUpAllMyDatabases.cmd
:: COMMAND LINE BATCH SCRIPT FOR TAKING BACKUP OF ALL DATABASES
::RUN THE SQL SCRIPT VIA THE COMMAND LINE WITH LOGGING
sqlcmd S localhost -e -i "BackUpAllMyDatabases.sql" o Result_Of_BackUpAllMyDatabases.log
::VIEW THE RESULTS
Result_Of_BackUpAllMyDatabases.log
::pause

Atau Cukup :

sqlcmd S [servername/instance] -i "c:\backupscript.sql"

Diambil dari : sini.