June 26, 2014 at 7:43 am
I work for a small facility where we have just one database. The prior dba, left and now I have been put in charge over it. I know enough to get me into trouble. Our database is 30GBs, its for medical health records, and as of right now its recovery model is set to SIMPLE. I would like to change it to full and start doing every 30 minute t log backups. Whats the best away to go about this? I will do a full backup at night with a maintenance plan. I will then do another plan for the 30 minute t log backups. Then finally, I will do a maintenance plan that will keep that backup directory clean. My questions are:
Should I append my t log backups or write each t log file to its on file? Of course this will make restoring harder as I will have to restore each t log one at a time unless I script something.
We have another SQL Server 2008 r2 box that is basically for test, should I do log shipping to this server/instance for failover? In the event of disaster I can just switch client DSN's to point to that box while the other box is being fixed.
June 26, 2014 at 7:54 am
I would backup the T_Logs to separate files just for the sake of seeing when the most activity occurs each day (it's helpful to know down the road). It doesn't make Point-In-Times restores difficult at all if you use the GUI to do the restore. It'll actually select the latest Full Backup and all of the proper log file backups automatically and give you the option to add other options.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2014 at 7:59 am
Gotcha. I have been testing restores using the 'From Device:' instead of 'From database:'. If my main SQL Server was to go boom, I would have to use the 'From Device'...
June 26, 2014 at 8:00 am
Every backup to its own separate file.
Appending backups to the same file doesn't mean you can restore them all in one single operation, you'd still have to run the RESTORE LOG statements one by one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 8:02 am
Should I append my t log backups or write each t log file to its on file? Of course this will make restoring harder as I will have to restore each t log one at a time unless I script something.
Do each one to its own file. It's much more intuitive to manage. You need the same number of RESTORE LOG statements whether you have one device or many.
We have another SQL Server 2008 r2 box that is basically for test, should I do log shipping to this server/instance for failover? In the event of disaster I can just switch client DSN's to point to that box while the other box is being fixed.
You can do that, yes, but you'll want to make that just one part of your company's business continuity/DR plan. If you're using a test server, beware of stuff like unintentionally exposing test data to live users.
I would backup the T_Logs to separate files just for the sake of seeing when the most activity occurs each day (it's helpful to know down the road).
Yes, this enables you to see backup sizes at a glance, although obviously once your old files are purged you won't be able to see them any more. For a more complete picture of when the most activity takes place, you can query the backupset table in msdb.
John
June 26, 2014 at 8:07 am
I'm with Jeff, and I'd toss in a few other suggestions as well...
First, pick up or download (hey, it's free!) a copy of "Troubleshooting SQL Server A guide from the Accidental DBA" from Red-Gate (disclaimer: I liked the PDF so much, I bought the book, and I don't work for Red-Gate)
Second, make sure you get a plan in place to copy those backups *OFF* the server to some sort of removable media, ideally several sets of media. Those backups won't do much good on the server drives if the drives crash, or the building burns to the ground.
Third, at least for a while, monitor that directory the backups are being written too. I've seen where a maintenance plan won't always "clean up" like you expect it to, usually because you typoed something (been there, done that, forgot to buy the t-shirt)
Once you've got a decent backup plan in place and working, test your backups by restoring one or more to your test box. After all, the backup won't do much good if it can't be restored...
After all that's up, running, and tested, then I'd look at setting up log shipping. It's a somewhat complex beastie (and this from someone who's only done it in test and lab environments,) so try it out in a practice setup. Maybe a couple VMs or even just a couple instances of SQL on a laptop. Get the hang of setting it up, so that if something goes wrong, it's not gone wrong in production...
Jason
June 26, 2014 at 8:07 am
Factory909, Congrats and welcome to the world of Database Design/Development 🙂 I became an Accidental DBA about a 14 years ago, and who knew working with databases would become a passion given my experience before that was application development and server administration.
As usual I agree with Jeff, but I'd also like to add that you should be backing-up your databases (System and User) to another server or cloud share if at all possible. This way if your server does go south you can recover more quickly after building a new server. Also if you have the resources to build a second SQL Server do some DR testing and go through the Restore process using your backups from the prior day.
Having this process down will also be helpful when someone comes to you at 3:10pm saying they've just deleted a slew of mission critical data. With your 30 minute logs you can totally save the day by quickly restoring your database back to the 3pm point and restore their data. Trust me, it'll happen. Also having this process down for DR purposes is good to have as well.
Take care --
June 26, 2014 at 8:17 am
Awesome and thanks for the information. Got the PDF downloaded and will take a look at it this evening. We are doing backups of the backups to a AppAssure box and also to a NAS device that is also backing up to a USB drive.
Another question. If a person were to lose everything, could they use the .mdf and .ldf to restore, err attach? Does this count as some form of a backup? I've got my maintenance plans re-setup and will keep an eye on them for the next few weeks. Since the former dba has left, we have been testing the backups every couple of days, mainly to get the hang of restoring, and get it documented better. I don't want to have to fumble around at 3AM on Google looking at how to restore the DB.
Once again, thank you for the clarity.
John
June 26, 2014 at 8:31 am
In a pinch, yes, you can attach the MDF / LDF and have a working database.
An example situation would be if say the OS crashed hard, you booted the server from a recovery CD, copied the MDF / LDF to an external drive, then copied them onto your test server.
The reason you wouldn't want to try copying the MDF / LDF as a "backup" is SQL would have them locked for use. While *in theory* you might be able to copy them, and the copy would report "successful," the odds are almost 100% that you wouldn't be able to attach the files (go ahead, test it with a "test DB")
Sounds like you're going in the right direction with this, too. You've got the "I'm not sure what I'm doing, but I know what I can look at doing, and where to go for help to get there." Take your time, if you're not sure something will do what you want try it on your test server or a stand-alone SQL install first, and take it slow.
Jason
June 26, 2014 at 8:59 am
One more thing to consider is that if you're taking log backups every 30 minutes, and if you wanted to do a point-in-time restore to 17.15, say, and your last full backup was at midnight (for example) you would have to restore quite a lot of files;
- FULL backup 12.00
- T log 12.30
- T log 01.00
- T log 01.30
- T log 02.00
- T log 02.30
- T log 03.00
- T log 03.30
- T log 04.00
- T log 04.30
- T log 05.00
- T log 05.30
- T log 06.00
- T log 06.30
- T log 07.00
- T log 07.30
- T log 08.00
- T log 08.30
- T log 09.00
- T log 09.30
- T log 10.00
- T log 10.30
- T log 11.00
- T log 11.30
- T log 12.00
- T log 12.30
- T log 13.00
- T log 13.30
- T log 14.00
- T log 14.30
- T log 15.00
- T log 15.30
- T log 16.00
- T log 16.30
- T log 17.00
- T log 17.30
Are you comfortable having to restore that many files if you have to? It's good if you are, but one alternative consideration might be to take a differential backup at, say, 12 noon, and then if you wanted to restore to 17.15, you'd only have to restore;
- FULL 12 midnight
- Differential 12 noon
- T log 12.30
- T log 13.00
- T log 13.30
- T log 14.00
- T log 14.30
- T log 15.00
- T log 15.30
- T log 16.00
- T log 16.30
- T log 17.00
- T log 17.30
which is still a lot of files, but a bit more manageable.
(Sorry if I'm stating the obvious and you already know this!)
June 26, 2014 at 9:06 am
I am not against differentials and implementing it into our dr plan. I am prepared to restore that many files, at least until I can find or build a script that will run through each file.
June 26, 2014 at 9:31 am
Factory909 (6/26/2014)
I am not against differentials and implementing it into our dr plan. I am prepared to restore that many files, at least until I can find or build a script that will run through each file.
Since the restores of the log files are fairly simple and mostly auto-magic, I have to admit that I just don't bother with differentials and wouldn't even if my T-Log backups were every 5 minutes.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2014 at 9:36 am
I actually don't use them either, but I thought it might be useful to mention it while the OP is considering options.
June 26, 2014 at 3:59 pm
Beatrix Kiddo (6/26/2014)
One more thing to consider is that if you're taking log backups every 30 minutes, and if you wanted to do a point-in-time restore to 17.15, say, and your last full backup was at midnight (for example) you would have to restore quite a lot of files;-- snip --
Are you comfortable having to restore that many files if you have to? It's good if you are, but one alternative consideration might be to take a differential backup at, say, 12 noon, and then if you wanted to restore to 17.15, you'd only have to restore;
-- snip --
which is still a lot of files, but a bit more manageable.
(Sorry if I'm stating the obvious and you already know this!)
I believe using SSMS will automate all this if the file locations haven't changed from when they were backed-up. Alternatively a query could be written easily enough using msdb.dbo.backupset and msdb.dbo.backupmediafamily to build a Restore script from the last Full Backup to restore it along with all Diff and Log files. Now if your database server is unavailable this isn't an option, but in a DR situation where the System databases were restored MSDB could be queried to get this information given the backups were made available via network or cloud share. This is one scenario we test anyway.
Sam
June 26, 2014 at 4:15 pm
That brings me to my next question. Should I be backing up my system db's as well?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply