Re: Daily Backup Filename

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 22:26:31 +0530

Hi,

Execute the below procedure inMaster database and schedule it using
Management -- SQL Agent-- Jobs -- Add jobs with TSQL type option. THis
procedure will backup all the databases except
'model','pubs','tempdb','northwind'. This will backup the databases with
DBNAME_DD-MM-YYY Y_DUMP.BAK

CREATE PROCEDURE BACKUP_SP @PATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @NAME VARCHAR(100),
DECLARE @DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')

OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @DBNAME

WHILE @@FETCH_STATUS=0

 BEGIN

     SELECT NAME=@PATH+@DBNAME+'_'+ltrim (rtrim
(convert(char,getdate(),105)))+'Dump.bak'
      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 Execute
This procedure will take @PATH as the input parameter, Say you have to
backup the database to D:\SQLBACKUP folder then the execution will be

EXEC BACKUP_SP 'D:\SQLBACKUP\'

This will backup all the databases to backup files based on database name
and date . So arrange a purging mechanism to delete the old files.

Thanks
Hari
MCDBA

"Jon Glazer" <jglazer.deleteme@adconn.com> wrote in message
news:Ld4Vc.23502$Nl1.16115@fe1.columbus.rr.com...
> I would like to run a backup daily using the backup command. However I
want
> the file to be named after whatever day it is. Monday, tuesday, etc...
>
> Can anyone help me do this? How do you pull the day of the week off the
> system?
>
> Thanks!
>
>

Relevant Pages

  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: RMAN restore of full backup fails completely
    ... You are not using a catalog database. ... You might have autobackup controlfile on, ... Your autobackup has been made *prior* to the backup! ... plus archivelog', when i try a restore on the other server however, the ...
    (comp.databases.oracle.server)
  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • Re: NTBACKUP error
    ... Are you putting the oracle tablespaces in backup mode before doing the ... start of the backup which they will be doing if the database is ... Shutting down archive processes ... Undo Segment 1 Onlined ...
    (comp.databases.oracle.server)
  • Re: NTBACKUP error
    ... Are you putting the oracle tablespaces in backup mode before doing the ... start of the backup which they will be doing if the database is ... Shutting down archive processes ... Undo Segment 1 Onlined ...
    (comp.databases.oracle.server)