super handi. thanx

Thanks , using it with our internal dbs

Nice one!

Thanks for the script.

You may want also to take a look at 3rd party solution for sql databases backup that I personally use in our company. It's LiteSpeed.

It's not only much easier than different handcoded scripts but can really speed up all backup and restore processes.

For example, after implementing litespeed in our environment database restoring time became several time less than before that really reduced so expensive server downtime.

http://www.scriptlogic.com/products/litespeed/

Can you tell me under what terms I can use and copy this code? i.e. what license you grant.

Thanks

Thanks Man,

You save my time 2 Hours perday. Offffffffff , Thanks Thanks

Hello ALL:

Thank you so much for this script. Pure genius! 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!

Anthony Urwin

Manatix Technologies B.V.

http://www.manatix.com

/$$$$$$$$$$$$$$$$$$$$$$$$ COPY FROM HERE $$$$$$$$$$$$$$$$$$$$$/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Anthony N. Urwin>

-- Company: Manatix Technologies B.V.

-- Website: http://www.manatix.com

-- Create date: <14-09-2008>

-- Description: <This is a simple stored procedure to backup all databases on a SQL server instance>

-- =============================================

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 $$$$$$$$$$$$$$$$$$$$$/