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.
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
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?
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.
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
View 6 Replies View RelatedI 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?
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
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,
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?
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 RelatedI 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 RelatedI 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 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?
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?
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
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?
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
hi, If I want to make one to many relationship, I put the pk of the parent as a fk in the child . like this
customer table (cust_id, fname,lname)
customer_order table(order_id,cust_id,qty,price)
I do understant this, but what about look up table, can I consider cust_id inthe customer_order table as a lookup field to the customer table?
Thanks
Al
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
View 2 Replies View RelatedThis one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
View 2 Replies View Relateddear all,
I have a case.
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
any idea or suggestion will be great....
regards,
-dedys
Hi Folks
anyone could provide steps, links, on how to bring secondary online, with more than one database being log shipped ?
thx
Hello all,
Many people are angry at MS for promising and not delivering on the mirroring feature of SQL 2005.
Does anyone have any idea - officially or unofficially when this feature will be available for reliable use, as we would like to use it.
Thanks,
S.
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
We have a user Training database that we would like to keep updated from our Production database. Users would make different modifications to the Training and Production databases. It is the user updates on the secondary that bother me and I have not seen anything that indicates Log Shipping would fail if new random records are added to both the secondary and primary.
Would Log Shipping be appropriate for this nightly update of Training from Production?
Thanks
In order to move from one data center to another, I am planning to use log shipping.
Lets say,
Current Data center - DC1
Current Server- S1
Current Database- DB1
New Data Center -DC2
New Server -S2
Now, on the New Server S2, I already restored DB1 as DB1 for QA testing. QA has to do some testing before we move from DC1 to DC2. In the mean time, I need to start log shipping from S1 to S2, so that we can fail-over to S2. Now since DB1 database is already present on S2, I can not use the same name. I will have to use some other name (say DB1_New).
I can not wait for QA to finish their testing. I have to start log shipping before that. When QA is done, I will delete the DB1.
So for this log shipping, -
Primary - S1- DB1
Secondary - S2 DB1_New
So when we fail-over, we will have the database name as DB1_New. But all the apps use the name DB1. So I will have to rename the database as DB1.
So want to know, will this renaming the database (DB1_New to DB1) cause any issues?
Anyone had similar experience?
Thanks
Hi
I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?
On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?
Does anyone have any experience of mirroring and is using it in prefence to log shipping?
Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring
For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?
Thanks in advance for any feedback
Robert
Hello All Database Mirroring Experts,
I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:
"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring
session is configured with an additional server instance known as the witness.
If the principal database is lost for any reason after the database is
synchronized and if the mirror server and witness can still communicate
with each other, automatic failover occurs. An automatic failover
causes mirror server to assume the principal role and bring its
database online as the principal database. For more information, see Automatic Failover
[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log
shipping backup location is accessible to the new principal/primary
server, its backup jobs begin to ship log backups to that location. The
database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored.
The secondary servers continue to copy log backups without knowing that
a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx
Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored?
Let me elaborate the situation (if anything I said is incorrect, please correct me )
Here is the time line of the failover happens:
------- tn-1 ---------- tn ---------- tf -------- tn+1 ---------------> t
----------------> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.
Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.
Thanks a lot,
Terence
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]....
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
I have a Subscriber database updated using transactional
replication and want to create a copy for development & testing. The Distribution database is running on the
same 2005 instance as the Subscriber and the Publication database on another
server running SQL Server 2005.
If I use a TSQL script to run the restore, the database is
restored to a usable state but I get the following messages:
Msg 15247, Level 16, State 1, Procedure
sp_MSrepl_check_job_access, Line 112
User does not have permission to perform this action.
The replication agent job [job name] was not removed because
it has a non-standard name; manually remove the job when it is no longer in use.
(I€™ve removed the job name from the message)
RESTORE DATABASE successfully processed 46219 pages in
935.413 seconds (0.404 MB/sec).
It looks like an attempt is being made to restore
replication information too. I note that
the RESTORE statement has a KEEP_REPLICATION options which implies (I would have
thought) that by default the replication information is not be restored.
Incidentally, before implementing replication, backing up
the live database and restoring the backup to a new database took < 7
minutes but running the same process with a Subscriber database backup takes
about 15 minutes.
I€™d be interested to know if anyone has any idea what I€™m
overlooking or doing wrong.
ThanksTim
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.