Backup scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Backup scripts

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-10-28 : 10:18:11

I want to take full backup everyday for all databases on the server.

Please correct me if the below scripts is missing any thing.



CREATE procedure sp_Fullbackup as 

DECLARE @BackupFile varchar(255), @DB varchar(50) 
DECLARE @BackupDirectory nvarchar(200), @Name varchar(50) 
DECLARE backup_cursor cursor 
FOR SELECT name FROM master.dbo.sysdatabases WHERE name not in ('master','model','msdb','tempdb') 
OPEN backup_Cursor 
FETCH next FROM backup_Cursor INTO @DB 
WHILE @@fetch_status = 0 
BEGIN 
--SET @name = @DB + '-'+CONVERT(varchar(50), CURRENT_TIMESTAMP ,112) + '.diff' 
select @name = @DB +'\'+ @DB + '_'+convert(char(4),datepart(yyyy,getdate()) )+ 
replicate('0',2 - len(convert(varchar(2),datepart(mm,getdate())))) + convert(varchar(2),datepart(mm,getdate())) + 
replicate('0',2 - len(convert(varchar(2),datepart(dd,getdate())))) + convert(varchar(2),datepart(dd,getdate())) + 
replicate('0',2 - len(convert(varchar(2),datepart(hh,getdate())))) + convert(varchar(2),datepart(hh,getdate())) + 
replicate('0',2 - len(convert(varchar(2),datepart(mi,getdate())))) + convert(varchar(2),datepart(mi,getdate())) + 
'.diff' 

SELECT @BackupDirectory = '(Backup Path)' ----Insert Desired Backup Path

SET @Backupfile = @BackupDirectory + @name 
--print @name 
--print @BackupDirectory 
--print @Backupfile 
backup database @DB to disk = @Backupfile WITH NAME = @Name, full 
FETCH next FROM backup_Cursor INTO @DB 
END 
CLOSE Backup_Cursor 
DEALLOCATE Backup_Cursor

Kristen
Test

22859 Posts

Posted - 2011-10-28 : 10:58:15

Don't start your Sproc names "sp_" - that's reserved for system, and will adversely affect performance.

Routine needs to check for database types that cannot be backed up. Such as offline or suspended etc.

Ditto if you make LOG backups, then databases set to SIMPLE recovery model need excluding.

Your cursor-approach will backup database sequentially, so if one takes a long time the subsequent ones will be delayed. I suppose if you get an error on one then the late ones won't backup at all.

You might want to use SET NOCOUNT ON within the Sproc so that any "output" doesn't confuse whatever launches this Sproc.

Return a ZERO / Non-Zero to indicate Success/Error.

Change

CREATE procedure sp_Fullbackup

to

CREATE procedure dbo.sp_Fullbackup

to prevent the sproc accidentally being created under a user-account

Julien.Crawford
Starting Member

21 Posts

Posted - 2011-10-31 : 00:53:51

There is a means of doing the each-database bit.


declare @sql varchar(4000)
set @sql = 'D:\Backup\'+convert(varchar(8), GETDATE(), 112)
exec master.dbo.xp_create_subdir @sql 
go

-- Daily Once - a FULL BACKUP
declare @sql varchar(4000)
set @sql = 'if DB_ID(''?'') > 4 begin backup database ? to DISK = ''D:\Backup\'+convert(varchar(8), GETDATE(), 112)+'\?_full_'+replace(convert(varchar(8), GETDATE(), 108),':','')+'.bak'' with COMPRESSION, init end'
-- select @sql
exec sp_msforeachdb @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources