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

behavior of ssis packages running under SSMS manually vs. job agent automatically

I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is

"An out-of-memory condition prevented the creation of the buffer object. "

Anyway, the package runs successfully when it runs automatically under the job agent.

I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.

js40

If you are running through SSMS, you are likley executing the package via your machine, not the server. Do you have the full SSIS client installed? (Do you have a SQL Server Integration Service service when looking at "Services" under the control panel?)|||

No, i ran the package on the same server as where it runs automatically under sql agent. I started it manually from SMSS under remote desktop. The server is sql server 2005 (64 bit) with SP1 and 8gig memory.

Actually, several times it crashed SMSS and the last thing I saw just before the SMSS window just plain disappeared was some errors related to memory.

Below are different errors i got when i ran this package serveral times.

1) The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

2)The system reports 99 percent memory load. There are 8588873728 bytes of physical memory with 67133440 bytes free. There are 2147352576 bytes of virtual memory with 27369472 bytes free. The paging file has 12954959872 bytes with 4205600768 bytes free.

3)A buffer failed while allocating 2048 bytes.

4)An out-of-memory condition prevented the creation of the buffer object.

js40