hello everybody
I have custom log shipping .
it run every 15 min
DB1 located on Server1
DB1 backup logs to server 2
DB2 located on server 3(300 km away from server1 and server 2)
Db2 restore logs from server 2
If server1 and server2 become unavailable
1.
Would enough login to server3
and
run
RESTORE DATABASE Db2
WITH RECOVERY
to bring db from read only to production mode up last restored log?
Thank you
Alex
I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following
RESTORE DATABASE database_name WITH RECOVERY
but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?
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
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.Â
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have a warm standby (secondary server) receiving log shipping files.
The database has 5 files all in the primary filegroup. Two of the files need to be moved from one hard drive to another. Whats the best way / process to accomplish the move and re-establish the log shipping recovery status?
Thanks
I'm allowed to do Log Shipping from x.contoso.com to y.contoso.com, however there is a requirement to have the same data made available to servers residing on z.contoso.com.
Connectivity between x.contoso.com and z.contoso.com is not permitted, so configuring log shipping with multiple targets is not possible.y.contoso.com and z.contoso.com can talk to each other, so what is the most efficient way to move data from y to z.
I 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.steps needed to setup TDE which are involved with log-shipping.
View 2 Replies View RelatedLets say I have a single instance SQL Server on a 2 node cluster with node names Server X and Server Y. These are part of Domain A.
SQL Agent Account runs under service account - DomainASQLAct.
Now I have Server Z which is a standalone SQL Instance on Domain B and there is no trust between domain A and B, not even one way trust.
Even though I dont have trust, I need to configure log shipping for the SQL Instance which is clustered under Domain A to a standalone box in Domain B.
Microsoft recommends Local Account(SQL Agent) to be used for Cross Domain Log Shipping. Also when there is no trust, MSFT recommends to use the same login name and password, however as my setup includes a cluster and cluster cannot have local account I'm not sure what will be the best strategy to follow here.
Log shipping was configured 6 months back. A Transaction log file got corrupted today. How to resolve this?
View 20 Replies View RelatedI have two databases on a Production Server that I want to Log Ship to a Test Server. According to the sys.master files the physical File Location is on an E drive. Early attempts at Log Shipping these two files error'd out due to space issues on the E drive (one Log Shipped and then one err'd out). I was subsequently informed from the server group that they would prefer that I Log Ship these two database files over to the M Drive where more space is available. In fact, they modified the Server Properties / Database Settings / Database Default Locations (for Data and Log) to the larger M drive (I'm not really sure why they just don't increase the E drive space but there is proabably a good reason).
Okay, so now my problems have been solved. Easy enough. Now I deleted the successful Log Shipped database and started from scratch. However, as before, one db restored and one failed (due to space issues). Apparently, both db are pointing towards the E drive. How is that possible?Â
So here I am with one successful database and the normal sys databases pointing to the E drive. What is the best way of approaching this  move to the larger and preferred M drive?
Is It Possible to Configure Log Shipping On a CDC Enabled Database?
View 4 Replies View RelatedI'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 RelatedSetup log shipping from an Australian SQL2008R2 server to a UK SQL2012 server. With the time involved to copy the large backup file across a WAN link, we end up with around 2 days of log backups since the full backup.
- The Full Backup is restored to the UK Server to create a new database in norecovery mode.Â
- The first log backup file is manually copied to the UK server and restored.Â
- Start the Log Ship copy SQL Agent job - starts copying files prior the full backup on the Australian server. Copies 1 Log backup file and then fails.Â
- Manually copy the next Log backup file to the UL server.
- Start the Log Ship Restore SQL Agent job and runs successfully and restores the Log file.Â
- Start the Log Ship copy SQL Agent job - AGAIN it starts trying to copy files prior to the Full Backup.  IS this correct behaviour? I thought it should only copy Log files since the full backup.Â
SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.
View 7 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 RelatedOut of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
So what is the simple way to do this check?
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 recently enabled log shipping on our production database, and I had initially accepted the default of 72 hours to delete copied logs. Well, i am running out of space quick, and I need to edit it to something like 6 hours.
When I try to disable log shipping in order to recreate it, or if I try to edit the secondary server settings when I am logged in as SA, or my windows account which has the sysadmin role assigned, I get an error that says:
Only members of the sysadmin fixed server role can perform this operation. Error 21089.
I've restarted the sql service, disabled and enabled the permission on my account, but for the life of me, i cannot get this to work!
hi,
The log shipping between my 2 serves have failed. Can somebody please tell me the steps to follow in order to restore the log shipping between the two servers. I applied full backup. But restoring of transaction logs is failing, I am not able to figure out the cause for the failure
I am getting following error in windows event viewer so can u tell me how to slove this problem
1.The log shipping primary database Aric-PCSQL2005.AdventureWorks has backup threshold of 30 minutes and has not performed a backup log operation for 319 minutes. Check agent log and logshipping monitor information.
2.The log shipping secondary database Aric-PCDEVSQL1.AdventureWorks has restore threshold of 5 minutes and is out of sync. No restore was performed for 304 minutes. Restored latency is 15 minutes. Check agent log and logshipping monitor information.
I am using sql 2000 standard. I set up log shipping 3 month ago it was working fine but yesterday its fail I got this error 4305. I don€™t know why this error I am getting
error messages:
failure /4305
activity : Load
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
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.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:MSSQLBackupMyDemoMyDemo_20061106115619.trn'
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
Any ideas or suggestions?
Hi,
I have a weekly job that runs to execute msdb.dbo.sp_delete_backup_history on history older than 7 days on a server that is a log-shipping destination server. However, I have now noticed after putting a new deadlock alert, that this job causes RESTORE LOG jobs for all 6 of my log-shipped databases, which run every minute, to deadlock.
I am not concerned with the fact that the RESTORE LOG jobs are then chosen as the deadlock victims, as they will simply re-run in the next minute anyways.
What I would like to do though is to somehow lock all the tables being cleared by sp_delete_backup_history, so that the restore jobs are forced to wait or fail without causing a deadlock to appear. I was wondering if I could do this via setting the job which runs sp_delete_backup_history to use serializable isolation level, or would that cause other problems/issues? If not that, would setting the deadlock priority level to high prevent deadlocks, or just simply force the restore log jobs to be chosen as the victim?
The main thing is that I would like to stop being "annoyed" by the deadlock messages being generated.
Any comments would be appreciated.
Regards,
Diane.
I'm trying to RESTORE LOG with a different recovery path. Is there a way to do this? I'm getting the below error. I know I could RESTORE from the beginning, but need to RESTORE the log file to the same database schema but different database and server. What steps do I need to do?
This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
Hello,
I have a file backup of a database from machine 1. I create an empty database with the same name on machine 2 and copied the backup file to machine 2. When I run a restore database, I get "Preceeding restore operation did not specify WITH NORECOVERY OR WITH STANDBY.Restart tehe restore sequence, specify the options for all but the final step. Restore failed"
Database : SQL 7.0 Trying to restore from the Enterprise manager
ANy clues as to what might be wrong.
Thanks, MMS
Hello all,
I just restored a SQL 7 db using the Enterprise Manager GUI interface. I
selected the "Leave database nonoperational, but able to restore additional transaction logs" option. I should not have selected this option as I do not have any transaction logs to apply.
Anyway, my question is... Is there a way to mark the database as opertational without running the restore again?
Thanks,
Corey
SQL Server 2008 R2
I don't see a "general discussion" thread and this is the closest i think.
I just have a general question: if my backup window is from 8am to 10am, and i do a restore within that backup window, what will happen? assuming we're talking only of a single database, ACME.
I'm comparing because with Oracle RMAN, it pukes when i do a restore while the backup is going on. it would complain of unable to find some archive logs.
I am set up log shipping  as backup and copy every one hour and restore every ( disconnect users and stand by) one hour on servers in same domain. Set-up completes successfully.Copy job works as expected. but the restore job fails intermittently to restore the .trn file saying ' could not apply  log, data invalid' .
when i  delete and copy the files for which restore failed  manually using CTRL+C and CTRL+V across network and re run the restore job it does restore successfully . Through out the day i get about 5-6 problematic .trn files on secondary server which are copied from primary server and when restore fails i have to manually delete the log file which had problem restoring and then copy that file again using CTRL+C and CTRL+V  then run the restore and it gets restored successfully.when running restore filelistonly on the logfile in issue on secondary server  it gives below error
Cmd:
RESTORE verifyonly
FROM disk='G:xxxx_Copyxxx_20150604010501.trn'
Error:
Msg 3203, Level 16, State 1, Line 2
Read on "G:xxxxxxx_20150604010501.trn" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.
and when i run restore filelistonly using netwok path then is seem s good and i can restore the logs as well
CMD:
RESTORE verifyonly
from disk='1.2.3.4xxx_Backupsxxx_20150604010501.trn'
Output:Â The backup set on file 1 is valid.I can assure there is no corruption on the log back up, no lsn mismatch, no log files missing.Same version and edition on both servers. Both servers are SQL server 2012 Enterprise edition on window server 2012Â
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
Given the follwoing scenario: You create a snapshot of a database with full recovery model, change it's recovery model to simple, then perform several updates/modifications on the database, before you finally (due to an error) restore the database from the snapshot.
Will the log chain be broken or not? The resteore sets the recovery model back to full, but I'm still a bit "curious" about the transaction logs.
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
Â
Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. Restore the server with Oct 1 full backup with NORECOVERY option.Try to restore to the point at Oct 5 12:00, with the transaction log.
Â
But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
View 2 Replies View RelatedHello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.