hi,
i have log shipping between 2 servers on sql server 2000. the log shipping failed because of some problem with transaction log backup files. somehow the files are missing and it is not able to apply transaction log. can someone please tell me how can i restore the log shipping between 2 servers. please help asap..need to fix it in couple hours
------------
Robbie at 3/7/2002 10:13:00 AM
Great news but how??
------------
mmwops at 3/7/2002 9:09:32 AM
can be done
------------
Robbie at 3/7/2002 7:03:16 AM
Does anyone know if Log Shipping be done using SQL Server 2000 standard edition or does this have to be Enterprise??
Many Thanks
Robbie
Hi,
I have maintanance plan for log shipping.it was working fine for last 2 months.But due to some reason I have to restart the server and after that my log shipping job was failed.When I start the job it is showing error "Executed as user: OKS-MARAdministrator. Invalid drive specification 0 File(s) copied. Process Exit Code 4. The step failed".This error is coming when I copy the bakup database file to another server.I have checked all the mapping and sharing option.Also I have nt changed anything in the two servers.But still then the error is coming.
Can you please help me out to shot out the problem?
Thanks in advance.
Sunanda
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
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
Thanks.
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
I am implementing log shipping in sql server 2000.The logs are been backed up on the source server but they are not been restored on the destination server.What could be the possible reason for this?
Thanks
Madhukar
Hello
I want make log shipping between a Sql Server 2000 64 bits itanium (primary server) and a SQL Server 2005 32 bits.
Is it possible or I'll have got any problem?
Thanks you
I am currently running a Standard Edition of SQL 2000 on a WIndows NT4.0 Platform with service pack 6a installed. I want to implement Simple Log Shipping on my Standby Server as a means of improving my ability to recover from a contingency.
The Setup is as follows:
1. Both the SQL Server Service and SQLAGENT Service on the Primary Server Start with a Domain Admin Account
2. Both the Primary and Standby Server have mixed authentication
3. The current security context of the User (Which happens to be a Domain Admin) is used for the Link Server Relationship.
4. The Directory on the Standby Server where the Transaction Log gets backed up to has permissions of "Everyone" - Full Control" as does the log file itself.
The error I am receiving is as follows:
Executed as user: xxxinvbsg. Backing up Northwind to
DISK=S5ad$yyyNorthwind_20020130_1540_LOG.BAK [SQLSTATE 01000] (Message 0)Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 1 pages in 0.264 seconds (0.007 MB/sec). [SQLSTATE 01000] (Message 3014) Execute [s5a].master.dbo.sp_ApplyStandByLog 'Northwind',N'S5ad$yyyNorthwind_20020130_1540_LOG .BAK',N'd:yyyUNDO_Northwind.DAT'
[SQLSTATE 01000] (Message 0) Executing RESTORE LOG Northwind FROM
DISK='S5ad$yyyNorthwind_20020130_1540_LOG.BAK' WITH STANDBY='d:yyyUNDO_Northwind.DAT [SQLSTATE 01000] (Message 0) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) Cannot open backup device 'S5ad$yyyNorthwind_20020130_1540_LOG.BAK'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000]
(Error 3201). The step failed.
Any help on this will be appreciated.
Thanks
Brad
I'm attempting to configure log shipping on 2 servers with SQL Server 2000 sp4 Enterprise Edition. I keep getting the following error when the job attemtps to execute.
Error 14261: The specified primary_server_name.primary_database_name('C')
already exists.
I have tried this KB http://support.microsoft.com/kb/298743 and i got the same error again!
Please HELP!
Hi,
I am now trying to configure Log Shipping in my compeny but I have encountered some difficulties. The destination site and the source site are far from each other and the transaction logs are too big, this situation causes an huge overload on our private network (we use WAN which is complitly disconnected from the internet). Is there any way to compress the transacion logs with zip or rar and combine it in the log shipping mechanizem?
Is there another way for making this files smaller?
I would be greatful for any ideas in this matter.
Thanks in advance,
Zvi Gilinsky
Hi,In the MS SQL 2000 Server, if the log shipping is set up right, does thatmean data in primary server changed will mapped to secondary server lateron?Thanks.Jason
View 4 Replies View RelatedWe have an environment where 3 SQL 2000 servers exist; one production servers and two standby servers that receive logs via log shipping. Everything works fine and it is stable. My client wants to get a new 64 bit server and run SQL 2005 64 bit version for the production box. Can we still do log shipping with the two remaining SQL 2000 boxes or do they all have to be on the same version and bit version?
Thanks
Bill
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
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
I have 2 SQL Servers. One of them is a production server and the other I want to have as a backup to the production server.
It is my understanding that Log Shipping requires the use of MSSQL Server Enterprise. Does the Enterprise version of SQL Server have to be on both servers? Or can it be just one one server?
If only one server, does it have to be on the subscriber or publisher?
This question is for concern towards proper licensing.
hi all,
For data ware house project, the reporting team needs to know the delta changes to the master database.
one way we were thinking was to use log shipping and run reports / ETL off the secondary server. But the team needs to know which records got changed and i was thinking of adding timestamp columns to the necessary tables (only on the secondary database schema) and that way we can track the changes.
But from my research, it seems like secondary database needs to have similar schema as promary database.
Is log shipping, can my secondary db have a bit different schema? if so how to do it?
If not, how to accomplish the above secanrio, with out adding new columns (if possible) in the master database and with low over head.
thanks
lucy
Hi,
We have SQL Server 2005 configured with mirroring to protect from physical errors. We also have a need for an (out of sync is ok) reporting server and we'd like to reduce our downtime in the event of a logical error.
The primary database is already being backed up (full and t-logs) to a shared network drive.
Can I implement the second half of log shipping (i.e. the stuff you do to the secondary) so that I don't have to change the current backup schedules on the primary server?
Specifically in the list of sp's below, can I start halfway down at sp_add_log_shipping_secondary_primary (Transact-SQL) ?? Without having to run the primary sp's?
sp_add_log_shipping_primary_database (Transact-SQL)
sp_add_jobschedule (Transact-SQL)
sp_add_log_shipping_alert_job (Transact-SQL)
sp_add_log_shipping_secondary_primary (Transact-SQL)
sp_add_log_shipping_secondary_database (Transact-SQL)
sp_add_log_shipping_primary_secondary (Transact-SQL)
Or do I have to ditch my current backup maintenance plans on the primary and start again?
Thanks
Ed
I have had log shipping set up for a month at a remote site. Created another server to failover to here locally that utilizes 512K line. Microsoft has a tab on the maint plan to add another server to the log shipping plan. Shut down all log shipping, moved full backup back here locally and created new database. Used tab to add new server to log shipping server and setup plan using this database. When log shipping starts up for this new server it fails. Error shows it is looking for the first log file that was sent over a month ago. Am I correct in assuming that the only way you can really add another log server is to do it at the same time? If so it's kind of stupid to have the tab to add the server. My log files are pushed over every 5 minutes, and I remove them after 1 week.
View 3 Replies View RelatedDoes anyone know if there are any minimal hardware requirements for the Monitoring server for SQL 2005 Log Shipping?
View 1 Replies View RelatedHi all,
I need some advice on this scenario.
I have to set a Disaster Recovery (DR) server from my production Server( Most probably, i will be using Database Mirroring for my DR Server). Right now, the production is log shipped to another server for reporting every 15 minutes.
So, for setting up Database Mirroring onto my DR Server, i can take Full Backup from production without damaging log Shipping.
But, for setting up Database Mirroring, we need to restore most recent Transaction log backup with norecovery ( Correct me if i am wrong).
But, I cannot take log back because the prodcution database is log shipped for Reporting needs. If taken, log chain breaks.
Can anyone please suggest some work around for setting up the DR Server with minimal downtime.
Thanks for all your help.
I have 4 sql 2000 servers. One is a the primary, one is a local stand-by, one a remote standby and one is a training server. WHen configuring which server should monitor log shipping I was torn between the local standby and the training. The training server which only has a few users if ever, might be used for some DTS tasks in the future for some B to B stuff. Based upon this I am leaning on the local standby to monitor. Anyone have an experience or input?
Thanks!
(No I can't buy any more servers)
I have to set up log shipping from Prod server "A" to 2 different DR servers ("B" and "C")...What do i have to do differently (or additional) using the GUI (ie not using Tsql Scripts) to accomplish this, in addition to the steps that are done to log ship to just one DR server?
View 0 Replies View RelatedI 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.
What are the steps needed to setup TDE which are involved with log-shipping.
Dear,
Are possible build a solution with SQL Server 2005 Cluster and Database Mirroring? I have the following scenario:
1) Site A
SQL Server 2005 with 2 nodes and active/passive
2) Site B
SQL Server 2005 for Disater Recovery with Log Shipping.
Are possivel change Log Shipping by the Database Mirroring? If is possible, are better:
a) Synchronous(with or without witness)
b) Asynchronous
Thanks a lot! Sandro.
sgpcosta@hotmail.com
I am following the instructions laid out in the MSDN article
http://msdn2.microsoft.com/en-us/library/ms190640.aspx
and am finding results that are confusing me. The primary server's database's log-shipping settings is set to copy the Transaction Log backups to a UNC share on the secondary server. The secondary server does not have an instance of the backup database yet, so I specify "Yes, generate a full backup of the primary database and restore it into...." in the Initialize Secondary Database tab.
Under the Copy Files tab, i make the secondary server additionally copy the original log file location (e.g. \secondarylogship - d:logship) to another extra location (e.g. d:logship
estore).
When the processed is kicked start, it appears to report everything is successful. However the primary database properties show that "Enable this as a primary database in a log shipping configuration" is checked off. There is no SQL Server Agent job to back up its Transaction logs. Over at the secondary server, there are the three expected Copy, Restore, and Alert SQL Server Agent jobs. The resultant database is in a perpetual Restoring... state and nothing can be done to it.
I also noticed the Restore job ran before the Copy job when i checked the Job History of those jobs. I would have thought Copy should be done before attempting a Restore.
Can anybody explain what behaviour is being observed here? Thanks.
I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.
View 3 Replies View RelatedI made B server which get logs from primary server A as a secondary server in the log shipping solution.
it always shows RESTORING in B server, it seems not to accessible.
my question is <if A failed down , how to revoke the B server as the primary one>
I am getting the following error " The specified servername does not exist" when I run the Log Shipping procedure. There is only a Primary server running Win Server 2003 withe Service pack 2 and SQL Server 2005 with service pack 2 and one secondary server runnung Win Server 2003 R2 with service pack 2 and SQL Server 2005 with service pack 2. All permission setting look ok . Can anyone help
View 4 Replies View RelatedI 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?
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
i'm writing below this code..
exec msdb..sp_send_dbmail
@profile_name = 'local',@recipients='chintu@www.com'
, @subject ='Log Shipping Job succeeded'
, @QUERY = 'exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N'
I want Log shipping Status Through Mail. Last back up transaction in the day and last restoration trn file in the day...
we are about to start a logshipping with the secondary server in read only mode.
I just wanted to know the disadvantages of using a read only on the secondary server.
I mean whether the transactional logs will be restored periodically or it won't be restored till users are connected on the secondary server.