| Author |
Topic |
|
Rasta Pickles
Posting Yak Master
174 Posts |
 Posted - 2013-06-24 : 15:27:08
|
| Any sample code appreciated :-)We get requests to back up various databases.I have a query that polls the database in question and will return a list of users logged in; we then either phone those users or email them to say "can you log out, we need to do a backup".Once they're out, we do the backup (hoping, of course, that somebody doesn't log in while it's happening).My idea is: to run a perpetual loop along the lines of "while count (usersloggedin) <> 0 do x otherwise just keep looping".The x in this would be:lock the database so no-one can now get inperform a copy-only backuprelease the lockemail all users to say "you can now log back in" |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-24 : 17:12:17
|
quote:
Originally posted by Rasta Pickles
Any sample code appreciated :-)We get requests to back up various databases.I have a query that polls the database in question and will return a list of users logged in; we then either phone those users or email them to say "can you log out, we need to do a backup".Once they're out, we do the backup (hoping, of course, that somebody doesn't log in while it's happening).My idea is: to run a perpetual loop along the lines of "while count (usersloggedin) <> 0 do x otherwise just keep looping".The x in this would be:lock the database so no-one can now get inperform a copy-only backuprelease the lockemail all users to say "you can now log back in"
Is there a reason to kickout the users? You can do a backup while users are logged on and the backup will still be consistent. Users can continue to do most operations (insert/update/delete etc.). |
 |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-06-25 : 14:02:35
|
| In a financial database where there may be hundreds of transactions being auto-journalled by them while the backup is in progress?Would there not be issues with attempting to back up whilst users are monkeying around in there? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-25 : 14:11:05
|
| The database should be consistent, i.e., when you restore, you will get the database as it existed at some point in time even if users make changes to the data while the backup is in progress. I am basing this on the description on this page: http://msdn.microsoft.com/en-us/library/ms175477.aspxThere are some operations you cannot do while backing up (such as operations that involve ALTER DATABASE) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-25 : 15:56:34
|
| Backing up is the easy part. Now restore/recovery is where DBAs earn their money. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-06-29 : 10:14:38
|
quote:
Originally posted by tkizer
Our system is used heavily 24/7/365. I'm talking 1500-7000 batches per second.There is no reason to kickout users for a backup, unless you are doing a deployment or similar and need everyone out to make schema changes. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Fascinating, truly.So why does my company's DBA insist upon it? He'd even convinced me, up until now, that it was the right thing to be doing :-(Tara, say you perform a manual backup at 1p.m while batches are being posted and at 4.30p.m someone decides for whatever reason that you need to restore the 1p.m backup plus logs......won't you lose some data?(Sorry, DBA has really messed with my head re this whole backup thing) |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-29 : 23:22:09
|
| Tara can probably answer this better than I can, but I will give it a try.You will not lose any data, and you can restore up to the point of the last log backup if you do a full backup followed by log backups. When you do a full backup (or a differential backup) SQL Server locks NOTHING. So your users' insert/update/deletes can proceed as usual. Backup will have no impact on your clients logically or from a resource locking perspective. The only impact it will have is the added disk and I/O usage. The backup process takes a copy of all the data pages that are in use (or rather extents that are in use, so there may be some empty data pages in the backup). Then it also takes copies of part of the logs and saves that also as part of the full backup. The log that is saved in the full backup has the LSN's to enable SQL Server to identify which log records have been saved.When you restore, the data pages are restored and the log that is saved is used to roll forward/back as necessary. At that point, you have a consistent database as it existed at the time the read operation of the backup process was completed.If you restore using a full backup and logs, the full backup will be restored just as above followed by the log backups. Since SQL server knows the log sequence numbers, the chain will not be broken and all will be well.As for what your DBA asserted about having to kick out the users for doing a backup - that is incorrect information unless you are doing certain kinds of alterations to the database (described in the link I posted in my previous post). If we had to kickout users for taking routine backups, SQL Server would have been a very poor excuse for an RDBS.Do an experiment for yourself. Create a database, do a few updates/inserts/deletes. Then, open a few transactions, lock a few tables and leave them open/locked. Now take a full backup and restore as another database. You will see that the restored database is just fine; it will have your committed transactions, will not have any of the open transactions. Now commit the transactions in the original database, and take a logbackup. Restore the full backup and the log backup. You will see all the data including the newly committed data are correctly included. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-06-30 : 02:24:16
|
| Thanks James, I will experiment.Just as an aside, we always tick "copy only" when backing up, maybe that makes a difference? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-30 : 08:17:24
|
| You should not be ticking the "Copy only" option for backups in your regular backup plan. That option is meant for use when you want to get an "out of sequence" backup for some other purpose without affecting the backup sequence.Let us say your regular backup plan is to take a full backup every day at midnight and a differential backup every six hours at 6:00 AM, 12:00 noon and 6:00 PM. (and log backups in between). If you want to restore the database as it existed at 12:00 noon on a given day, you would restore the full backup from the prior midnight and the differential backup from 12:00 noon. SQL Server knows that the differential backup is the differential from the midnight backup, so it is able to put the full backup together with the differential backup to restore the database as it existed at 12:00 noon.Now, if you had needed to take a full backup for some reason at 10:00 AM that day, perhaps because a developer wanted a copy of the production database, your 12:00 noon differential backup is going to be based on the out of sequence backup that you took at 10:00 AM. So if you (or another DBA) tried to restore the database based on the midnight full backup and the 12:00 noon differential backup, that operation will fail. Checking the copy only option allows you to get around this problem. When check that option, SQL Server makes sure that it does not make any changes to the database backup sequence, thus ensuring that the differential backup at 12:00 noon will still be based on the full backup from midnight. |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-07-01 : 14:34:46
|
| Thank you James, most illuminating.There's one DBA who's going to get a thump in the stomach tomorrow morning before work.Microsoft certified muppet. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-03 : 10:18:28
|
quote:
Originally posted by tkizer
And that's why I ignore certifications when interviewing people. Unless it's Microsoft Certified Master, then you've got my attention and likely a job offer. lolTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Indeed! And it's a shame that all the brain dumps and cheaters have zeroed out the business value of these certifications.I took the T-SQL 2012 "Querying Microsoft SQL Server 2012", Exam #461, just to measure myself. I went with no advance preparation and really didn't care if I failed. The test was really good; you are deep into T-SQL territory with mind, body, and soul for four hours; I thoroughly enjoyed that experience, and other SQL afficionados like me would enjoy it as well, I am sure. And, I did pass with a pretty good score.Editing: Saw this post after I posted this reply. Really a shame that that is the mindset of a lot of people... At least he is asking also for books and other preparation materials, so may be there is some good there. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186583 |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-07-03 : 14:14:30
|
| Indeed; our DBA has at least two Microsoft Certified logos on his email signature and yet I wouldn't trust him in charge of a whelk stall let alone a corporate network.At least twice a day I have the exact same conversation with him:Him: "I don't understand, it was working perfectly until....."Me: "until you messed with it?"Him: "I didn't mess with it, I just changed part of the stored procedure. That shouldn't have broken anything"We all play DBA bull$h1t bingo these days: the first time he says "stored procedure" we all have to shout "bull$h1t".All good fun. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2013-07-05 : 23:16:39
|
if certifications are worthless, why do people collect them? I don't pay attention to them when interviewing people either. It must be the interviewers that themselves have the certs that like to see them on resumes. It's a feedback loop.
elsasoft.org |
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-07-06 : 15:54:15
|
| My company pay £500 a pop for each certificate.Maybe that's why?I did give our DBA a really good stomach punch (he was coughing up blood) and yet I'm sure I heard "err, ugh, stored procedure" in there as he went down.Muppets, every last one of them. |
|
|
|
|
Is an automated backup via SQL do-able?