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've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.
Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).
This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Is this just cosmetic?
Here is an Example of how the Studio shows the incorrect path for the secondary.
I received an alert from one of my two secondary servers (all servers are running 2012 SP1):
File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.
The detail in the job step shows this additional information:
*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192. Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.
I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.
We have set up Log shipping between Primary and Secondary DB. The secondary DB is right now option: Standby/Read-Only. I can not take Backup of Secondary DB now.
Shall we disable Log shipping and change the DB Option to Multi-user mode and take backup? or any different method, without disabling log shipping?
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.
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?
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?
I 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 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.
I'm a MS Access developer who needs to help someone migrate/convert from a 'finished' SQL application to Access. How do I gain access to the SQL backend so I can examine/export the raw data? For example, in Access, I would hold down the Shift key while opening the program and it would give me editing rights to the database.
Please be very specific because I have zero experience with SQL.
We are using MSSQL 2005 and Log Shipping. After making our secondary SQL server primary, how can we put the secondary SQL server back into standby mode?
We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.
I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.
When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.
The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.
Message 2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'. 2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0
I checked the server and found that LS restore job failing and Backup and copy jobs running fine without any issue. and also observed that Copy folder the trn file existing on secondary server. i try to restore trn file im getting the error. and observed that last log backup file that it restored at the secondary database on May2nd,2015.
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
From Restore job histort details below.
Message 2015-06-02 12:25:00.72*** Error: The file 'xxxx\_20150530104503.trn' is too recent to apply to the secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) *** 2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** 2015-06-02 12:25:00.73Searching for an older log backup file. Secondary Database: 'database' 2015-06-02 12:25:00.73*** Error: Could not find a log backup file that could be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) *** 2015-06-02 12:25:00.74Deleting old log backup files. Primary Database: 'database'
We tried to configure log shipping using script generated by GUI and when executed specific script which is meant for secondary server the database is not created and threw below error.
Msg 15010, Level 16, State 1, Procedure sp_add_log_shipping_secondary_database, Line 50
The database 'BUBALLO' does not exist. Supply a valid database name. To see available databases, use sys.databases.Â
Note:Â Only Copy, restore and alerts jobs have been created.
The account I'm trying to configure log shipping is the service account by which SQL and agent services are running and folder in where data and log files are intended and to be created is open to all (everybody has read/write permissioins) believe the issue is not with permissions.
I am trying to implement a log shipping scenario in sql 2005 where the secondary server is in standby mode with the ability to roll change during failover.
With the help of BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2d7cc40a-47e8-4419-9b2b-7c69f700e806.htm) I can implement my scenario in Recovery mode, but not in standby mode. I use the following sql to put my primary in standby
BACKUP LOG [database] TO DISK = @filename WITH STANDBY = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupROLLBACK_UNDO_database.BAK' GO
which works, but then my restore job fails on the last step. How can I put my primary db in standby mode in such a way that the log shipping restore will work?
I set up Log Shipping with just 2 servers primary and secondary. When I run from the Wizard for the very first time keeps failing at the stage of saving Secondary Server Configuration info. When i instead run the generated script this problem disappears but then restoring of transactions fails - the process can backup transactions from the Primary server , copy them accross to the secondary and fails on the restore. Any ideas why.
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?
I have created a package to copy 10 tables from a database to another one. I have created a datasource with SQL Authentication mode. And it couldn't run until I changed the datasource connection to Windows Authentication mode. How could it be like that, it's just the authentication mode?
Is there anyone have the right answer for this situation?
I am trying to configure log shipping on same server with different instances/I am facing the below error//Cannot open backup device.Operating system error 67(The network name cannot be found)Restore filelist is terminating abnormally (Microsoft sql server Error 3201)
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 have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?
I'm new to replication. I configured Server named TESTER as publisher & distributer. Create new publication for database named TEST ( type : Transactional ), then Create a new push subcriptions for database TESTREPLICATION ( on the same Server : TESTER). Then I open a table in database TEST to input some data but I couldn't see any tables or data in database TESTREPLICATION ( I chose Continuously, not scheduled).
Yet, after the lunch, I could see the tables, no data. I tried to choose Start Synchronizing but not helped.
Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.
So I have tried below steps
1) Executed following statement on the same secondary replica database where the issue exists
alter database <DBNAME> set multi_user with rollback immediate
but it failed with the error saying "The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed."
2) Primary database is multi_user but still tried following command on primary replia database(thinking it will replicate)
alter database <DBNAME> set multi_user
but no luck. The secondary alwaysON database shows (synchronizing) as the alwaysON is set in async mode but the command doesn't replicate across secondary
so we are left with the only option to re-setup alwaysON but I want to avoid it as database size is huge..
I have just installed sql server 2005 enterprise edition. I am totally new to sqlserver. I want to create a database in sql server and access that from my VB.net application. I couldn't find sql server management studio to create a database. can somebody help me with this...
Because forum HTML codes are OFF I had to put my question between code tags. Because to tell my question I had to show the database tables and wanted output. I succeeded retrieving the datas but combining these three tables together with SQL or ASP codes is too complex. Please help me determining the table. As I am subscribed to this forum you can ask me anything about my question. I can clarify my question if you need. Your answers will be replied immediately. (To see my question pls save the codes below as htm file or see it with a wysiwyg editor.)
I'm not able to browse through http://localhost/reports.
When I'm trying to connect it gives me "Server Error in '/Reports' Application."At the same time I'm able to view Reportserver.At the same time I'm able to view Reportserver.(i.e)http://localhost/reportserver
please can anyone suggest me some solution to this problem.. Server Error in '/Reports' Application.
The trust relationship between the primary domain and the trusted domain failed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.SystemException: The trust relationship between the primary domain and the trusted domain failed.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SystemException: The trust relationship between the primary domain and the trusted domain failed. ] System.Security.Principal.NTAccount.TranslateToSids(IdentityReferenceCollection sourceAccounts, Boolean& someFailed) +1167 System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean& someFailed) +42 System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean forceSuccess) +46 System.Security.Principal.WindowsPrincipal.IsInRole(String role) +205 System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120 System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300 System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191 System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3403086 System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application  jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER  During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.  After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".  My Question is
Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring
 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
