I have only ever been required to take a full back up of my main prod database every night.
Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.
I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:
Full backup @ 7pm
Backup tran log every hour after that starting @ 7am (as there are no changes overnight)
How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it? Basically I need to know what to do so it doesn't get too big!
Hi All, Can any one help me with this.. I've a critical application that can't be stopped for a second. I'd like to have an implementation that uses 2 sql 6.5 servers one as standby and which is ready and up to date to take place and run instead of the master server when it's down and when the master is back to work it's updated with the data entered to the standby. This process must be automatically to maximum extent. Thanks Mohamed
Pardon me for asking a question that I know has been asked before. I need to develop a backup strategy for our SQL Server and I am looking for any help that anyone can offer including recommending good books for reading.
In SQL Server 6.5, Is it generally better to dump the transaction log first, then the database or to dump the database and then run a dump 'tranlog with truncate only' option?
I've recently inherited a position where I am responsible for the well-beingof some DBs.2 (much) more important than others.The current recovery model, from what I can tell, is to do a full db/logbackup overnight.This .bak file is then written to tape as well as saved on the disk for 2days.Both these dbs are used fairly extensively 8-5pm and losing data would notbe good.The db sizes are approx 5gb and 3gb.This doesn't seem like the ideal situation to me. Everything I read tellsme... full backup periodically, differential nightly and transaction hourly.Agreed?If so then I have 2 questions:1. Is the best way to do this via a maintenance plan or by scripting andscheduling?2. What, if any, overhead can be expected with regular transaction backupsduring work hours?A bit of a pointer to #1 would be appreciated also.Thanks.
We are currently doing daily full backup of system & custom databases since database size is small. Is that good idea ? or better option would be weekly full & daily incrementatl ?
Do we need to do any special backup on system databases or transactional logs ?
I have concern about an sql server. The server has the operating system and sql server installed locally. The databases and transaction log files is stored on SAN. We used to have the database backup and transaction log backups stored locally on the server. We tape the database backup and transaction logs every 24h. If we lose the san and the server then we are stucked with no backup easily accessible. And on tape we loose of 24h data. We decided to put up stand alone server with no connection to the san and dump the backups file on this server. We also put a secondary server sql I case of emergency, to test backups and We are looking at the getting a mirroring or log shipping solution but we are not there yet, next year€™s budget. We still using some old server left from migrating to virtualization.
Then I read the €œPractical Troubleshooting The Database Engine book€? best practice not to avoid net work drive backups. Stuck aging. Back with the backup to local drive and robocopy them? Keep them on the network drive, start using? MIRROR TO in the BACKUP DATABASE? Today we are using the Backup Database Task in the SSIS.
I am running SQL Server 2005 x64 Enterprise under Window Server 2003 x64 Enterprise. After reviewing many posts and suggestions in this forum, I am developing a backup strategy that should include keeping my transaction log file in a manageble size.
Please examine the following proposed backup schedule and let me know if this is considered a sound plan. The scripts below will write to disk and each night and then be backed up to tape.
*** TASK 1 ***
Backup transaction log
/* This script backs up the DSS database transaction log to disk, overwriting any previous backup */
BACKUP LOG [DSS]
TO DISK = N'g:mssqlackuplogdss_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
*** TASK 2 ***
/* This script shrinks the DSS database transaction log file
*/
BACKUP LOG [DSS] with truncate_only
dbcc shrinkfile(DSS_log)
**** TASK 3 ****
/* This script backs up the DSS database to disk, overwriting any previous backup */ BACKUP DATABASE [DSS] TO DISK = N'g:mssqlackupdatabaseDSS.bak' WITH DESCRIPTION = N'DSS Full Database Backup' , INIT , NAME = N'DSS - Full Database Backup' GO /* Backup validation to ensure the file is valid before storing it */ RESTORE VERIFYONLY FROM DISK = N'g:mssqlackupdatabaseDSS.bak' WITH FILE = 1 GO
This may seem like a silly question, but has anyone ever heard of a DBA or an Engineer deciding to not back up databases inside EM, and only relying on the RAID or third party software for redundancy?
SBS 2003 R2
SQL 2000
Veritas 8.6 open file agent, SQL agent, Exchange agent
In my current organisation they are using SQL Server.
They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.
However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.
Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?
My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.
My maintenance plans are starting to acting weird. I'm building a custom script to manage the database backups on my server, but curious if anybody has some sample work that will allow me to avoid re-inventing the wheel.
A couple of primary constraints: I want to do a full backup daily (and only retain 1 day of full backups) Transaction Log backups every 20 minutes I'd like to loop through the databases on the server automatically to make this a little more flexible.
You have anything you'd like to share? Or, bits of knowledge worth sharing?
I tried searching, but it appears the search is still broken.Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).Backup our DB weekly w/ transaction logs.I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.Along with that I have some optimizations and shrinks I run. Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.So. Here in lay the questions.The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).So, I sorta get that..I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?I guess I'm a bit befuddled here.
I would like to have guide for backup estore strategy for production DB..if i have to apply for high availability of production DB..
consider scenario ..Full backup every week, differential backups every 6 hours, and logs every fifteen minutes.
suppose my DB is crashed at 8:14 AM and my last logs were backed up at 8 AM then by looking above startegy then i lost my DB from 8 am to 8:14 am???? if not then where it shud find???
also looking above scenario by taking backup for such high frequency is it full my disk?? guide me pls...
Hi,Could anyone tell me the backup strategy for a 1000GB database?Thank you!Peter Wang*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi I have one query regarding backup ecovery startegy hi
I have Db with simple recovery model.. recently i updated my column without where clause now i need to recover all my original state of that column..i just have idea @ set DB recovery model to Full then take full back up and then take differantial back up and then take transactional log back up and restore it as full , diff and then most recent transactional log back up...but still i didnt get original state of that table column...
Real World: Backup Strategy and implementation, how? A quote:
€œReal World:
Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:
//servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups. //servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups. //servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups. //servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.
Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.€?
I understand these concepts, however in €˜the real world€™ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3€™s rotation.
Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.
I would like full details on this please, as I need to get my head around it.
We have a critical Production database on which we want to setup Log-Shipping. We have also purchased the Symantec NetBackup utility for taking Backup to Tape Drives. We Know that there are some inherent problems with using Log-Shipping and a Backup Strategy together, and thus we were finding out various ways in which we can run both in tandem.
One of them was taking the Symantec Log backups with Copy-Only option. The main problem with this is that the Symantec Backups becomes dependent on the Log-Shipping Backups and also most of their Log-Backups become useless. Also, we are not sure whether the utility allows Copy-Only backups
The second alternative was to disable the Backup job of Log-Shipping on the Primary Server and to use the Log-Backups done by the Symantec utility for performing the Restores on the Secondary.
Thus, if Log-Shipping is scheduled to run say every 4 hrs, and the Symantec Log-backup happens every 1 hour, then at an interval of every 4 hrs, the Restore Job on the secondary will pick up the 4 backups done by the Symantec Backup utility and Restore each one of them in sequential manner. But, I guess it is not easy to have a manual Restore Policy in place. I was really banking on this solution until I found that the Restore Job of the Log-Shipping setup is dependent on the Filename of the Transaction log file which the systems generates automatically, and it won€™t be easy to create a customized Restore Job on the secondary server which takes in all the Log Backups generated by the Symantec Backup utility and Restore the Secondary database.
Have any one of you ever face this issue? Would like to know what is the best way to keep both of them running together.
We currently use a split-mirror backup strategy for our Sybase database, which has a "quiesce database" command to suspend all transactions. By quiescing the database before splitting the mirror, we suspend all transactions to ensure we get a stable backup of the environment. It works very well for us and I'm trying to understand how we could implement this with our SQL Server 2005 DB.
(I'm aware of SQL Server mirroring and that there are other ways of possibly backing up the DB. In this post however, I'm only interested in how I would make the split-mirror strategy work if I wanted to pursue it. I'm trying to avoid paying for software that uses the VDI as it's quite costly.)
Can someone help me with how I would accomplish a split-mirror backup strategy in SQL Server 2005 (without using a vendor's software that uses the VDI)? I have to imagine there's something similar to the "quiesce database" command in SQL Server...
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 am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them. 2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write restore filelistonly from disk = 'path and filenam.bak' restore database. zy from disk = 'path and filename.bak' with replace, move..... move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
I had to replace my hard drive which was going bad. I reinstalled Sql 2005 and now need to restore the backup that I have. I selected Restore Database and chose From Device, and selected the backup location on J: drive and selected the backup file which has no extenstion. I checked the checkbox next to the backup set to restore. Click OK and then I get this error message: Restore failed for Server System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo) I don't know why it is looking for a MDF file as this was never in the Backup folder. Can someone please help me on this? I thought I was doing the right thing making a backup all along and now I can't restore it!
Is it possisable to restore from tape when the backup was taken from a different server. ServerA has the tape drive which performs the backups to tape, but ServerB which is under a different domain wants to restore ServerA database on ServerB, both databases on the two servers are the same is it possiable to restore a database on ServerB using ServerA file?
hello all !! I suffered a crash on one of my disks on a server runing MS-sql 7.0,and found out my backupexec dosnt really restore a runing version of my database. which solution would you recomend ,taking in acount i may have a copy of the database on another computer,which files i can restore? thanks yochai lam
Hi I am operating my databases under "trunc log on checkpoint" mode as I do not need point of failure recoverability.I was performing a recovery test using a backup which resulted in it being unsuccessful .Error Message received was "Recovery has failed because a nonlogged operation could not be redone.Use the RESTORE statement to restore all data in filegroup PRIMARY to a pt beyond the nonlogged changes"
Isnt sql*server supposed to prevent a non-logged operation while a backup is in progress?Also how do i prevent such a situation from recurring. If i switch to single user mode before a backup how do i prevent exisiting users from accessing ... without bouncing my sql*server?
I did a restore of database the user the original database is admin where it says the user is dbo.
After i did the restore i was able to add admin as user and dbowner but when i tried to change the user it said the user already exists.
How do i create the same..enviroment as or the original system with users..i saw a problem i i drop the user and them recreate it then it cannot find the object or the object which are owned by it are lost..could any one help me with this ...so as how to and what do i need to keep track of while restoring the database and configure the server back with same user names and permissions.
I have two databases on two different SQL Servers,our application is updating these two databases simultaniously.So every time these two databases will be in synchronized state. Now i want to schedule backup for these two Databases.How can i take backup of these two databases at single point of time. so that if i restore these two backups on corresponding servers,There should not be any conflicts.
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'NetworkDevice'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
I don't really understand what's goin.
basically i need to synchronize my production database so what am i planning is to take a full backup on that server everynight and then restore it back.
Can some one guide me to take a backup as well as restore on the remoteserver with syntax and what does it mean to add a device...i could not understand.
Is there any other process that's much faster to implement.
