Recovery :: Point New Log Shipping Files To Different Drive
Jun 9, 2015
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?
View 6 Replies
ADVERTISEMENT
Aug 31, 2015
Is there a way to get access to the files on the shared drive from either of the nodes when the SQL Cluster is shut down?
It doesn't look like there is, but hoping there is some trick. I see how it can be done with a Windows Cluster but not a SQL Cluster.
View 4 Replies
View Related
Mar 26, 2008
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
View 4 Replies
View Related
Dec 23, 2014
is bulk logged recovery model support point in time recovery
View 9 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
May 21, 2002
Hi Guys,
I wanted to know how to do a point in time recovery on sql server 2000.
I wanted to go back in time when I restore the file.
Can u also suggest any good book about it. Does BOL explains about it, I couldn't find it in BOL.
Thanks in advance.
Joe
View 2 Replies
View Related
Sep 2, 2004
We have been testing point of time recovery using EM and found that this does not work.
We enter date and time and do net get the logs restored. Even if we use the default date it does not work. In Query Analyser we have have managed to recover to a point in time. Anybody got any idea why EM does not work.
We are using 2000 sp3
View 2 Replies
View Related
Oct 13, 2006
Dear All,
How do we accomplish point in time recovery with SQL Server database.
For example:
My backup schedule is
Monday - Complete database
Tuesday - transaction log backup
Wednesday-transaction log backup
Thursday - transaction log backup
Friday - Complete database
Saturday - transaction log backup
Sunday - transaction log backup
For complete database backup, I use the below syntax:
BACKUP DATABASE myDB
TO DISK= @File1
WITH DESCRIPTION = @Desc
For transaction log backup, I use the below syntax:
BACKUP LOG myDB
TO DISK= @File1
WITH DESCRIPTION = @Desc
With this scenario, can I accomplish a point in time recovery? For example, if my database crashes on thursday night. How do i do complete recovery till that time?
Pls guide...
Regards,
qA
View 1 Replies
View Related
Aug 10, 2000
Hi Everybody:
We plan to do point-in-time recovery for certain databases. We plan to do Complete Database Backup every night and transaction log backup every two hours from 8:00 AM to 5:00 PM. I have following questions regarding the log backup.
1. There are two type of backup 'Append to media' or 'Overwrite'. If I choose 'Append' for log backup, is that mean I only need to restore database against last log backup file because all previous log backups have been accumulated there?
2. Can I automatically truncate log after the backup is done? How I can do it?
Thank you very much.
Joan
View 1 Replies
View Related
Nov 11, 2001
Is is true that in SQL Server 7, a point in time recovery can not read the active logs? So that any point in time recovery has to be with in some time frame of the backups of the logs?
Example:
If you have a full backup at 5 am.
Log backups every 2 hours.
You need to recover at 3:55. You would have to go back to your full backup from 2 am and the transaction logs from 6, 8, 10, 12, and 2.
You would lose any work done from 2-3:55pm?
View 3 Replies
View Related
Jul 26, 2007
When restoring a database where the data file(s) are located under a mount point, seems to be a problem with reporting free space available.
DB Size - Say 30GB (25 Data, 5 Log)
E: Drive 20GB with 15GB Free
Restoring database file to
E:SQLMountPoint
- This points to separate disk with more than enough free space
SQL prevents the restore by stating there is not enough free space.
A long work around is
assign a drive letter to the large disk
Restore database using temp drive letter - F:SQLDATAMyDB_Data.mdf
Update sysfiles - changing drive & path - E:SQLMountPointSQLDATAMyDB_Data.mdf
Stop & restart database...
Is there a better way???
Wishlist - Restore only warns about free space - but allows continue...
Regards
View 7 Replies
View Related
Oct 23, 2015
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?
View 12 Replies
View Related
Aug 9, 2004
Good Morning guys n Gals.
New user on these forums, so basically, hey, how are yas?
I have a bit of a situation, and have done a search - although not much info has turned up relating to my situation (maybe my search was just useless).
Anyways, basically, I run SQL Server 6.5 on NT4 server.
The disks were striped - not RAIDED.
What happened was, the 4th disk died, and we replaced it with a new blank drive.
Now, I am left with two DAT files (log and data) for my database.
I cannot remember how large these were created as it was many years ago.
I have no backups.
Reseting the status hasn't worked, also I have tried creating a new DB / Devices (in size similar to those reported in windows), and "moving" the devices to point to the original ones, but no luck: and thus was wondering if anybody had any hint / tips or ideas?
Cheers,
Jamie
View 2 Replies
View Related
Dec 18, 2001
Hi All,
If I perform a truncate table (non-logged operation) in my application, will this preclude me from being able to do a partial recovery (point-in-time) of my app. using the transaction logs?
Thanks in advance,
Liz
View 1 Replies
View Related
May 6, 2015
How do I recover point in time if the database is participating in transactional replication and is a publisher.
View 0 Replies
View Related
Sep 16, 2015
Assuming all windows servers belonging to the WSFC are on the same subnet, will the AG listener become a single failure point in system if all Application servers connect to the AG through the listener? If the answer is yes, what are the options to resolve this issue?
View 3 Replies
View Related
Aug 21, 2003
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
View 1 Replies
View Related
Apr 10, 2008
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
View 3 Replies
View Related
Apr 30, 2015
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.
View 6 Replies
View Related
Sep 16, 2015
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 Related
May 13, 2015
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.
View 8 Replies
View Related
Sep 20, 2007
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?
View 10 Replies
View Related
Dec 21, 2007
I am amazed to see this morning that log file consuming whole disk space even though the database is in simple recovery mode.
What could be the reasons to fill in the space even in simple recovery mode??
View 10 Replies
View Related
Aug 7, 2015
Log shipping was configured 6 months back. A Transaction log file got corrupted today. How to resolve this?
View 20 Replies
View Related
Nov 13, 2015
Is It Possible to Configure Log Shipping On a CDC Enabled Database?
View 4 Replies
View Related
Oct 12, 2015
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.
View 3 Replies
View Related
May 11, 2015
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.
View 3 Replies
View Related
May 1, 2015
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 Related
Sep 25, 2015
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!
View 2 Replies
View Related
Jul 16, 2015
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.
So what is the simple way to do this check?
View 4 Replies
View Related
May 5, 2015
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.
View 4 Replies
View Related
Mar 24, 2012
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!
View 10 Replies
View Related
Nov 6, 2015
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
View 7 Replies
View Related
