how to program bakup in sql

drachx · October 13th, 2004, 11:15 PM

hi guys,
how do i make a batch file that will bakup all my 5 databse (with each file is databasename and the date it is bkup)
in enterprise manager
m using sql2000

happygv · October 14th, 2004, 02:48 AM

I got a script that does. Tested and works fine. Just fine tuning it a bit. Will post that here once I am done with it.

_________________________
- Vijay G
Strive for Perfection

drachx · October 14th, 2004, 03:00 AM

ok, Ill wait for that

happygv · October 14th, 2004, 03:27 AM

Here you go. Will post the code again if any changes made to it in future. Go ahead and create this under MASTER DB

Code:

Create Procedure dbo.sp_BackupAllDbs
as
    set nocount on
    declare @name sysname
    declare @SQL  nvarchar(600)

    declare c1 cursor for 
        select name from master.dbo.sysdatabases

    open c1
    fetch c1 into @name

    while @@fetch_status >= 0
    begin
        If Exists(select 1 from sysdevices where name = @name + '_dev')
        Begin
            Set @SQL = 'sp_dropdevice ' + @name + '_dev'
            --select @sql
            execute (@SQL)
        End
        Set @SQL = 'sp_addumpdevice ''disk'',''' + @name + '_dev' + ''',''DRIVELETTER:\MSSQL7\BACKUP\' + @name + '_' + convert(varchar(10),getdate(),121) + '.bak'''
        -- select @sql
        execute (@SQL)
        Set @SQL = 'BACKUP DATABASE ' + @name + ' TO ' + @name + '_dev'
        -- select @sql
        execute (@SQL)
        fetch c1 into @name
    end
    deallocate c1
    set @sql = 'xp_cmdshell ''del DRIVELETTER:\MSSQL7\BACKUP\*' + convert(varchar(10),getdate()-3,121) + '.bak'', NO_OUTPUT'
    -- select @sql
    execute (@SQL)
    set nocount off

This does backup all databases including system dbs to the location marked in RED(cahnges as your requirement), and keeps only last three days backup always(deletes the backup files that are older than 3 days marker in RED again, that can be changed to your need.)

Will notify you all if any changes I do to this in future.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Jago · October 14th, 2004, 03:40 PM

Hi Drachx,

I suggest to use the database maintanance wizard from within Enterprise Manager. In de wizard select the databases you want the create a maintanance plan for. At the end of the wizard you can configure a schedule for the plan. Beside a regular full database backup you can also configure the plan to do transaction log backups and or reindex and defragment your database/index.

Regards,

Jago

drachx · October 14th, 2004, 07:16 PM

tnx, i will try ur code:)

pgtips · October 15th, 2004, 03:05 AM

or you can use sqlmaint.exe from the command-line (via batch file), or xp_sqlmaint from T-SQL

happygv · October 16th, 2004, 01:14 PM

Yes, Jago and Phil are right. I had developed this procedure as a fix to the sql maintenance jobs, as those weren't working as expected for sometime. Now that too has been fixed and so this is disabled. Though a good learning, that you can do a backup od dbs and logs on your own.

Cheers!

_________________________
- Vijay G
Strive for Perfection

drachx · October 17th, 2004, 07:40 PM

happygv,

tried ur code , and will be using it in our databse bakup,
tnx :)

i have a minor problem, it cannot bakup tempdb as shows after i run in it sqlanalyzer, how can you exclude tempdb in backing up or any suggestion?

jemacc · October 17th, 2004, 08:03 PM

try adding a where clause to this portion

Code:

declare c1 cursor for 
        select name from master.dbo.sysdatabases
where name not in ('master','tempdb')

Jaime E. Maccou
Applications Analyst