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
>
>

Differential Backup in Maintenance Plan?

Hello. Can you schedule a differential backup in a
Database Maint Plan? I didn't see any way to do that, so
my guess is this is a sqlwish type of thing? Unless
someone knows a trick? Thanks, BruceHello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||Hello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||ok Andy... Yep, a scheduled job would be fine. Just not
ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>--Original Message--
>I thought I just saw Tibor answer this question a few
minutes ago. Oh well,
>no there is not a way to do it in the MP. Create your
own scheduled job and
>do it there.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:006401c34641$7daeeba0$a501280a@.phx.gbl...
>> Hello. Can you schedule a differential backup in a
>> Database Maint Plan? I didn't see any way to do that,
so
>> my guess is this is a sqlwish type of thing? Unless
>> someone knows a trick? Thanks, Bruce
>
>.
>|||Why not put ALLLLL the backups in your own scheduled jobs then to be
consistent. You have much better control over things if you don't use the
wizard and there really isn't anything the wizard can do that you can't with
a few lines of code.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0b4001c34647$70000e20$a001280a@.phx.gbl...
> ok Andy... Yep, a scheduled job would be fine. Just not
> ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>
> >--Original Message--
> >I thought I just saw Tibor answer this question a few
> minutes ago. Oh well,
> >no there is not a way to do it in the MP. Create your
> own scheduled job and
> >do it there.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:006401c34641$7daeeba0$a501280a@.phx.gbl...
> >> Hello. Can you schedule a differential backup in a
> >> Database Maint Plan? I didn't see any way to do that,
> so
> >> my guess is this is a sqlwish type of thing? Unless
> >> someone knows a trick? Thanks, Bruce
> >
> >
> >.
> >|||Bruce
You can use the backup wizard to create a differential
backup, if you do not want to code it yourself, and keep
it consistent if you need to do more than one.
Regards
John