Recovery :: Log Shipping Transaction Log Backups Not Being Deleted On Primary
Oct 12, 2015
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. Â I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. Â I could simply set up a jog to delete the older files, but that will only mask the issue.
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!
Out of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
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?
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
I am running sql server 2000 on windows 2000. i have my backups set to delete after a week. they are not deleting, can anyone tell me why this is happening?? the backups filled the hard drive.
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.Â
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
I have some questions on backups and log shipping.
Before I get to them, though, the goal:
Back up a SQL 2000 database (~5GB in size) completely every day. Back up its transaction log hourly. Maintain a warm backup DB server using log shipping.
Here is what I have done so far, which has led to my questions:
I have stopped using maintenance plans for backups, feeling that then the process would be less 'black boxy'
I first created two jobs to perform the daily db and hourly tlog backups that saved the backups in files using the naming formats <name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.
Issue 1: doing backups this way means that it's a little difficult for the log shipping jobs to figure out the filenames (esp. the tlog ones), if log shipping is going to use the files generated by the backup jobs.
Issue 2: I thought maybe I'd have log shipping generate its OWN backup files, but would that cause problems with the transaction log? Say the normal tlog backup fires, then fifteen minutes later the shipping tlog backup fires. Would the shipping tlog backup file be missing the transactions that were backed up during the normal log backup?
Issue 3: To try to help with the naming issue, I tried switching the backups from creating new files each time to file devices whose names would be constant. This worked, but since my database is about 5GB in size, that meant that, with expiring backups after 7 days, the database backup device would settle in at about 25GB (I skip weekends) and I'd have to copy or cab-copy-uncab that file over to my warm server every day. That seemed a little inefficient. Does anybody have alternative ideas?
My last question is for general info:
Where does SQL Server keep the information on the backups that exist? Whether I was using individual files or file devices, I was able to go to Database-All Tasks-Restore Database... in Enterprise Manager and it would show me the backups that existed. I imagine these must be stored in a system table somewhere, but I did not see any obvious place to look (no 'sysbackups' table, etc.).
I moved my projects off of the C drive and onto a larger drive a few months back. I discovered recently, like a lot of us have unfortunatly in the past, that we are not pulling a backup of the drive I put the projects on. While going back through some old stuff on the C drive, I deleted a project inside the old Projects folder. This was a mistake because it was a project that is in use. The report still runs fine, but when I went to edit one of the reports in the project, much to my dismay, the project could not be found. I have a txt file of every SQL script that I wrote to create these reports, but am wondering if there is any way to salvage the reports from somewhere on the system so I don;t have to rebuild them?
We have a SQL 2012 server instance that has log shipping set up to another SQL 2012 server to provide a warm standby for a forward facing application. The databases on the primary server occasionally are required to be backed up and restored to a development environment, completely different server. Is there a way to schedule full backups with log shipping enabled?
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
I plan to use Log Shipping on SQL 2000 to have warm standby database. I understand current procedure but have a question wether I can do full normal backups of my database server without screwing up my Log Shipping process? Also can I do transaction log backups as well (separate from the ones used for log shipping)?
How can i make sure that i dont have any data loss in the event of DB crash. I take daily full database backup dump to the disk and every 4 hrs transaction log dump.
What shoud be the backup strategy to get 100 % database from the backup/ to get in point in time receovery.
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
I have a warm standby (secondary server) receiving log shipping files.
The database has 5 files all in the primary filegroup. Two of the files need to be moved from one hard drive to another. Whats the best way / process to accomplish the move and re-establish the log shipping recovery status?
I'm allowed to do Log Shipping from x.contoso.com to y.contoso.com, however there is a requirement to have the same data made available to servers residing on z.contoso.com.
Connectivity between x.contoso.com and z.contoso.com is not permitted, so configuring log shipping with multiple targets is not possible.y.contoso.com and z.contoso.com can talk to each other, so what is the most efficient way to move data from y to z.
I have log shipping enabled on databases(primary and secondary) and works fine. I need to implement TDE on the database. I have experience on implementing TDE on databases which are not used for log-shipping.steps needed to setup TDE which are involved with log-shipping.
Lets say I have a single instance SQL Server on a 2 node cluster with node names Server X and Server Y. These are part of Domain A.
SQL Agent Account runs under service account - DomainASQLAct.
Now I have Server Z which is a standalone SQL Instance on Domain B and there is no trust between domain A and B, not even one way trust.
Even though I dont have trust, I need to configure log shipping for the SQL Instance which is clustered under Domain A to a standalone box in Domain B.
Microsoft recommends Local Account(SQL Agent) to be used for Cross Domain Log Shipping. Also when there is no trust, MSFT recommends to use the same login name and password, however as my setup includes a cluster and cluster cannot have local account I'm not sure what will be the best strategy to follow here.
I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following
RESTORE DATABASE database_name WITH RECOVERY
but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:  Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. Restore the server with Oct 1 full backup with NORECOVERY option.Try to restore to the point at Oct 5 12:00, with the transaction log.  But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
Knifflige Frage zu Datenbanken:Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID.Soweit ok.Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalischgelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHTangegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz nichtgelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung drin.Dann funktioniert der PK nicht mehr, denn es können ja durchaus mehrereDatensätze mit gleicher MAID und GMID vorhanden sein. Aufnehmen vonGELOESCHT in den PK geht theoretisch, ist aber gefährlich (daFließkommafeld, und wer weiß schon, ob 0 immer gleich 0 ist...).Hat jemand eine Idee?SWE@KP
I have two databases on a Production Server that I want to Log Ship to a Test Server. According to the sys.master files the physical File Location is on an E drive. Early attempts at Log Shipping these two files error'd out due to space issues on the E drive (one Log Shipped and then one err'd out). I was subsequently informed from the server group that they would prefer that I Log Ship these two database files over to the M Drive where more space is available. In fact, they modified the Server Properties / Database Settings / Database Default Locations (for Data and Log) to the larger M drive (I'm not really sure why they just don't increase the E drive space but there is proabably a good reason).
Okay, so now my problems have been solved. Easy enough. Now I deleted the successful Log Shipped database and started from scratch. However, as before, one db restored and one failed (due to space issues). Apparently, both db are pointing towards the E drive. How is that possible?Â
So here I am with one successful database and the normal sys databases pointing to the E drive. What is the best way of approaching this  move to the larger and preferred M drive?
Setup log shipping from an Australian SQL2008R2 server to a UK SQL2012 server. With the time involved to copy the large backup file across a WAN link, we end up with around 2 days of log backups since the full backup.
- The Full Backup is restored to the UK Server to create a new database in norecovery mode. - The first log backup file is manually copied to the UK server and restored. - Start the Log Ship copy SQL Agent job - starts copying files prior the full backup on the Australian server. Copies 1 Log backup file and then fails. - Manually copy the next Log backup file to the UL server. - Start the Log Ship Restore SQL Agent job and runs successfully and restores the Log file. - Start the Log Ship copy SQL Agent job - AGAIN it starts trying to copy files prior to the Full Backup.  IS this correct behaviour? I thought it should only copy Log files since the full backup.Â
