Backup Solution - Question About Transaction Log

Aug 6, 2007

I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.

Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.

The T-SQL being run in each case was

Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:

BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD

5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,

BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @i INT
select @i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db')
RESTORE VERIFYONLY FROM <device> WITH FILE = @i

This is then backed up to tape each night.

Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.

If I issue a

BACKUP LOG <database> to <device> WITH INIT, SKIP

then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?

Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?

Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?

Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated!
Matt

View 5 Replies


ADVERTISEMENT

[Q] High Transaction Load Solution?

Nov 10, 2001

Hey guys,

I orignally wrote a post here regarding some info on setting up a cluster. Upon further analysis of the problem with our system, I noted that at particular times we have tremendous amounst of Update, Insert, Delete etc, transactions hitting out database.

I thought originally SQL Clustering could solve this problem, but the time and upkeep that will be required to maintain such a configuration might not be feasible and more importantly it may not even fix the problem.

Next week I plan on doing some more specific performance monitoring off the database during normal business activity, but my initial suspicion is that there is a tremendous amount of I/O processing due to the high transaction load which is slowing down the application.

I was wondering what you have done to alleviate such problems? One of the solutions I have come up with is to possibly create a Master/Slave SQL Server design where the Slave handles most of the database transactions and then at a low load during the day update the Master DB. How does this sound? Any other ideas would be greatly appreciated...

Thanx

View 1 Replies View Related

What Is The Best Backup Solution?

Mar 2, 2004

SQL Gurus,

What is the best backup solution?

Product:MS SQL 2000 Enterprise Edition
Environment:webserver

Goal:Off-site backup of database without onsite tech intervention

Obstacles:
* Webhost does not want SQL Server Agent running for fear of viruses
* Database is 3.4GB, LDF is 3.7GB, and a Full Backup is 3.1GB.

Connection / Access:Enterprise Manager, RDP and FTP all work to access data and backups.

I have looked into doing a full backup then differential backups, doing transaction replication, and log shipping. The client doesn't really need replication, he just wants to know that if his server with the production database goes up in flames he has a way of getting back in business without losing more than one day of data.

I am hoping there is a method of "restoring" on his off-site server the data that is "backed up" on the production server. Is that possible if we're doing differential backups?

Is that the best solution or is there a better way?

Thanks for any recommendations or advice,
Alec Sherman

View 13 Replies View Related

Backup - Solution Needed

Feb 22, 2005

Provide solutions for the following :

My database name : developer


day 1. Taken full backup

day 2. taken differential backup

day 3. taken differential backup

day 4. taken differential backup

day 5. taken differential backup

day 6. taken differential backup


finished backups

month 1. after one month volunterily deleted records of tables.


month 2. want to view only database with day 2 .

( I want to restore only day2 backup (including full backup) and use the database)

Is it possible in Microsoft SQL Server?

Need Good solution or tool for this.

Expecting good suggestions or coding or tools

View 1 Replies View Related

Backup - Solution Needed

Jul 23, 2005

hi groupMy database name : developerday 1. Taken full backupday 2. taken differential backupday 3. taken differential backupday 4. taken differential backupday 5. taken differential backupday 6. taken differential backupfinished backupsmonth 1. after one month volunterily deleted records of tables.month 2. want to view only database with day 2 .( I want to restore only day2 backup (including full backup) and usethe database)Is it possible in Microsoft SQL Server?Need Good solution or tool for this.Expecting good suggestions or coding or toolsWith AdmirationRLN

View 3 Replies View Related

What Is The Best Availability And Backup Solution?

Feb 21, 2007

Hi Everyone,I'm working on a site that is running MS SQL 2000. They performregular backups of databases and log files and in the event of adisaster they have a second backup server.At present they regularly copy the backup up files from the productionserver to the standby server. The plan, in the event of a disaster, isto restore these backups onto the standby server and change the dnsrecords so that client machines will point to the standby server.Is there a better way?Thanks for any advice,Danny

View 2 Replies View Related

Backup Solution For 2005 Upgrade

Sep 20, 2007

Hi all

We have a huge terabyte database that we are planning to upgrade to 2005. We are working on realistic backup solutions in case we have to rollback the upgrade. We have over 400 tables out of which about 100 are kind of static. The other 300 tables have very high transaction rate - thousands of batch transactions per sec. Earlier, prior to 2005 SP2 release we tried setting up replication to a 2000 box and replication agents failed to catch up with the transactions. Not sure of SP2 has any improvements. We are thinking of setting up replication for only the 300 or so tables and we will take a regular nightly back up and can use a day's old data for the 100 small tables.

What are your experiences/suggestions on a proper back up solution to have a 2000 server in almost stand by mode that we can switch back to in case of any issues?

Thanks,
don.

View 10 Replies View Related

SQL Security :: Full Backup Needed After Restoration Of Database Before Transaction Log Backup

Jul 15, 2015

We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?

View 5 Replies View Related

Should Full Backup And Transaction Backup Be From One Plan?

Mar 11, 2008

Hello, everyone:

I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.

Is it true? Can anyone offer official documents?

In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.

Thanks

ZYT

View 2 Replies View Related

What Is The Differences Between A Differential Backup And Transaction Log Backup??

Jun 13, 2007

what is the differences between a differenctial backup and transaction log backup?

View 1 Replies View Related

Is It Possible To Restore From A Database Backup Without A Transaction Log Backup?

Oct 14, 2007

I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).

Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.

Thanks!


SQL Server 2005 on Windows 2003 Server x64.

View 16 Replies View Related

Transaction Log Backup

Apr 11, 2001

Hi

I have a full backup and transaction log backup strategy for my test server.
My transaction log backup is failing as some user must have run some nonlogged operation. Is there any way to find out what is causing transaction log backup to fail?

Any hint would be appreciated.

View 1 Replies View Related

Transaction Log Backup

Sep 18, 2001

I have a problem w/ my Transaction Log Backup. The backup files continues to grow and grow and grow. I have created a new Transaction Log backup through EM, but instead of appending each backup to the existing backup file, I overwrite the existing backup file. My gut feeling is that this is not right. If it isn't, then how can I continue to backup my TL's w/o having the file growing at an astronomical pace?

I would appreciate your help

Chris

View 2 Replies View Related

Backup Transaction Log

Feb 9, 2001

Is there a possibility to recover a database up to the point of failure ?
I have a database with a primary datafile and a transaction logfile.
I have backup of the database and transaction log backups upto an hour before failure. But I also want to recover one hour data which is in the transaction log.
I have lost my primary datafile.

Is there a way to recover ?

How do I backup the transaction log before starting restore ?

Thanks,
Bala

View 1 Replies View Related

Transaction Log Backup

Feb 2, 2001

Hi friends,
I have set up the Transaction Log backup on one server he was running fine for few hour later he failed for two times and then after full backup it is running fine.
Can any one of shed some light on this issue.
Error:
Cannot allow BACKUP LOG because file 'ZTVData' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup. [SQLSTATE 42000] (Error 4213) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Thank You,
Paul T

View 2 Replies View Related

Transaction Log Backup

Aug 26, 1999

Hi!
Does the command,
backup log <database name> to <backup device name>
clear the inactive portions of the transaction log?

Praveena

View 1 Replies View Related

Transaction Log Backup

Jun 9, 1999

I've set up 4 database maintenance plans for my databases. 1 for all system databases, and the other three for specific databases. they back up to a network drive on another computer.

The plans are set up to do complete backups every day and then transaction log backups every 15 minutes.

The complete backups work fine. They all create the backups on the network drive. The transaction log backups are only created for one of the databases though. All the others report "successful completion" but they don't actually create the backup file. I looked at the report created and it never has a step that backs up the transaction log. I compared the T-SQL code from the one transaction backup job that works to one that doesn't and all the commands are exactly the same (aside from different directories to save in to, job numbers, etc).

Does anyone have any idea what is going on? I've deleted the maintenance plans and recreated them to no avail. I've also created an on the side differential backup that works as well.

Any help would be appreciated

Bob

View 1 Replies View Related

Transaction Log Backup

Feb 21, 2001

I am using ArcserveIT 2000 SQL Agent to backup my SQL Server 7.0 db. I perform a full backup (with archive on) every day. ArcserveIT backsup both database and transaction log files. 'Truncate log on chkpt' is set to False in the databases. If I don't use SQL server's backup command with trunc. log on chkpt to true, will the transaction log file ever get trimmed? Does a third party backup utility like ArcserveIT will do something similar to 'backup command' in SQL server 7.
Anyone using ArcserveIT SQL Server 7.0 Agent, please comment.
Thanks

View 2 Replies View Related

Transaction Log Backup

Mar 15, 2004

Good afternoon,

Has anyone ever had an issue with a Transaction Log being backed up but not truncating? We’ve been having trouble with this and now have to truncate the log manually. Any information at this point will be useful. Thank you.

View 2 Replies View Related

When & How To Take Transaction Log Backup.

Oct 3, 2001

Hi,
Can anybody help me in,
When to take backup of transaction log and how to schedule it?

My database is in Full recovery mode with two log files.
I am doing mass insertion, then i am getting the following error
"The log file for database 'nags' is full. Back up the transaction log for the database to free up some log space."
I have taken backup of transaction log. now also it is giving the same error.

Any suggestions how to handle to this situation, to prevent the above error.


Thanks in advance.
Nageswararao.

View 1 Replies View Related

Backup Transaction Log

May 11, 2007

What is the proper way to backup a transaction log for a database whose name contains a space. (I.E. DBname = Database Name). I am trying to execute a command, but it does not like the space in the database name. I have also tried wrapping the name in single quotes and parethesis to no avail.

View 2 Replies View Related

Transaction Log Backup

May 29, 2008

How often do you guys back up your trans logs, every 10mins, 30mins or hourly???

Just curious

View 12 Replies View Related

Transaction Log Backup

Mar 22, 2004

We have a couple of databases in same server, and I set up the job on SQL server agent for backup transactions, a few dbs log backup successful, but a few are always failed, no error message be given, only can check the report --- "Backup can not be performed on database 'Agrdev'. This sub task is ignored ....," I checked all db property and set up , couldn't find out what's going on. Did anyone can help me?

View 8 Replies View Related

Backup Transaction Log

May 11, 2004

Hi,
I have error when i backup the transaction log of master and msdb database.
I think there is an error because the model of this database is simple.
can i modify the model and write full ?

I have another question,
I have a db with 1 db file of 11Go, i have to create secondary files or not.

View 9 Replies View Related

Transaction Log Backup

Mar 25, 2008

Hi,
If i have enabled truncate log on checkpoint option then --

1. Can i take transaction log backup?
2. Can i use point-in-time recovery for the Datbase.

can somebody help me on this. please explain the concept also.


Thanks in advance.
-- Chetan

View 2 Replies View Related

Backup Of Transaction Log

Jun 13, 2006

How to schedule backup of transaction log???

View 2 Replies View Related

HELP! Transaction Log Backup!

Jul 20, 2005

Hello!!I am relatively new to SQL and am having a problem. I have taken overa SQL 2000 db that did not have any backup plans scheduled for it. Icreated a maintenance plan that backs up the database every night andanother plan that backs up the system databases once a week. I ranboth these jobs successfully, but when I created a third plan tobackup the transaction log it fails with the following message:Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE42000] (Error 22029). The step failed. I then executed the followingstatement: sp_grantlogin 'NT AuthoritySystem' which did not work.I still do not understand why I can't backup the log? The databasebacks up fine with no errors so it can't be a permission thing and Icreated the job with a maintenance plan so it has to be in fullrecovery mode. Right? How do I tell? Thank you for all yourresponses!!NS

View 9 Replies View Related

Backup Transaction Log

Jan 4, 2008

Thanks a lot for all the replies.
I'm trying to set up a job to do transaction log backup. I used the maintainence plan to create the job. the code is quite long and not very straight forward, I do not even see the BACKUP LOG .. command. Do people usually use maintainence plan for this purpose or they write their own script and then create the job?

Ping

View 7 Replies View Related

Transaction Log Backup

Nov 16, 2007

Hi,
I have one doubt about Transaction backup. I want to take backup of highly growing database. I will take full backup every 3 days, differential backup every 1 day and transaction backup every 6 hours.

Now my question is-
When I take a transaction backup do it take backup of all transactions which are currently in progress and not committed in database?
OR What are the chances of loosing transactions which are currently running on system while I take a backup?

Thanks in advance.

View 1 Replies View Related

Problems With Transaction Log Backup

Jul 9, 2002

We are experiencing problems with our transaction log backup. The follow error appears when we attempt the backup.

"Backup, CHECKALOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a datbase must be serialized. Reissue the statement after the current backup, CHECKALLOC or the file manipulation operation is completed."

We turned on "Truncate Log on Checkpoint" and created an online backup. Then we turned off "Truncate Log on Checkpoint" and manually saved the transaction log to tape. We thought all was well until the scheduled job to backup the transaction log to tape brought back the same error.

Any suggestions? Thank you!!

View 3 Replies View Related

Transaction Log Backup Failures

Dec 29, 2000

We have a job created by the maintenance job wizard that backs up the transaction logs for all of our databases on an hourly basis. At random intervals, one of the tranaction log backups will fail with the following message in the job history:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

The next scheduled transaction log backup will run fine the next hour. The sqlmaint.exe is present and executable. There are no additional messages in the SQL Server error log or SQL Agent error log. Any ideas what causes this random failure?

View 1 Replies View Related

Transaction Log Backup Failure

Aug 4, 2000

I seem to have a problem with my SQL Server log backup system. First I was receiving alerts that the log file was filling up, so I increased it from 3Mb to 150Mb, but then I got another alert saying the same thing, later on I got an alert saying that the log backup had failed. I then decided to truncate the log and do a full backup after as suggested, however this did not work, I don't know if I am doing something wrong, this is really confusing and frustrating.

View 1 Replies View Related

Transaction Log Backup Failure

Aug 15, 1999

I have scheduled transaction log backups to occur every 15 minutes with database backups every sunday at 1.00AM and differential db backups every day at 1.30AM.

While viewing the job history of the transaction log backups through enterprise manager, I noticed that one transaction log backup had failed at 1.00AM on sunday. The error message was,

"Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "

Please let me know what the error message implies. Will there be any problem if the transaction log backup takes place at the same time as the full database backup or diff. db backup?

Thankyou.
Praveena

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved