SImple Log Shipping In SQL 2000
Feb 1, 2002
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
View 1 Replies
ADVERTISEMENT
Dec 18, 2007
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.
View 3 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
Jan 20, 2005
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
View 9 Replies
View Related
Mar 7, 2002
------------
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
View 1 Replies
View Related
Jan 25, 2007
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!
View 3 Replies
View Related
Mar 25, 2004
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
View 3 Replies
View Related
Jul 23, 2005
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
May 28, 2008
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
View 4 Replies
View Related
Sep 19, 2007
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
View 4 Replies
View Related
Aug 15, 2006
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
View 5 Replies
View Related
Dec 8, 2006
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
View 1 Replies
View Related
Mar 21, 2006
Hello,I just started with ms sql. I need to insert data from some textboxes into ms sql 2000 database.I can't get it work.I'm trying this:"INSERT INTO tblUplate (ID_Uplata, JMB, IDOsnovaUplate, Iznos, Datum) Values('', ' + txtJMBUplate.Text + ', ' + ddVrstaPrihoda.SelectedItem + ', ' + txtIznos.Text + ', ' + DateTimePicker1.Value + ')"Is this wrong? Should I use + or & (like I used to use with MS Access)?Thanks
View 1 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
Nov 28, 2006
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
View 4 Replies
View Related
Feb 1, 2006
creating a simple package and cant get it to run below is the error. when i check the source 2k db its there see below...
i am clue free of the issus...
SSIS error message
Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=0 description='FK_MoreBDRInformation_dbo_BatchDataReports' is not a constraint. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
source Server DB sysobjects table record
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
FK_MoreBDRInformation_dbo_BatchDataReports 1618573300 F 1 0 0 0 0 86043838 2006-01-31 12:35:41.530 0 0 0 F 0 11 0 2006-01-31 12:35:41.530 0 0 0 0 0 0 0
View 1 Replies
View Related
May 19, 2007
I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.
View 7 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
Jun 8, 2006
I have been trying to export an sql2000 database to sql2005 with no luck. I continuosly get the error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have looked upon all support material and have installed the latest sql2005 service pack, but this has not fixed the problem. a simple query as:
select * from [transaction] ta,transactionentry tr
where tr.transactionnumber=ta.transactionnumber and
glacctid=6 and ta.[time]> '3/01/2006'
would produce this fatal error.
the current version I am using is:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
This problem is happening on some of the most important tables in the database.
the error log contains the following:
2006-06-06 11:43:32.59 spid54 ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0001.txt
2006-06-06 11:43:32.59 spid54 SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-06-06 11:43:32.59 spid54 * *******************************************************************************
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * BEGIN STACK DUMP:
2006-06-06 11:43:32.59 spid54 * 06/06/06 11:43:32 spid 54
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * Exception Address = 0136B4F0 Module(sqlservr+0036B4F0)
2006-06-06 11:43:32.59 spid54 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-06-06 11:43:32.59 spid54 * Access Violation occurred writing address FFFFFFFC
2006-06-06 11:43:32.59 spid54 * Input Buffer 260 bytes -
2006-06-06 11:43:32.59 spid54 * select * from [transaction] tr,transactionentry te where tr.
2006-06-06 11:43:32.59 spid54 * transactionnumber = te.transactionnumber and glacctid = 6
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * MODULE BASE END SIZE
2006-06-06 11:43:32.59 spid54 * sqlservr 01000000 02BA7FFF 01ba8000
2006-06-06 11:43:32.59 spid54 * ntdll 7C800000 7C8BFFFF 000c0000
2006-06-06 11:43:32.59 spid54 * kernel32 77E40000 77F41FFF 00102000
2006-06-06 11:43:32.59 spid54 * MSVCR80 78130000 781CAFFF 0009b000
2006-06-06 11:43:32.59 spid54 * msvcrt 77BA0000 77BF9FFF 0005a000
I have followed instructions on some support issues posted but none have helped. If anyone has any solution to this problem PLEASE HELP!!!!.
Thanks.
View 7 Replies
View Related
Dec 22, 2005
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.
View 12 Replies
View Related
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
Apr 16, 2007
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
View 1 Replies
View Related
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
Jun 21, 2002
Could somebody please guide me on how best to Implement SQL Server Log Shipping. I have gone through BOL without luck. Thanks.
View 2 Replies
View Related
Aug 1, 2000
I need to create a RO copy of a production DB owned by an outside company. We are connectd via a WAN link, but cannot use replication. They are proposing using an initial load via tape, and sending us a text file nightly with the days changes to the DB. We will then need to load that data using BCP, DTS or some other method. Does any one have any ideas on using log shipping instead of the text file. It would only be practical to get a fresh load of the entire DB once a quarter or once a month at most. It is a 40+ GB database and we are expecting 100 to 200 MB of logs per night. For business reasons, we are limited to some type of file transfer mechanism for the data transfer, and cannot really change their backup schedule which is nightly fullbackups and tlogs every 30 minutes.
View 1 Replies
View Related
Nov 28, 2000
Can anyone point out me , What does Log shipping mean ?
Is it backup of Log file ? or moving backup logs to any other destination ?
Thanks
Karthik
View 2 Replies
View Related
Mar 27, 2002
Hi all,
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
Any help is highly appreciated.
thanks in advance.
Minesh.
View 1 Replies
View Related
Sep 6, 2000
This might end up being fairly lengthy...I'm in the midst of implementing log shipping as a "warm stand-by" solution at my company. All the components appear to be in place: I'm using cmd shell to copy the backup device file to a remote server and then execute a RESTORE stored procedure on the remote server.
The copy and restore work just fine. The problem I'm having is with the transaction log dumps and restores.
We normally dump transaction logs (and then truncate) every hour. With the log shipping being implemented, we're going to want to do separate log dumps every ten or fifteen minutes, copy that dump over to the remote server, and then apply that log to the database.
Here's the question: for the log ship portion, I don't truncate the log. But after the "normal" log dump occurs, things get tossed out of whack. When you try to apply a log, I get the message "database has not been rolled forward enough....".
Has anyone encountered this type of issue and if so, how did you work around it? I'm assuming it's a simple of issue of certain options you set on your dumps and scheduling....
I'd appreciate any help....
Thanks!!!
View 3 Replies
View Related
Sep 24, 2002
Does anyone know if Log Shipping in SQL 2000 Ent also ships over database schema changes?
View 2 Replies
View Related
Oct 14, 2002
We are considering implementing log shipping. Do the sql server logs keep track of the logs that are shipped and applied through log shipping? Or is there some other way to make sure that all logs have been shipped and applied?
View 1 Replies
View Related
Jul 25, 2004
I have been successful in getting log shipping working but still have some nagging questions that I cannot find answers to.
1. I had a situation where the copy for one TranLog took much longer than the 15 minute interval I have it setup for. It seemed to get stuck on that copy. Is that how it is supposed to behave.
2. Related to the question above, weekly, I have jobs that reorganize, check integity, recalc statistics. Would these jobs create very large log files? If so, how do others deal with this?
3. Is there any documents available that discuss testing converting your secondary server/database to your primary and back again?
4. Is there a way to setup Email notification to report out-of-sync conditions?
Any other gotcha's that people have run into.
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
View 2 Replies
View Related