Database maintence plan

I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
TomWe have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom

Database maintence plan

I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
Tom
We have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom

Database maintence plan

I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
TomWe have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom

Database Maint issue

I've got an issue when creating a database maintenance plan. When I get to
the
Specify Backup Disk Directory (in the DB Maintenance Plan Wizard), the
drop-down box for specifying the remove files older than is blank, and the
default timeframe is 0, and the Backup file extension is blank. On other SQL
servers, by default these locations show the drop-down as Week(s), the
timeframe as 4, and the extention as BAK.
If I continue setting up the maintenance plan, when I get to the Specify
Transaction Log Backup Disk Directory, I'll run into the same problem as the
Database (blank and 0 for the same fields).
In any event it will allow me to setup the plans, however I'd like to be
able to set the remove feature and I can't...
Any ideas?
hi,
Darth Blubber wrote:
> I've got an issue when creating a database maintenance plan. When I
> get to the
> Specify Backup Disk Directory (in the DB Maintenance Plan Wizard), the
> drop-down box for specifying the remove files older than is blank,
> and the default timeframe is 0, and the Backup file extension is
> blank. On other SQL servers, by default these locations show the
> drop-down as Week(s), the timeframe as 4, and the extention as BAK.
> If I continue setting up the maintenance plan, when I get to the
> Specify Transaction Log Backup Disk Directory, I'll run into the same
> problem as the Database (blank and 0 for the same fields).
> In any event it will allow me to setup the plans, however I'd like to
> be able to set the remove feature and I can't...
> Any ideas?
this is a long nightmare with Maintenance Plan withard (which actually is
very buggy)... please have a look at
http://groups.google.it/groups?as_q=...0&as_maxy=2005
for a short idea...
I strongly recommend you to write your own SQL Server jobs in
Transact-SQL... this can be a little bit longer than in the wizard,
requiring you to improve your skills, but it will pay in the long terms...
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply