| Author |
Topic |
|
denkide
Starting Member
3 Posts |
 Posted - 2008-08-18 : 12:16:31
|
| I have a question that i cannot find an exact answer to in other posts. I apologize if this is covered elsewhere.I am trying to get a handle on my logs. I currently do a full backup and a log backup each night. I am trying to backup my log files each hour, but keep getting the error message that says that there is not a current valid database backup.If i change the hourly backups to include a differential backup first, i can then backup the log files. Am i doing this correctly or is there some better way of doing this?I have read through many of the things that Kristen posted here (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,Backup) but didn;t find an answer that will work for me.thanks for any help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-18 : 12:26:56
|
| Are you truncating the transaction log at some point? If so, you are breaking the log chain, hence the error. Do not truncate the logs, just backup the tlog more frequently to manage the tlog size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
denkide
Starting Member
3 Posts |
Posted - 2008-08-18 : 13:00:57
|
| hi tara,Thanks for the quick reply.I changed the job to look like this This is the nightly backup-----------------------------------BACKUP DATABASE [dbX] TO DISK = N'F:\VectorData_Files\PROD\backup\dbX.bak'WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, STATS = 10BACKUP LOG [dbX] TO DISK = N'F:\VectorData_Files\PROD\backup\dbX_transaction.bak' WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, STATS = 10This is the hourly backup-------------------------------------DECLARE @now char(14) DECLARE @fileName varchar(200)SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')SET @fileName = 'F:\VectorData_Files\PROD\backup\dbX_TRANS_' + @now + '.bak'BACKUP LOG [dbX] TO DISK = @fileNameWITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, STATS = 10So far it has worked.I suppose time will tell ... unless you see anything that jumps out at you.thanks for the help, Tara.david |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denkide
Starting Member
3 Posts |
Posted - 2008-08-18 : 13:20:24
|
thanks!  |
|
|
|
tanu
Yak Posting Veteran
57 Posts |
Posted - 2008-08-18 : 17:31:21
|
| Thanks, tara. Now I will use your stored procedure for mybackup routine.I can create a tsql job that can run every day.Right now I do one backup in the night and 8 transaction and 2 differential backup.Do i need to take some precautions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-18 : 17:33:44
|
| I wouldn't recommend running 2 diffs during the day. Typically you run a diff so that you don't need to do a full backup daily. So some people will run full backups once per week and then differentials the other 6 days of the week. I don't run any differential backups as part of my backup scheme. We've got the disk space to run fulls each night.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Transaction logs