Hi
I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.
The commands to do this are as follows and are set up to run as
database jobs:
-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0
At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)
I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.
Does anyone have any ideas?
Many thanks.
Thiko!
3  7548 
-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- After that (once a day)
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT
"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om... Hi
I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.
The commands to do this are as follows and are set up to run as
database jobs:
-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0
At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)
I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.
Does anyone have any ideas?
Many thanks.
Thiko!
Hi!
This is very helpful. Thank you very much.
Could you tell me please why to truncate the transaction log
immediately after the full database backup command? What happens if I
need to recovery some of the transactions that were taking place
during the full database backup, for example? Would it be better to
do the truncate transaction log with the INIT immediately before the
full database backup takes place?
Many Thanks.
Thiko!
"Igor Raytsin" <n&*@cyberus.ca> wrote in message news:<3f********@news.cybersurf.net>... -- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- After that (once a day)
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT
"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om... Hi
I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.
The commands to do this are as follows and are set up to run as
database jobs:
-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0
At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)
I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.
Does anyone have any ideas?
Many thanks.
Thiko!
Hi,
There is some misunderstanding here:
1. You do not need to truncate transaction log manually. Full Backup or
Transaction Log Backup will do it for you.
2. INIT option does not truncate log. INIT specifies that all backup sets
should be overwritten.
Regards,
Igor
"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om... Hi!
This is very helpful. Thank you very much.
Could you tell me please why to truncate the transaction log
immediately after the full database backup command? What happens if I
need to recovery some of the transactions that were taking place
during the full database backup, for example? Would it be better to
do the truncate transaction log with the INIT immediately before the
full database backup takes place?
Many Thanks.
Thiko!
"Igor Raytsin" <n&*@cyberus.ca> wrote in message
news:<3f********@news.cybersurf.net>... -- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- After that (once a day)
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT
"Thiko" <bi*****@hotmail.com> wrote in message
news:b8**************************@posting.google.c om... Hi
I take one nightly full database backup at 02:00 and backup the
transaction log to one backup set every 15mins.
The commands to do this are as follows and are set up to run as
database jobs:
-- Database Backup
BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,
NOFORMAT
-- Transaction Log Backup Every 15mins.
BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
RETAINDAYS = 0
At the moment the transaction log backups every 15mins are simply
being added one by one to the one backup set. I want the backed up
transaction logs in the backup set after 24hrs to be overwritten so
disk space does not add up. I thought RETAINDAYS would do this as an
expirydate is set to expire on the time it is created so i would have
expexted it to be overwritten when that time comes around the next
day. (But is doesent)
I think it is ok to overwrite the transaction log in the backupset
after 24hrs as you only need the transaction logs since the last full
backup (which happens nightly at 02:00) for recovery.
Does anyone have any ideas?
Many thanks.
Thiko!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: TZoner |
last post by:
1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
2) Is it safe to delete it, as SQL will create a new Transaction...
|
by: xo55ox |
last post by:
Hi,
I have been trying to set up an automated restore process from prod to
backup server. First, I schedule the full database backup nightly,
transfer the backup file and restore it to the...
|
by: xo55ox |
last post by:
Hi,
Currently, I am on SQL2000 SP3 and Windows 2003.
I am trying to restore the full database backup and transaction log
backup on the development server. The full database restore is not a...
|
by: Burkhard Schultheis |
last post by:
As I wrote last week, we have a problem with a DB2 V8 on Linux. Here is
what is in db2diag.log during online backup:
Starting a full database backup.
2004-04-01-02.33.54.760164 ...
|
by: xixi |
last post by:
i am using db2 udb v8.1 , so if parameter retain log records is off ,
i am assuing it use circular logging, in the doc it explains the log
records fill the log files and then overwrite the initial...
|
by: Balaji |
last post by:
Hi,
I got a problem while loading a table on DB2 database. It is saying that
transation log is full and do more commits in between. Is it done by a DB2
or can I fix this at BD2 server?
Thanks...
|
by: DataPro |
last post by:
New to Sql Server, running SQL Server 2000.
Our transaction log file backups occasionally fail as the size of the
transaction log gets really huge. We'd like to schedule additional
transaction...
|
by: yashgt |
last post by:
Hi,
We have created a SQL server 2000 database. We observe that the
transaction log keeps growing over time. We are now about to run out of
space. We have been periodically shrinking the...
|
by: sifrah |
last post by:
Hi All,
My SQL server transaction log is getting bigger every day and my HDD if
running out of space.
So i follow the MS KB about how to Shrinking the Transaction Log.
After doing so the log is...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
