Co-existence Of Log-Shipping And A Independent Backup Strategy
Dec 19, 2007
We have a critical Production database on which we want to setup Log-Shipping. We have also purchased the Symantec NetBackup utility for taking Backup to Tape Drives.
We Know that there are some inherent problems with using Log-Shipping and a Backup Strategy together, and thus we were finding out various ways in which we can run both in tandem.
One of them was taking the Symantec Log backups with Copy-Only option.
The main problem with this is that the Symantec Backups becomes dependent on the Log-Shipping Backups and also most of their Log-Backups become useless. Also, we are not sure whether the utility allows Copy-Only backups
The second alternative was to disable the Backup job of Log-Shipping on the Primary Server and to use the Log-Backups done by the Symantec utility for performing the Restores on the Secondary.
Thus, if Log-Shipping is scheduled to run say every 4 hrs, and the Symantec Log-backup happens every 1 hour, then at an interval of every 4 hrs, the Restore Job on the secondary will pick up the 4 backups done by the Symantec Backup utility and Restore each one of them in sequential manner.
But, I guess it is not easy to have a manual Restore Policy in place.
I was really banking on this solution until I found that the Restore Job of the Log-Shipping setup is dependent on the Filename of the Transaction log file which the systems generates automatically, and it won€™t be easy to create a customized Restore Job on the secondary server which takes in all the Log Backups generated by the Symantec Backup utility and Restore the Secondary database.
Have any one of you ever face this issue? Would like to know what is the best way to keep both of them running together.
View 3 Replies
ADVERTISEMENT
Oct 2, 2014
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
View 2 Replies
View Related
Jul 18, 2000
Hi all,
Pardon me for asking a question that I know has been asked before. I need to develop a backup strategy for our SQL Server and I am looking for any help that anyone can offer including recommending good books for reading.
Thanks in advance,
Faustina
View 1 Replies
View Related
Oct 18, 2000
In SQL Server 6.5, Is it generally better to dump the
transaction log first, then the database or to dump
the database and then run a dump 'tranlog with truncate
only' option?
Or, is this more a matter of personal choice?
Toni
View 1 Replies
View Related
Jul 23, 2005
I've recently inherited a position where I am responsible for the well-beingof some DBs.2 (much) more important than others.The current recovery model, from what I can tell, is to do a full db/logbackup overnight.This .bak file is then written to tape as well as saved on the disk for 2days.Both these dbs are used fairly extensively 8-5pm and losing data would notbe good.The db sizes are approx 5gb and 3gb.This doesn't seem like the ideal situation to me. Everything I read tellsme... full backup periodically, differential nightly and transaction hourly.Agreed?If so then I have 2 questions:1. Is the best way to do this via a maintenance plan or by scripting andscheduling?2. What, if any, overhead can be expected with regular transaction backupsduring work hours?A bit of a pointer to #1 would be appreciated also.Thanks.
View 4 Replies
View Related
May 23, 2007
We are currently doing daily full backup of system & custom databases since database size is small. Is that good idea ? or better option would be weekly full & daily incrementatl ?
Do we need to do any special backup on system databases or transactional logs ?
Please advice
View 30 Replies
View Related
Nov 1, 2007
Hi
I have concern about an sql server. The server has the operating system and sql server installed locally. The databases and transaction log files is stored on SAN. We used to have the database backup and transaction log backups stored locally on the server. We tape the database backup and transaction logs every 24h. If we lose the san and the server then we are stucked with no backup easily accessible. And on tape we loose of 24h data. We decided to put up stand alone server with no connection to the san and dump the backups file on this server. We also put a secondary server sql I case of emergency, to test backups and We are looking at the getting a mirroring or log shipping solution but we are not there yet, next year€™s budget. We still using some old server left from migrating to virtualization.
Then I read the €œPractical Troubleshooting The Database Engine book€? best practice not to avoid net work drive backups. Stuck aging. Back with the backup to local drive and robocopy them? Keep them on the network drive, start using? MIRROR TO in the BACKUP DATABASE? Today we are using the Backup Database Task in the SSIS.
Advice?
Regards
Johan
View 4 Replies
View Related
May 21, 2007
I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.
Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.
*** TASK 1 ***
Backup transaction log
/* This script backs up the DSS database transaction log to disk, overwriting any
previous backup
*/
BACKUP LOG [DSS]
TO DISK = N'g:mssqlackuplogdss_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
*** TASK 2 ***
/* This script shrinks the DSS database transaction log file
*/
BACKUP LOG [DSS] with truncate_only
dbcc shrinkfile(DSS_log)
**** TASK 3 ****
/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:mssqlackupdatabaseDSS.bak'
WITH FILE = 1
GO
*** TASK 4 ***
Update statistics on the DSS database
View 3 Replies
View Related
Jun 15, 2007
This may seem like a silly question, but has anyone ever heard of a DBA or an Engineer deciding to not back up databases inside EM, and only relying on the RAID or third party software for redundancy?
SBS 2003 R2
SQL 2000
Veritas 8.6 open file agent, SQL agent, Exchange agent
Thanks for any input,
Rich
View 3 Replies
View Related
Mar 2, 2007
Hi,
In my current organisation they are using SQL Server.
They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.
However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.
Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?
My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.
Any suggestions please ?
Constantijn Enders
View 5 Replies
View Related
May 4, 1999
Hi All,
Can any one help me with this..
I've a critical application that can't be stopped for a second.
I'd like to have an implementation that uses 2 sql 6.5 servers one as standby and
which is ready and up to date to take place and run instead of the master
server when it's down and when the master is back to work it's updated with the
data entered to the standby.
This process must be automatically to maximum extent.
Thanks
Mohamed
View 1 Replies
View Related
Jun 14, 2007
My maintenance plans are starting to acting weird. I'm building a custom script to manage the database backups on my server, but curious if anybody has some sample work that will allow me to avoid re-inventing the wheel.
A couple of primary constraints:
I want to do a full backup daily (and only retain 1 day of full backups)
Transaction Log backups every 20 minutes
I'd like to loop through the databases on the server automatically to make this a little more flexible.
You have anything you'd like to share? Or, bits of knowledge worth sharing?
Please advise,
alex8675
View 3 Replies
View Related
Sep 27, 2006
Hello.
I have only ever been required to take a full back up of my main prod database every night.
Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.
I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:
Full backup @ 7pm
Backup tran log every hour after that starting @ 7am (as there are no changes overnight)
How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it? Basically I need to know what to do so it doesn't get too big!
Thanks
View 1 Replies
View Related
Sep 15, 2007
When attempting to backup a database using Management Studio, I receive a ""Cannot verify the esistence of the backup file location. Do you want to use the backup file location anyway?" yes/no messsage afer entering a file directory which exists. When entering a new directory, when I select "yes," a file locaiton will not be created, however, the backup operation continues to success. Originally, I had configured the sql services to execute under a user account with domain admin and administrative privledges, and I vverified the folder's security config permited write access. I reinstalled and now al SQL services are executing as "Local System" (or "Network Service") with same problem.
Any recommendations as to how to correct what I am doing wrong? (Thanks).
Jamie
__________________________
SQL Server Enterprise 2005 running w/all patches on Windows Server 2003 Server.
View 3 Replies
View Related
Aug 16, 2006
I tried searching, but it appears the search is still broken.Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).Backup our DB weekly w/ transaction logs.I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.Along with that I have some optimizations and shrinks I run. Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.So. Here in lay the questions.The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).So, I sorta get that..I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?I guess I'm a bit befuddled here.
View 14 Replies
View Related
Jun 9, 2008
Hi,
I would like to have guide for backup
estore strategy for production DB..if i have to apply for high availability of production DB..
consider scenario ..Full backup every week, differential backups every 6 hours, and logs every fifteen minutes.
suppose my DB is crashed at 8:14 AM and my last logs were backed up at 8 AM then by looking above startegy then i lost my DB from 8 am to 8:14 am???? if not then where it shud find???
also looking above scenario by taking backup for such high frequency is it full my disk?? guide me pls...
T.I.A
View 4 Replies
View Related
Jul 20, 2005
Hi,Could anyone tell me the backup strategy for a 1000GB database?Thank you!Peter Wang*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jun 9, 2008
Hi
I have one query regarding backup
ecovery startegy
hi
I have Db with simple recovery model.. recently i updated my column without where clause now i need to recover all my original state of that column..i just have idea @ set DB recovery model to Full then take full back up and then take differantial back up and then take transactional log back up and restore it as full , diff and then most recent transactional log back up...but still i didnt get original state of that table column...
please guide me
T.I.A
View 13 Replies
View Related
Jan 24, 2007
Real World: Backup Strategy and implementation, how?
A quote:
€œReal World:
Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:
//servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups.
//servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups.
//servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups.
//servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.
Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.€?
I understand these concepts, however in €˜the real world€™ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3€™s rotation.
Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.
I would like full details on this please, as I need to get my head around it.
Thanks
Neil
View 6 Replies
View Related
May 11, 2006
We currently use a split-mirror backup strategy for our Sybase database, which has a "quiesce database" command to suspend all transactions. By quiescing the database before splitting the mirror, we suspend all transactions to ensure we get a stable backup of the environment. It works very well for us and I'm trying to understand how we could implement this with our SQL Server 2005 DB.
(I'm aware of SQL Server mirroring and that there are other ways of possibly backing up the DB. In this post however, I'm only interested in how I would make the split-mirror strategy work if I wanted to pursue it. I'm trying to avoid paying for software that uses the VDI as it's quite costly.)
Can someone help me with how I would accomplish a split-mirror backup strategy in SQL Server 2005 (without using a vendor's software that uses the VDI)? I have to imagine there's something similar to the "quiesce database" command in SQL Server...
I appreciate the help!
View 3 Replies
View Related
Jun 1, 2007
I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?
View 3 Replies
View Related
Dec 5, 2007
Hi all,
We have log shipping set up for some our databases.
So, my question is, Can i take a FULL BACKUP of databases which are involved in log shiping at primary.
--> Please give your comments on this.
Thanks.
View 4 Replies
View Related
Apr 9, 2008
Hello,
I have an SQL 2005 maintenance plan that backups up the t-logs of my production db every 15 minutes. I want to begin log shipping over to a warm-standby secondary server. The network share, log shipping folder is not the same folder location as my maintenance plan folder. My question is, do I need to disable the maintenance plan for tlog backups in order for log shipping to be sucessful? Or will the dual backups to seperate backup folder locations cause a failure in the secondary server restore process?
I suspect the latter but wanted a second opinion.
Thanks.
View 4 Replies
View Related
May 29, 2008
Does anyone know if you can delete the initial backup file created (xxx_init.bak) on the secondary server after setting up Log Shipping?
View 1 Replies
View Related
Jan 17, 2008
Hi everyone,
I'm trying to configure log shipping on a 2005 sql server. I follow the wizard's instructions (see http://technet.microsoft.com/en-us/library/ms190640.aspx) and everything looks right except for the backup job that somehow is not being created on the primary server. Secondary server contains copy, restore and alert jobs.
No errors reported on job monitor.
Any ideas?
Regards,
View 9 Replies
View Related
Jul 14, 2007
Hi!
i have a question regarding tail log backup.
I have set up logshipping between primary and secondary servers. Now when i want to fail-over to secondary, I am planning to take the tail log backup on primary
backup log primarydb
to disk=N'..',
with norecovery
and then I will restore this tail log backup to secondary
restore log secondarydb
from disk=N'',
with recovery
Will there be any issue with this approach?
Will I be able to recovery the primarydb if i want to, in future? Taking tail log backup doesnt cause any issues? Or do i need to take the full backup of primary before I take tail log backup..in case if I can not recover the primary db after taking the log backup?
How big the tail log backup can be? Will it be of similar size of other normal transactional log backups.?
Any help would be appreciated.
Thanks
View 3 Replies
View Related
Dec 8, 2006
Hey all-
I am working to establish a backup plan for SQL Server 2000. We are currently doing log shipping between servers.
A full backup once a week
transaction log backup every hour
We run a bulk deletion (delete * from table where date < ninetydays) which deletes 22,000 rows approximately. We are running in Full Recovery mode.
I wonder if there was anyway to improve my backup plan, preferably minimizing logging when the deletion takes place but still backup the data with log shipping.
Let me know if any of you have ideas. Thanks
Nate
View 1 Replies
View Related
Feb 1, 2008
Hi
Im planning to setup sqlserver 2005 log shipping between two servers.The size of the database is around 50GB and we are taking transaction log backup and shipping at 30mins interval.
Suppose if the backup job couldnt take the transaction log backup with in the specified interval ie 30mins,then how does the copy and restore job works. Will it give an error saying tht the transaction log could nt be copied and restored ???
How to deal with this kind of situations where the backup is taking more time than the threshold time limit and ur copy,restore fails. Is there a way to handle this kind of situation in logshipping Sqlserver 2005.
and how does it affect the Secondary database. ??
Please help me out !!
Thnkx in advance
Regards
Arvind L
View 4 Replies
View Related
May 29, 2007
I have a 20 GB SS2005 database that I would like to be replicated to a dev server for testing purposes. I might have some issues with the firewall blocking ports. I haven't read up on the specifics of how log shipping works yet, but I assume that is the best approach. A daily transfer would suffice.
A few options I am considering:
1. use the built in stuff, but I'm not sure it will work with our security settings, and if the trans logs are large it might be strained.
2. writing a small custom app to zip up the log file then ftp them down to the dev server. This could run nightly.
3. some third party util, such as FolderShare could transfer the log files
Any comments or suggestions?
View 4 Replies
View Related
Feb 13, 2008
I have setup Log shipping between two SQL 2005 servers, and everything seems to be working well. The files are transferring and restoring correctly.
My question is whether I need to add any backup procedures for the secondary server to prevent the secondary server's log file size from growing continuously. Should I be doing a transaction log backup on the secondary server? Or will that break the Log chain?
If it makes a difference, the secondary server is in Standby mode after applying the logs.
Any advice would be appreciated,
~ Michelle
View 4 Replies
View Related
May 14, 2007
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
View 1 Replies
View Related
Jun 23, 2007
I'm experiencing a weird problem with log shipping in SQL 2005.
I've setup Log Shipping for a production database between two sites. The standby database is being updated correctly and everything seems to be working as expected but for one detail: the name of the transaction log backups are generated with an UTC timestamp instead of my local timezone.
The the data below extracted from the backup history:
2007-06-23 17:30:00.000 D:BackupDatabasesmydbmydb_20070623073000.trn
2007-06-23 17:15:00.000 D:BackupDatabasesmydbmydb_20070623071500.trn
2007-06-23 17:00:00.000 D:BackupDatabasesmydbmydb_20070623070000.trn
2007-06-23 16:45:00.000 D:BackupDatabasesmydbmydb_20070623064500.trn
My timezone here is GMT+10.
Although it's not affecting Log Shipping, it's very confusing as the full backups have a timestamp in the local timezone!
Has anyone seen experienced something similar to this? Please see below my SQL details:
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 English (United States)
4 Platform NULL NT AMD64
5 Comments NULL NT AMD64
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2005.090.3042.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 199360512 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 4 f
18 ProcessorType 8664 NULL
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID NULL NULL
Thanks,
André
View 3 Replies
View Related
Sep 25, 2015
I want to redirect the logshipping primary backup folder to another drive, how to change the configurations steps to move the primary logship folder to another location within the same server!
View 2 Replies
View Related
