Post reply

database backup using stored procedure

  • November 11, 2007 at 11:04 am

    #183819

    Hi,

    I want to write a stored procedure to backup a database and store it in a folder in C:

    Moreover say if the filename provide is abc.bak the backed up file should have a timestamp associated with it .

    abc.bak should read abc_20071111_06:00:00.bak ( the current day and time) . Actually I'll schedule a job using the stored procedure to take backups every 12 hrs so I need the timestamp associated with the filename.

    Any help is really appreciated!!

  • November 11, 2007 at 10:12 pm

    #750142

    Have a look on http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31932/[/url]

  • November 12, 2007 at 11:58 am

    #750342

    DECLARE @BackupFile char(91), -- 91 is length of BACKUP script

    @strSQL VARCHAR(8000), @DateSuffix char(16)

    set @DateSuffix = CONVERT( char(16), getdate(), 121 )

    set @DateSuffix = replace(@DateSuffix, '-', '')

    set @DateSuffix = replace(@DateSuffix, ' ', '')

    set @DateSuffix = replace(@DateSuffix, ':', '')

    set @BackupFile = ('E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Web_1_TI_CMS\TI_CMS_db_' + rtrim(@DateSuffix) +'.BAK')

    set @strSQL = 'BACKUP DATABASE [TI_CMS] TO DISK = N' +''''+ @BackupFile + ''''+ ' WITH NOFORMAT, NOINIT, NAME = N' + '''TI_CMS-Full Database Backup''' + ', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

    -- run the Backup script.

    EXECUTE(@strSQL)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply