October 28, 2011 at 8:26 am
I would like to automate the full backup for all databases on the server daily.
Below are the scripts which gave me incorrect syntax for the below line (full)
backup database @DB to disk = @Backupfile WITH NAME = @Name, full
Please correct me if anything is missing.
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) + '.full'
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())) +
'.full'
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
October 28, 2011 at 9:11 am
'full' is not a keyword on backups.
p.s. please don't create stored procedures starting with sp_. sp means system procedure, and yours is not a system procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2011 at 9:22 am
Gila is correct as usual, try pulling the full off of your statement.
Before you get too deep into reinventing the wheel you might want to take a look at this site.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply