Hi,
How can i make sure that i dont have any data loss in the event of DB crash.
I take daily full database backup dump to the disk and every 4 hrs transaction log dump.
What shoud be the backup strategy to get 100 % database from the backup/ to get in point in time receovery.
Best regards,
Madhu
My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups.
View 9 Replies View RelatedI 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 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 RelatedIs it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...
View 5 Replies View RelatedWe have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies View Relatedis bulk logged recovery model support point in time recovery
View 9 Replies View RelatedThe space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedPages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies View Relatedin 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.
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies View RelatedWe have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
I developed a intranet web application that stores about 1000+ new entries into a SQL Server database over the course of about a half hour. This runs 24/7 so its a very busy application. SQL Server is running on a server with a RAID 5 array which runs very good but I want to better guard against data loss. If I run a backup at 8:00 AM and the roof falls on the server crushing and destroying it at 5:00 PM, we just lost thousands of records that were made between 8am and 5pm that cannot be replaced.
I was thinking of adding a second server so when an entry is made it would be stored on both servers. The second server would be located at a different location so if 1 gets crushed we could just pick up where we left off and I wouldn't have to update my resume.....
Does this sound like a good idea or could anyone recommend a better solution?
Thanks in advance.
Is it possible to backup a remote SQL Server database to my local system? It is not working for me as I'm getting error as given below
Cannot open backup device 'path'. Device error or device offline
Any idea??
Does anyone know how to get SQL Server to see a network drive for backups? Even when trying to create a backup device on a network drive using UNC naming, SQL Server seems to have a problem with accepting it. If you browse for a place to put the device, only the local drive is accessible.
Please help. Thanks. John
I am working on a new SQL Server 7.0 system and have gotten to the issue of backing up and restoring the database. I am fairly new to SQL Server 7.0 and I am not sure how good the built in backup/resore is in SQL Server 7.0. The other optioin I have is Seagate Backup Exec which is currently being used for network backups.
Does anyone have any advide/information on this?
Thanks in advance.
Mike
Can anyone tell me some advantages/disadvantages to using ARCServe for
backups vs SQL Server 6.5 backup?
Thanks for the help!
Toni
Our e-business group will be running SQL Server as a backend and C#, ASP.NET, VB.NET application on the front end. Besides my enternal backups of user data on the database. What would you suggest for the Network team to backup and how offen. This is for productions. I usally get a full drive dump on the Dev network daily appended,and weekly archived. Any suggestion for production.
View 2 Replies View RelatedI have a job scheduled to backup a production database every night at 10pm. It will run fine for a number of days and then fail with the error:
Msg 3201, Sev 16: Cannot open backup device 'PCSLogisticsDB'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007] The backup set is valid. [SQLSTATE 01000]
When I go back and run it manually it works fine and then will run on it's own for a few more days. I've checked and File System backups aren't running at the time the backup kicks off. Has anyone experienced this?
I'm looking for an opinion on an architecture I'm thinking of rolling out.I have a HA cluster with 3 nodes:
Primary -- Main SQL Server
Failover -- Passive node, only used in DR scenario
Reporting -- Read Intent only queries directed here, SSRS.
I am pondering with the idea of balancing the backups out between 2 nodes as follow:
Primary: Full backup weekly, daily incremental.
Reporting: Log backups every 5 minutes.
I have tested that I can indeed restore the logs from the secondary node using the Full and Diff from the other server, no worries there.My concern is what will happen to the logs on the primary and fail-over server. Will they just grow, grow and grow?
Can SQL server handle High Availability or does it have to go downoccasionally for a cold backup? Does it support incremental backups?
View 4 Replies View RelatedI'm trying to create a backup set which maintains only a fixed number ofdays. As such, I've got the following script:BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD,NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT,RETAINDAYS=5DECLARE @i INTselect @i=position from msdb.backupset where database name='mydb'and type!='F' amd backup set id=(select max(backupset set id) frommsdb.backupset where database name='mydb')RESTORE VERIFYONLY FROM [mydb Backup Set] WITH FILE=@iThis script was created automatically, but I added the RETAINDAYS bit. Ionly want the past five days of backups available, but when I check thelist, I've got backups dating back to December 2003! The older backupsare correctly being marked as expired, but they are still on the list!How can I purge the list to only have the last five days of backups?Thanks in advanceJohnny
View 1 Replies View RelatedCan anyone please confirm if SQL Server 7 allows differential or indeed transaction logs backups? I noticed in Enterprise Manager that while trying to configure a backup for the Master database, only complete was highlighted and the other types including file and filegroup was greyed out....
View 2 Replies View Relatedwe are looking into a new tape drive and they say it is NT compatable and that its built around NT. My question the backup that is build in to sql 7.0 is it built around NT. I would think so since its a microsoft product
View 1 Replies View RelatedI am looking for the best method to backup SQL Server databases. Currently we are
running a dump database statement to disk and backing up the files to tape through Arcserve.
One problem that I am having is the statement to dump the database. I would like to retain
the dump for at least three days and be able to restore the database from any one of those
three days. My current statement is:
"DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH
NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP"
but, every other day I receive the message from SQL executive:
"Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)"
What am I doing wrong? Any suggestions?
P.S.
Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but
the backup files still remain on the disk and I have to delete them every week.
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
View 4 Replies View RelatedFor some reason my backups (Transaction log and full backups) have started failing. When looking at the job histoy for the step that failed it reports:
*****(0.011 MB/sec). [SQLSTATE 01000] (Message 3014) Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.*****
I have tried running the SQL for the step manually and I get:
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.
Server: Msg 3013, Level 16, State 1, Line 36
Backup or restore operation terminating abnormally.
The step is only determining what day of the week it is and putting it in to a '@bkupday' variable then: "BACKUP LOG [model] TO @bkupday WITH NOINIT , NOUNLOAD , NAME = N'Model Log Backup', NOSKIP , STATS = 10, DESCRIPTION = N'Model log backup', NOFORMAT" for each database and log.
This had been working fine, but for some reason has now started playing up. (trying to workout wether this ties in with when I started merge replication of one of the DBs)
Any one got any ideas as to what might be causing this as it's happened for a couple of nights now?
Cheerz.
Has anyone had experience using ‘VERITAS Backup Exec For Windows Servers Agent for MS SQL Server’ to run backup jobs? If so, what are the pros and cons?
I have always preferred using SQL Server Agent (not the maintenance plans) to run backup jobs and don’t know why anyone would want to use 3rd party software to do something SQL Server Agent does perfectly. Any advice?
Hello,
If I understand correctly I cannot do table & databse backups and restores or exports & imports as with Enterprise Manager in SQL 2000 because I have the lowest level version of the Studio Express Manager. Is that right?
Can anyone tell me what I need to do to regain that funtionality? It will be used for managing local (on my pc) and remote databases.
If it involves downloading and installing a different Studio version:
1. which one would that be?
2. is there a cost?
3. do I need to UNINSTALL my current version?
4. if so, how do I preserve my existing local databases (since there's no backup capability)
Many thanks in advance for any advice out there!
Richard
Not sure if there is anyway to do hots on sql server .From all I haveheard an EMC can do clones and fractures in order to implement a hotbackup or some kind of it in sql server.Seemingly the DBA has to do something toenable the fracture of the clone . Is anyone familiar with this ?Your input is highly appreciatedMB
View 1 Replies View RelatedHi there,
I am looking to be able to backup our database automatically on a daily basis. It is for a SQL Server 2005 Express Database. Does the Express edition have automated backups or does it have to be carried out manually?
I'm not sure of which steps to take.
Could someone please advise
Thanks
Hey guys,
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Thanks,
-Kilka