Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert Lie
Hi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DXUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','noXrthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.XSERVERNAME+'_'+@.DBN AME+'_'+ltrXim(rtrim(convert
(char,getdate(),105)))+'Dump.bXak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie
|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:

> Hi,
> Compile the below stored procedure in Master database and define the folder
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DXUMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','noXrthwind')
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@.XSERVERNAME+'_'+@.DB NAME+'_'+ltrXim(rtrim(convert
> (char,getdate(),105)))+'Dump.bXak'
> BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
> @.DBNAME, NOSKIP , STATS = 10, NOFORMAT
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> END
> CLOSE BACKUP_CUR
> DEALLOCATE BACKUP_CUR
> end
>
> How to schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
>