We 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
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 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 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
------------
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
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 Related
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
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
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
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 have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
Whats new for SQL Server 2005 SP2 states:-
"The sqllogship application is now supported."
Can anybody confirm that log shipping is not supported in SQL Server 2005 SP1?
Thanks
Hi everyone,
How/when should transaction logs be truncated without breaking Log Shipping?. I'm trying to deal with the scenario of my tlogs increasing their size as I configure log shipping to 5 min. -frequency backups.
Many thanks
Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
View 6 Replies View RelatedI am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies View RelatedHello,
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
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 RelatedHi
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
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 RelatedHi
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
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
I have installed SQL Server 2005 and setup a cluster and log shipping. I decided to perform a planned failover and realized that there are no sp_change_primary_role and sp_change_secondary_role stored procedures. I checked on both the SQL Server 2005 installations and could not find them.
My question is :
"Is it safe to get these stored procedures from a SQL Server 2000 database and run them on the SQL Server 2005 database ??"
"Has anyone come across such missing stored procedures in SQL Server 2005 and know the reason why it is so (so that I can avoid it in the future if it due to a faulty installation) ?"
Cheers
NEVILLE GEORGE
Hello,
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.
Thanks,
Erik
Afternoon,
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.
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 am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View RelatedHi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies View Related