Differential Backups in Maintenance Plans

Using SQL Server2000, I don't see any way of setting up a maint plan to
include nightly differential backups. I would like to use a maint plan as it
allows you to select "All user DBs". Since we add databases quite a lot, I
like the dynamic aspect of maint plans.
Does anyone know of workaround or even a script that would allow me to
dynamically backup each user database? I've looked at sp_msforeachdb, but
can't seem to get it to work as I'm not too good at scripting.
Thanks
RonRon,
In SQL Server 2000, as you have discovered, there is no way to do
differential backups. Technet when discussing SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ELYAG
describes how to set up differential backups one database at a time,
creating a schedule for each differential backup. By the way, please note
that _master_ cannot be backed up differentially.
You, of course, do not want to do that, but if you do it for one database it
will give you the working syntax. E.g.
BACKUP DATABASE MyDatabase TO
DISK = N'\\BackupServer\MyDatabase_diff_200710230021.BAK'
WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,
NAME = N'MyDatabase backup', NOSKIP , STATS = 10, NOFORMAT
Now, from that perhaps you can create a script from that. Here is one that
only PRINTs the command, but you can change this to EXECUTE it instead.
sp_msforeachdb @.command1='
DECLARE @.BuildStr NVARCHAR(500)
SET @.BuildStr = CONVERT(NVARCHAR(20),GETDATE(),120)
SET @.BuildStr = REPLACE(REPLACE(REPLACE(@.BuildStr,''
'',''''),'':'',''''),''-'','''')
SET @.BuildStr = ''
Backup Database $ TO DISK = N''''\\BackupServer\$_diff_''+@.BuildStr
SET @.BuildStr = @.Buildstr + '''
WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,''
SET @.BuildStr = @.Buildstr + ''
NAME = N''''$ backup'''', NOSKIP , STATS = 10, NOFORMAT ''
IF ''$''<>''master''
PRINT @.BuildStr'
,@.replacechar='$'
Of course, the backups need to be in the same location as other backups for
your maintenance plan deletion of old files to include these as well. Also,
remember that sp_msforeachdb is unsupported.
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:44788552-025A-4A26-8387-F31F069AE7D2@.microsoft.com...
> Using SQL Server2000, I don't see any way of setting up a maint plan to
> include nightly differential backups. I would like to use a maint plan as
> it
> allows you to select "All user DBs". Since we add databases quite a lot,
> I
> like the dynamic aspect of maint plans.
> Does anyone know of workaround or even a script that would allow me to
> dynamically backup each user database? I've looked at sp_msforeachdb, but
> can't seem to get it to work as I'm not too good at scripting.
> Thanks
> Ron|||Thank you Russell!
"Russell Fields" wrote:
> Ron,
> In SQL Server 2000, as you have discovered, there is no way to do
> differential backups. Technet when discussing SQL Server 2000
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ELYAG
> describes how to set up differential backups one database at a time,
> creating a schedule for each differential backup. By the way, please note
> that _master_ cannot be backed up differentially.
> You, of course, do not want to do that, but if you do it for one database it
> will give you the working syntax. E.g.
> BACKUP DATABASE MyDatabase TO
> DISK = N'\\BackupServer\MyDatabase_diff_200710230021.BAK'
> WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,
> NAME = N'MyDatabase backup', NOSKIP , STATS = 10, NOFORMAT
> Now, from that perhaps you can create a script from that. Here is one that
> only PRINTs the command, but you can change this to EXECUTE it instead.
> sp_msforeachdb @.command1='
> DECLARE @.BuildStr NVARCHAR(500)
> SET @.BuildStr = CONVERT(NVARCHAR(20),GETDATE(),120)
> SET @.BuildStr = REPLACE(REPLACE(REPLACE(@.BuildStr,''
> '',''''),'':'',''''),''-'','''')
> SET @.BuildStr = ''
> Backup Database $ TO DISK = N''''\\BackupServer\$_diff_''+@.BuildStr
> SET @.BuildStr = @.Buildstr + '''
> WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,''
> SET @.BuildStr = @.Buildstr + ''
> NAME = N''''$ backup'''', NOSKIP , STATS = 10, NOFORMAT ''
> IF ''$''<>''master''
> PRINT @.BuildStr'
> ,@.replacechar='$'
> Of course, the backups need to be in the same location as other backups for
> your maintenance plan deletion of old files to include these as well. Also,
> remember that sp_msforeachdb is unsupported.
> RLF
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:44788552-025A-4A26-8387-F31F069AE7D2@.microsoft.com...
> > Using SQL Server2000, I don't see any way of setting up a maint plan to
> > include nightly differential backups. I would like to use a maint plan as
> > it
> > allows you to select "All user DBs". Since we add databases quite a lot,
> > I
> > like the dynamic aspect of maint plans.
> >
> > Does anyone know of workaround or even a script that would allow me to
> > dynamically backup each user database? I've looked at sp_msforeachdb, but
> > can't seem to get it to work as I'm not too good at scripting.
> >
> > Thanks
> >
> > Ron
>
>