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?
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
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.
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.
How to test always on availablity after configuring them.I have configured always on group with 1 active and 1 passive with readonly.I want to test from application.what are cases which we can have for testing.
I am a SQL lightweight and I tried to update the data on the test SQL server with the data on the production server and now the test SQL server won't start.
I was trying to move a sql database from our production server to our test server and things got mucked up. I think where I went wrong is that I tried to backup the database on the Production server and then restore it to the test server. I think I should have backup the production server and then detached the databases from the test server and then attached the backups. I got in a hurry and had an attitude that SQL is so good that if I mess up, I can recover.
These are the databases I recovered to test server:
Builder CSales CIB Master Model MSDBdata
Now SQL server won€™t start and I get the error message that the database Model is in the middle of being restored. We are on SQL 2000. I tried to start from a command line with the €“f switch and it still can€™t come up.
Should I reinstall SQL and then attach the databases?
Do I need to bring over the Master, Model and MSDBdata data bases from the production servers.
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?
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.
Lets 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.
I 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?
I'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.
Setup 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.
I 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!
Out 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.
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!
I administer about 100 databases. I back them up to a file on the server hard drive everynight. Once a month I would like to test restore the backups . Due to the huge number of databases now its almost impossible to manually test restore them one by one so I came up with an automated script to do it. I have a database called testrestore and I restore each backup file to it get the count on certain crucial tables, throw it in a different table for later comparison and replace the database with the next backup file. I need to run this script on production, do you think its okay to test restore 100 databases one after the other using the 'replace' parameter ? Can it cause any memeory issues ? Is there any other way to test restore such a huge number of databases ? Suggestions are welcome.
I am trying to copy a production db (26.5 gigs) with a 3 gig log from production to a test server. The Prod db name is EDD_Cat which resides on one logical drive for the data (.mdf) and another logical drive for the log (.ldf). The test server does not have the same physical raid allocation. The only way that I can get that much space is to spread the data across 3 logical drives. I have preallocated a database called EDD_CatT with the same total physical db size. I have not been successful in restoring from a sql backup device (copied from production) to the new test db. Here are my tsql statements and error:
Restore Database EDD_Catt from Iloc01bkp with File=2, Move 'EDD_Cat_dat' to 'D:Mssql7DataEDD_Cat.mdf', Replace, Move 'EDD_Cat_dat' to 'E:Mssql7DataEDD_Cat2.ndf', Replace, Move 'EDD_Cat_dat' to 'F:Mssql7DataEDD_Cat3.ndf', Replace, Move 'EDD_Cat_log' to 'G:Mssql7DataEDD_Log1.ldf', Replace
start db restore --------------------------- 2001-01-02 12:23:31.610
(1 row(s) affected)
Server: Msg 3257, Level 16, State 1, Line 0 There is insufficient free space on disk volume 'E:' to create the database. The database requires 20447232000 additional free bytes, while only 1732972544 bytes are available. Server: Msg 3013, Level 16, State 1, Line 0 Backup or restore operation terminating abnormally.
I also tried using EM but basically got the same type of error.
I could do this with SQL 6.5 as long as the db size was the same or larger.
Any advice/suggestions will be greatly appreciated. BOL and the manuals that I have seem to only give examples that have one file for the data and another for the log but I could not find one that gave an example of what I am trying to do.
Thanks much for your time Calvin Matsumoto - State of California
Can anybody give me an idea or a script which can be used to Restore a production Database to Test Database on another server. As I need to do this 3 days a week, I would like to make this automated.
Please advise me about restore a master database from production to test server.
The reason to do this because I need to test and evaluate some login in master database.
I tried to restore master database to test server, but I got some errors regarding about user databases are not exists in test server. I don't want to restore user databases, I only need master database for evaluate user login.
I have performed several restores due to poor equipment(in the past where I used to work, now I have a brand new HP); this database has never crashed. I read an article in which the guy said a good DBA should be doing a test restore at least once a month. I see where that would be extremely important since i test my ups weekly, and my genset once a week, why not ensure that the data is good also.
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 have two SQL Databases on separate servers, live and test. I have been asked to copy the data from the live system and put it into test. They are SQL Management Studio 2008 running on MS Server 2008R2.
Could a simple backup of the database, then copy that file to the test system and restore the database from that point work or it there more to it?
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.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 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.
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.
