Suspend All Transactions For A Split-Mirror Backup Strategy?
May 11, 2006
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 was contacted by the SAN team to test backup/restore of larger databases using a split-mirror backup (BCV) or clone that is taken from production db server and copied to another sql box. They want to use this process once a week. I see the mounted drives with the data/log files. All looks good. Initially I attempted to attach the databases and received (Unable to open the physical file db.mdf Operating System Error 5 Access is denied). I manually granting SQLServerMSSQLUser$<computer_name>$<instance_name> on all of the physical files 20 total. That worked.
Since this will be weekly, the SAN team performed the copy again and now none of the databases can communicate with the newly copied files. NTFS permissions need to be set again. I'm getting (Operating System error 21: the device is not ready). Is there something that I'm missing in this process how the vendor BCV clones the data and SQL communicates with the copied files as I was thinking it would be more automated process?
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.
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
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 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!
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.
I have a very simple question. Im studying for 40-431 and just finished reading about Log Shipping. It says in the book that when using Log Shipping you cannot run any other log backups because that would affect the LSNs. Can one do log backups with mirroring - principle and mirror? Because mirroring uses log to keep the mirror up-to-date and synchs on LSN.
I have a Server Agent Job that does a full backup of my principal database. I then have a separate job which does an incremental backup of the log files every 20 mins throughout the working day - the incremental backup sends me an email if it fails.
If I establish a failover to the mirror server, the backups continually try to run, sending me an email every 20 mins telling me that it can't back up the database as it's a Mirror.
Is there any way to script a method in the job that checks to see if the DB is a principal or mirror. if it's a principal I want the backup to happen, if it's a mirror I want it to skip the job.
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 want to do sql db backup.But how can I backup db to split backup files? The reason I want to split the backup file is becasue single file size is too big and I want to write to dvd.
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?
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
Is there any improvments in SQL 2008 backup methods such as spliting backup files in manageable size(s), compress the backups and/or improving the speed of backup?
Though there are "commercial" tools available, it would be nicer if Microsoft SQL team can incorporate some core needed features in these areas for Small/Medium size businesses.
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
Hi,Is it possible to somehow set a SQL Server DTS job to automaticallydisable itself, when it encounters a fail, so that future scheduledoccurrences don't happen until the problem has been fixed?I've hunted about for this on the web, but drawn a blank unfortunately!Many thanks if anyone can help at all.
I have an urgent requirement to setup a backup SQL server 2000. This will run as a mirror of the live SQL server 2000 which processes about 5000+ statements per minute. I will need to keep the mirror server as close to the live as possible, so if we need to take down the live server we can switch users to the backup server (it’s ok I can do this in code – client side.) and I may also be reporting from this server to take some of the strain from the live server (if that’s wise – not sure yet).
Can someone please point me in the right direction? What other software (apart from MS SQL server 2000) will I need? What sort of link do I need between both servers? (note: I don't want to do this link using triggers for each table if possible) Are there any pitfalls to look out for (i.e. I seem to remember something about auto id fields in tables but this was a long time ago so I could be wrong).
Please note I am a programmer with a lot of experience, but I don’t know that much about setting up SQL servers (well I can install one locally on my dev machine, but that’s really as far as I go) but I am willing to learn and investigate anything, so just giving me sites that I can look at will be enough.
