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

No license, you can freely use the code

Thanks Man,

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

You're welcome Amardeep! Saves me a bunch of time too

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

Hi ,

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

thanks dude, its so handy

Great script!

Question - the script works very well manually. I tried to automate with with sqlcmd to no avail. Has anyone automated this script? I know you can automate and schedule the the management studio but you have to set a schedule for each individual database and I have quite a few. It would be nice to get it all done in one shot like this script does when you run it manually.

-CoderXMan

Hi CoderXMan,

I run this from SQL Agent and it backs up all databases. Are you saying that it only backs up one?

Paul

No, It does back up all databases when I run it manually. I am just looking for a way to automate it to make sure I get good backups.

When you backup this way it will also get a good backup of databases that are open on in use, correct?

-CoderXMan

Muchas Gracias!!

I created a batch file as a scheduled task to run each night on my web server. The batch file is:

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

This works for me!

I have been using your script for awhile now and it has been working very well. The only thing is I notice my databases were growing out of control over 150 GB's. From what I can gather it is because the transaction logs are not being purged. Do you have any code you can add to this that will back up the *.trn files also to purge these out of the databases?

Thank you,

CoderXMan

Thank you Paul. You're a time-saver..

Thank you.

You save me a lot of work.

Haj

Hi,

Excellent Script My dear friend.

Thanks a lot

Nice Code. It helped me a lot.

Very usefull script, thx!

Got it to work with your help.

I need to backup Database in to my Tape. how can i change the code. pls help.

thans

Hi

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)

SET path = NetworkBackupShare N'\' ServerName N'\' folderdate + N'\'

gives error. pls correct

Gr8888 work!!!!

Really good work !

Thank all you so much!

Gr888888888888888888888888888 work MANNNNNNNNNNN!!!!

THANK YOU ,THANK'S A LOT

This is wonderful script.. It is helping me in taking backup of all my SQL DBs in a single shot.