Backup log file
-
Dear All,
I have a data warehouseing database that I backup Full and Differential once a day.
Questions:
1. How often should I backup the log?
Thank you.
TJ
-
That depends on how much data are you willing to lose
* Noel -
How often does the data warehouse get populated?
If it is a periodic load then having the database in simple recovery mode is OK. When you do a data load then switch to BULK RECOVERY mode otherwise your loads will be too slow.
If you are continuously loading the warehouse then, as NoelD says backup the log depending on how much data you are prepared to lose.
Another factor is where you get your data from for your data warehouse? If the source is always available then having a complex recovery plan for the datawarehouse may be overkill.
-
Thank you for your help!!
The database is populated every 10 minutes data from remote sql server databases.
Officially, I did not want to lose too much data.
Currently, the recovery model is setup Full.
Is the Full recovery model not a good option?
Best regards,
TJ
-
Recovery model full is the best option to backup tlogs. If you dont want to lose much data then backup tlogs every 5 or 10 mins and have a retaination period of 1 day so that you have last 1 days log backups to recover.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Hi Sugesh,
I setup my disaster and recovery in Microsoft SQL Server Management Studio via Maintenance Plan Wizard.
Enclosed is the generated scripts:
---FULL DATABASE backup
BACKUP DATABASE [SQLID02] TO DISK =
N'C:\ SQLIDBV02_Backups \SQLIDBV02_Full.bak' WITH NOFORMAT, INIT, NAME =
N'SQLIDBV02_backup_20070716115438', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name = N'SQLIDBV02'
and backup_set_id = ( select max(backup_set_id)
from msdb..backupset
where database_name = N'SQLIDBV02'
)
if @backupSetId is null
begin
raiserror ( N'Verify failed. Backup information for database ''SQLIDBV02''
not found.',16, 1 )
end
RESTORE VERIFYONLY FROM DISK = N'C:\SQLIDBV02_Backups\SQLIDBV02_Full.bak' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND
--- DIFFERENTIAL DATABASE Backup
BACKUP DATABASE [SQLIDBV02] TO DISK =
N'C:\ SQLIDBV02_Backups\SQLIDBV02_Diff.bak' WITH DIFFERENTIAL, NOFORMAT,
INIT, NAME = N'SQLIDBV02_backup_20070716120207', SKIP, REWIND, NOUNLOAD,
STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name = N'SQLIDBV02'
and backup_set_id = ( select max(backup_set_id)
from msdb..backupset
where database_name = N'SQLIDBV02'
)
if @backupSetId is null
begin
raiserror ( N'Verify failed. Backup information for database ''SQLIDBV02''
not found.',16, 1 )
end
RESTORE VERIFYONLY FROM DISK = N'C:\SQLIDBV02_Backups\SQLIDBV02_Diff.bak' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND
--- LOG DATABASE BACKUP
BACKUP LOG [SQLIDBV02] TO DISK = N'C:\SQLIDBV02_Backups\SQLIDBV02_Log.bak'
WITH NOFORMAT, INIT, NAME = N'SQLIDBV02_backup_20070716120416', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name = N'SQLIDBV02'
and backup_set_id = ( select max(backup_set_id)
from msdb..backupset
where database_name = N'SQLIDBV02'
)
if @backupSetId is null
begin
raiserror ( N'Verify failed. Backup information for database ''SQLIDBV02''
not found.',16, 1 )
end
RESTORE VERIFYONLY FROM DISK = N'C:\SQLIDBV02_Backups\SQLIDBV02_Log.bak' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND
What is the best practice to backup the database log in every 5 or 10 minutes as you have suggessted. Did you have any sample t-sql scripts?
Thank you.
TJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply