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
Home »
» Backing up SQL 2005
Backing up SQL 2005
|
0 comments:
Post a Comment