Best practice-backup-

I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>>I am confused because I see two options on the backup plan of SQL 2000.
>> Under SQL Server Enterprise Manager - SQL Agent:
>> 1.Security Backup (copia de seguridad)
>> Here I can setup a simple copy, at first it allows me only to select the
>> path and filename. Then now I was able to right click and select the
>> schedulle and all.
>> 2.Database Maintaince Plan (Planes de mantenimento de la base)
>> Here I can do more then just backup. And what I like best is that it has
>> option to automtacly remove files older the x day plus the schedulle.
>> Is there a link a good SQL backup plan.
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
> script
>> for Full backup|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK ='g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK ='g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK ='g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK => 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK => 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK => 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>> Yes, Hari if you could post any samples of scripts.
>> Well I found something in the net witch I will give it a try.
>> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
>> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup