Weekly Full Backup

,

This script will backup all databases that are online (sql 2000). So if a new database is added you will get a backup. This is setup as a weekly job at our site (Sunday night).

SET QUOTED_IDENTIFIER off
select getdate() "Start Time"
set nocount on

declare @dbname varchar(36),@cmd varchar(255)
declare dbname_cursor cursor
    for select name from master..sysdatabases where name != 'tempdb'
          order by name

open  dbname_cursor
fetch dbname_cursor into @dbname

while @@fetch_status = 0
begin
if  DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'
  begin
        select @cmd ='backup database '+@dbname+' to DISK="E:\Program Files\Microsoft SQL Server\MSSQL\backup\'+@dbname+'.bak" with init'
           print @cmd
          execute (@cmd)
  end

fetch dbname_cursor into @dbname
end

close dbname_cursor
deallocate dbname_cursor
select getdate() "End Time"

Rate

5 (1)

Share

Share

Rate

5 (1)