SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Is an automated backup via SQL do-able?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/24/2013 :  15:27:08  Show Profile  Reply with Quote
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 in
perform a copy-only backup
release the lock
email all users to say "you can now log back in"

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 06/24/2013 :  17:12:17  Show Profile  Reply with Quote
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 in
perform a copy-only backup
release the lock
email 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.).
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/25/2013 :  14:02:35  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 06/25/2013 :  14:11:05  Show Profile  Reply with Quote
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.aspx

There are some operations you cannot do while backing up (such as operations that involve ALTER DATABASE)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/25/2013 :  15:56:34  Show Profile  Reply with Quote
Backing up is the easy part. Now restore/recovery is where DBAs earn their money.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 06/25/2013 :  16:23:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.

Our backup strategy is a daily full, diff runs 12 hours after the full and then tlog backups every 15 minutes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/29/2013 :  10:14:38  Show Profile  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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)

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 06/29/2013 :  23:22:09  Show Profile  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/30/2013 :  02:24:16  Show Profile  Reply with Quote
Thanks James, I will experiment.

Just as an aside, we always tick "copy only" when backing up, maybe that makes a difference?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 06/30/2013 :  08:17:24  Show Profile  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 07/01/2013 :  14:34:46  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 07/02/2013 :  18:04:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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. lol

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 07/03/2013 :  10:18:28  Show Profile  Reply with Quote
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. lol

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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

Edited by - James K on 07/03/2013 10:22:08
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 07/03/2013 :  14:14:30  Show Profile  Reply with Quote
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.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 07/05/2013 :  23:16:39  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 07/06/2013 :  15:54:15  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000