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
Showing posts with label maintence. Show all posts
Showing posts with label maintence. Show all posts
Monday, March 19, 2012
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
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
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
Wednesday, March 7, 2012
Database Maintenance Plan
I created a Database Maintence Plan to backup one of my database @. 7pm but doesn't seem to do anything at all. It was working for the past months untill just this morning i noticed that no latest *.BAK files were created since 18-Aug-2006. I have other databases which do backup OK except for this particular database. I can manually back it up but cannot automatically backup from the Database Maintenance Plan.
I can't seem to locate any error messages in the Log...Any ideas how i can get this working again?Check the SQL Server Agent Jobs to see if it is enabled. If it is not there, go to the DB Maintenance Job and reschedule it and the save it again. You should then see it in the SQL Server Agent Jobs.
Subscribe to:
Posts (Atom)