Log Shipping.. Original Primary In 'restoring' State
Oct 23, 2007
I have a database called PrimaryJunk that is being log shipped to another location, secondary database is secondaryjunk. From PrimaryJunk to SecondaryJunk logs ship and apply fine with no issues. So I figured lets make sure that I am able to perform a role change and swap the roles and that is not working well. My original primary db is stuck in restoring state.
I manually backed up the active transaction log on primary server by performing a transaction log backup with the option 'backup the tail of the log and leave the db in restoring state'
MS site has the same step but mentions NORECOVERY. I am not sure if my step above does that automatically. http://msdn2.microsoft.com/en-us/library/ms191233.aspx
Wonder if thats the reason my original primary db is still in restoring state. any idea?
View 4 Replies
ADVERTISEMENT
Apr 23, 2008
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
View 3 Replies
View Related
Jun 14, 2006
Hi,
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
Thanks.
View 1 Replies
View Related
Sep 26, 2007
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Thanks in advance.
View 3 Replies
View Related
Nov 15, 2006
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
View 2 Replies
View Related
Jan 4, 2008
Hey all!
I have log shipping set up between two servers (s1 and s2). Data is flowing from s1 to s2 and the log shipping monitor is on s1.
I get the following error sporadically
"Transaction Log Backup Job for DB Maintenance Plan 'LogShip DatabaseName" failed..
Error: 60000, Severity: 10, State: 1
So i usually go and start the job manually and it runs fine. But why does this happen and what does the error mean? The above job is scheduled to run every 5mins daily.
Thanks in advance!
THE LADDERS (The Most $100k+ Jobs.)
www.TheLadders.com
View 6 Replies
View Related
Nov 8, 2007
Hi there,
I'm new to this forum but was wondering if you could advise me on getting the DB back to an accessible state. I tried restoring the DB but unfortunately I don't have enough disk space. The error message appeared instantly informing me of the lack of disk space. Now it says its in (Loading state) and I cannot access the DB. Is there anyway I can manually change it back??
View 3 Replies
View Related
Jun 6, 2006
The database in Mirrored server is always in the state of restoring only.
the database in mirrored server is restored with norecovery
then we started mirroring.
the status of mirroring is successfull and is synchronized.
but the Mirrored server is always in the state of restoting
not allowing use to open the database.
could any one know why this problem is comming and give some solution for this problem so that the mirrored database can be accessible opened and queryed to really verfiy that the changes made in principal database is mirrored to Mirrored database.
View 3 Replies
View Related
Oct 5, 2007
I'm testing log shipping locally and had it running between two boxes. I tried to remove log shipping so on the primary I unchecked "Enable this as a primary database in a log shipping configuration" as per another thread. The primary stopped after a bit but the secondary never came out of the (Restoring...) state.
I'm obviously missing a step that needs to be run on the secondary because what I did wouldn't work if site A exploded but I can't figure out what the last step is.
Thanks,
Jeff
View 4 Replies
View Related
Oct 29, 2007
When performing a tail log backup to an online database through SSMS, the database is placed in restoring state. Is there a proper procedure to take the database out of restoring state after the backup is completed?
View 1 Replies
View Related
Nov 21, 2007
Hi guys, right now I am having insufficient hard disc space at the secondary server (Mirror side) due to the large db that I have. Therefore, I get a new HDD on the server (Assuming located at drive G) and would like to shift the current mirroring db to that new drive. Is it possible for me to do that ? Hope able to get any suggestion from here ASAP. Thanks.
Best Regards,
Hans
View 4 Replies
View Related
Jun 22, 2015
I noticed that after a SQL AlwaysOn failover, one of the DB in the secondary replica is stuck in Restoring state. The primary replica shows that it is in a synchronized state. These are the error logs from SSMS. How do I trace the cause of the problem?
Error: 5901, Severity: 16, State: 1.
Nonqualified transactions are being rolled back in database for an AlwaysOn Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required
Error: 18400, Severity: 16, State: 1.
One or more recovery units belonging to database failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required.
View 4 Replies
View Related
Apr 7, 2015
Why do I leave my DB in Restoring State after attaching to the instance? Could not this fire a redo activity on Transaction Log?
Or, How does sql server undo all uncommitted transactions on active vlf during recovery?
View 2 Replies
View Related
Aug 12, 2015
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies
View Related
Aug 28, 2015
I have a server that has several DBs .. some are log shipping(ls_) and others are not. The current TRAN backup script just does a:
select name from sys.databases
where name != 'ls_xxxx'
and name != 'ls_yyyyy'
Is there a way to check a status byte or something so I can remove the hard coded names?
select name from sys.sysdatabase
where status = 'I log ship' -- ;)
I checked several places, but found nothing. Finding other status like RECOVERING et al ... is easy
View 2 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 8, 2015
We are having full backup every day and hourly transactional log backups during the working hours in our production server which is running sql server2008 R2 as a clustered instance. For the Db's under simple recovery model we are having full backups. Now we want to implement transaction log shipping to a remote server in another site. I understand that log shipping involves the restoration of a full backup initially in the remote server and then restoring the transactional log backups which are shipped to it ,on a no recovery basis.
My question is whether we can continue taking the full backups every day in the production sever which is given for offsite storage. Will the full backups taken in the primary server, after the log shipping has been implemented, affect the log backups which are restored into the remote server. Will the chain of log backups which are restored into the secondary server be affected in any manner if a full backup is taken in the primary?
View 6 Replies
View Related
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.
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
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
May 29, 2015
We have several Sql Server 2014 AlwaysOn Availability Groups set up in our test lab and use a combination of SCDPM and Maintenance Plans to back up the databases in them.
Our applications always work well regardless of which of the physical servers are, at any given time, designated the "Primary" for a database. Automatic failover works fine.
There are other applications, such as SQL Server built-in backup and System Center DPM that only work properly when the databases are currently hosted where originally intended.
During the normal course of Windows Updates, etc., machines will occasionally be rebooted in the middle of the night. When they come back up, we would like them to "reclaim" the databases that properly belong to them, in an orderly fashion of course (when the "Synchronization State = Sychronized"). We have lots of these test servers and we do not have staff watching the servers in the middle of the night.
Is there an automatic way to accomplish this?
View 8 Replies
View Related
Apr 23, 2008
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
View 1 Replies
View Related
Apr 25, 2008
We have the following scenario,
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.
Thanks Much
Tejinder
View 6 Replies
View Related
Apr 20, 2015
I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.
Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?
When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?
View 3 Replies
View Related
Jun 11, 2014
I would like to do partitioning one of my client, but that primary database is participating in log shipping.
If I do partitioning on primary, automatically apply those changes on secondary db? if not, what is the way?
Note: I am adding new filegroups and ndf file in different drives on primary database.
View 2 Replies
View Related
May 13, 2007
Hi
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.
Is there any possibility of loosing data ?
Thanks
View 1 Replies
View Related
Aug 3, 2006
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
View 1 Replies
View Related
Jun 8, 2006
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
View 5 Replies
View Related
Jan 9, 2007
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
View 3 Replies
View Related
Jul 23, 2005
from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":http://redmondmag.com/columns/artic...ditorialsID=838"Developers will also pick up the usual new-version enhancements to T-SQL,including error handling, recursive queries, a ROW_NUMBER function, a PIVOTrelational operator and more."ROW_NUMBER???? What possible purpose could that have???
View 5 Replies
View Related
Mar 29, 2007
Hi,
I got an issue with encrypt and decrypt. I encrypted a data, say 'ok', then I decrypt the encrypted data, but what I got is not 'ok'. but if I encrypt a column, then after decryption, those data asr same with the original data in that column.
In my case,
...
create table mytable ( column Password varbinay(128) )
...
create symmetric key with certificate
...
OPEN SYMMETRIC KEY Sym_Key_01
DECRYPTION BY CERTIFICATE Cert;
UPDATE mytable
SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')
select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable
here, I didn't get the value 'ok' but a another wierd word (like a chinese word).
does someone know the reason?
Thanks,
Jone
View 1 Replies
View Related
Apr 24, 2006
When using @original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this :
<UpdateParameters>
<asp:Parameter Type="String" Name="ProductType" />
<asp:Parameter Type="String" Name="OpportunityType" />
<asp:Parameter Type="Double" Name="Value" />
<asp:Parameter Type="String" Name="Probability" />
<asp:Parameter Type="Int32" Name="OpportunityID" />
</UpdateParameters>
And my stored procedure like this :
CREATE procedure dbo.UpdateOpportunity
( @ProductType int, @OpportunityType int, @Value money, @Probability int, @OpportunityID int)
as
update Opportunity set ProductType = @ProductType, OpportunityType = @OpportunityType,[Value] = @Value, Probability = @Probabilitywhere OpportunityID = @OpportunityID
Do I need to change both cases of @OpportunityID in my stored procedure to @original_OpportunityID for it to work?
View 1 Replies
View Related
Dec 7, 2006
Hi All,
How can I find out the original size of the database?
Thanks.
View 5 Replies
View Related
