Jerry Nixon @Work: Backing up SQL 2005

Jerry Nixon on Windows

Thursday, December 22, 2005

Backing up SQL 2005

Because the beta version of SQL 2005 we are using did not come with the management studio, we had to back up the server manually (with tSQL) and I wrote the following script which backs up every single database on the server (except tempdb). Here it is:

DECLARE DbCursor CURSOR
READ_ONLY
FOR select Name from sys.databases where Name <> 'tempdb'
DECLARE @DbName varchar(40)
OPEN DbCursor
FETCH NEXT FROM DbCursor INTO @DbName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

declare @date varchar(100)
set @date = convert(varchar(4), datepart(yyyy, getdate()))
set @date = @date + '-'
set @date = @date + convert(varchar(4), datepart(mm, getdate()))
set @date = @date + '-'
set @date = @date + convert(varchar(4), datepart(dd, getdate()))
set @date = @date + '-'
set @date = @date + convert(varchar(4), datepart(hh, getdate()))
set @date = @date + '-'
set @date = @date + convert(varchar(4), datepart(n, getdate()))
set @date = @date + '-'
set @date = @date + convert(varchar(4), datepart(s, getdate()))
set @date = @date + '-'
set @date = @date + convert(varchar(4), datepart(ms, getdate()))
set @date = @date + '-'

declare @BackUpDeviceName varchar(150)
set @BackUpDeviceName = @DbName + '_BackupDevice'
declare @BackUpDeviceFile varchar(150)
set @BackUpDeviceFile = 'c:\SqlBak\' + @DbName + '_' + @date + '.BAK'
if (exists (select * from master.dbo.sysdevices
where name = @BackUpDeviceName))
exec sp_dropdevice @BackUpDeviceName
exec sp_addumpdevice 'disk', @BackUpDeviceName, @BackUpDeviceFile
backup database @DbName TO @BackUpDeviceName
exec sp_dropdevice @BackUpDeviceName
print @DbName + ' is backed up as of ' + @date

FETCH NEXT FROM DbCursor INTO @DbName
END
END
CLOSE DbCursor
DEALLOCATE DbCursor
GO