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

::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

I am newbe, so dumb. Do you a a script to do a single database. Don't ask but, I need to backup an old SQL 7 database that was restored onto SQL 2005 and the Management Studio Management Maintenance Plans do not like SQL 7 so will not automatically backup and add the date and time to the end of the backup file. So it keeps overwriting the same file. Problem if external backup misses for a night.

Thanks Sir, It's nice one For Backup.

  Thanks Again.

Hi, I am from Bolivia, 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

Hi ,

Thanks a lot .. Its Really helped me a lot .... Nice...

thanks dude, its so handy :)