May 5, 2006 at 8:14 am
here's what i'm trying to do.
i'm trying to pass parameters into this script.
set quoted_identifier off
set nocount on
declare @dbname varchar(36),@cmd varchar(255)
declare dbname_cursor cursor
for
select DISTINCT db.name from master..sysdatabases db, msdb..backupset bs,msdb..backupmediafamily bf
where db.name not in ('master','tempdb')
and bs.database_name = db.name and bs.type ='d' and bs.media_set_id = bf.media_set_id
and physical_device_name = 'E:\Program Files\Microsoft SQL Server\MSSQL\backup\' + convert(varchar(40),db.name) + '.bak'
order by db.name
open dbname_cursor
fetch dbname_cursor into @dbname
while @@fetch_status = 0
begin
if DATABASEPROPERTYEX(@dbname,'Recovery') = 'SIMPLE' and DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'
begin
select @cmd ='backup database '+@dbname+' to DISK="E:\Program Files\Microsoft SQL Server\MSSQL\backup\'+@dbname+'.bak" with noinit,differential'
print @cmd
execute (@cmd)
end
fetch dbname_cursor into @dbname
end
close dbname_cursor
deallocate dbname_cursor
_________________________
May 5, 2006 at 9:26 am
What advice would you like?
Mark
May 5, 2006 at 11:04 am
well... never mind that script. i was able to do some thing else like this:
set quoted_identifier off
set nocount on
declare @dbname varchar(36),@cmd varchar(255)
declare dbname_cursor cursor
for
select [name] from master..sysdatabases where [name] not in ('master','tempdb')
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="C:\backup\'+@dbname+'.bak" with noinit' --not sure if i need this disk location.
print @cmd
execute (@cmd)
end
fetch dbname_cursor into @dbname
end
close dbname_cursor
deallocate dbname_cursor
what if i wanted to pass in parameters for the 'type' of backup. and i wanted
those parameters to be pulled from a table.
for example:
(F) is Full
(L) is Log
(D) is Differential
and those paremeters would be defined in a table. i dunno... does that make sense?
_________________________
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply