This is not a particularly well-written piece of software, but it does serve a purpose – if you’ve seen me use it in a demo and want to play around with it, here it is…
There has been some interest expressed in my script for automated backup testing. I, rather glibly, said in my last post that I could share the scripts with you. That was before I took another good look at them, however. It turns out that my solution is integrated rather heavily into my database maintenance solution which is many years old and probably not as good as Ola Hallengren’s – the only reason I still use it is that I wrote it before I knew of Ola’s and it’s not broken…
I have extracted the main functionality and post it here as a starting point for you. It’s possible that I could further develop this into more of a solution that you can just install and use, but for now, here is something that should get you started.
The main limitation of this script is that it pulls database backup information from MSDB and then executes RESTORE – all in the same stored procedure, which means you’d be doing your test restores on your production server. If you happen to have the capacity (both space and resource capacity) to do this, it will work just fine, but you probably want to run your tests on some server other than production. This means that your test server would have to find some other way to get the backup information – perhaps it can monitor a file share where the backups show up, or perhaps it could reach through a linked server. Perhaps a powershell script is more suitable for this sort of thing once multiple servers are involved. In any event, the multiple server question makes this a whole different problem (one which I have solved with one client, by the way, but the solution is very specific and it would simply be too much work to generalize it for a blog post like this). The logic in the script here and this discussion should give you enough to go in the right direction if you need to do this – or you could always contract me
For now, here is the script – as the header says, please come back to this post and comment if you find issues with it, have updates to suggest or just have something to say about it.
Note that while I have done some work to make sure this script does not do anything exceptionally stupid, it *does* run the DROP DATABASE command – it is probably possible to configure it to do some real damage, so please be careful – consider yourself warned.
Hello – just a quick post today to remind you to test your backups. Don’t just look to see if the file gets created and copied offsite, actually restore it somewhere and run CheckDB on it.
I have an agent job that does this for me every day for some of my clients. It might seem anal, but you’d want to automate it anyway, and once it is set up, it just runs and you can forget about it (more or less) - at least until you get an alert that it failed, which happened to me this morning. When I looked into it, I found that RESTORE simply errors out when trying to read the file that BACKUP wrote only hours before. BACKUP reported no errors when writing the file, and as far as it was concerned, the backup was done, but RESTORE just barfed – the file is no good. If I hadn’t been testing my backups, I would never have known my backup was no good unless I needed it which, I think we can agree, is a *really* bad time to find something like that out.
So what happened to the backup? What was wrong with it? I could probably look into it and figure it out, but much like Neo’s ability to dodge bullets in the Matrix, it’s much better if I don’t have to… I caught it right away and took another backup (which, of course, I immediately tested). If it happens more than once in a blue moon, I’ll look into it, but this one I’ll just delete and chalk up to an anomaly. I’m a happy DBA and my client’s data is safe for another day.
I am not aware of any tools that automate backup testing for you, although I admit that I haven’t really looked into it. The script I wrote is pretty straightforward and I can share it with you – just contact me
Here are the session materials for the Index-fu talk I gave at SDEC13 on Oct 23. I hope you enjoyed the session.
Here are the session materials for the Winnipeg SQL Server User group meeting on May 15
I hope you enjoyed the session and I hope to see you at a future event.
We often work with Anthony, Pierre and Mitch, the evangelists from the IT Pro team at Microsoft Canada. They asked us to share this important message with you.
The team at Microsoft Canada is focused on ensuring that they help set you up for success by providing the information and tools you need in order to be get the most out of Microsoft based solutions, at home and at work.
Twice a year, Microsoft sends out the Global Relationship Study (GRS for short); it’s a survey that Microsoft uses to collect your feedback and help inform their planning. If you receive emails from Microsoft, subscribe to their newsletters‚ or you’ve attended our any of their events you may receive the survey.
The important details:
- Timing – March 4th to April 12th 2013
- Sent From – “Microsoft Feedback”
- Email Alias – “feedback@e–mail.microsoft.com”
- Subject Line – “Help Microsoft Focus on Customers and Partners”
Many of you already read the Microsoft Canada IT Pro team’s blogs‚ connect with them on LinkedIn and have attended their events in the last year or so. So you may already know that you’re their top priority. So they
want to hear from you.
Pierre, Anthony and Mitch use the GRS results to shape what they do, how they do it and if it’s resonating with you. Tell them what you need to be the “go-to” guy (or gal). Tell them what you need to grow your career. They want you to be completely satisfied with Microsoft Canada.
This year, Pierre, Anthony and Mitch have delivered 30 IT Camps and counting across the country. Giving you the opportunity to get hands on and learn how to get the most value for your organization. They have a few more events planned this year, so keep an eye on their plancast feed for events near you. Based on your feedback, topics they’re planning to cover will include:
- Windows 8
- Windows Server 2012
- System Center 2012
- Private Cloud
- BYOD – Management and Security
That’s not all. They’ve heard you loud and clear so in addition to hands on events, they’re also delivering more technical content online via the IT Pro Connection Blog. Windows 8 continues to be a big area of focus for them. They covered a lot of great content at launch and they’ve complimented that with new content like:
In addition to this, there are some valuable online resources you can use like Microsoft Virtual Academy, Microsoft’s no-cost online training portal. Or software evaluations (free trials) on TechNet that allow you to build your own labs to try out what you’ve learned.
Regardless of how you engage with the team at Microsoft Canada‚ you’d probably agree that they hear you. They’d also encourage you to continue to provide that great feedback. They thrive on it‚ they relish it‚ they wallow in it and most importantly of all‚ they action it. So please keep connecting with them and keep it coming! Pierre, Anthony and Mitch are listening.
Resources, Tools and Training
Tim Horton’s Gift Card Contest
– We’re giving away 350 Tim Horton’s gift cards, all you have to do to qualify is download a free qualifying software evaluation (trial). Download all three for more chances to win, but hurry, the contest closes soon.*
Windows 8 Resource Guide
- Download a printable, one-page guide to the top resources that will help you explore, plan for, deploy, manage, and support Windows 8 as part of your IT infrastructure.
Windows Server 2012 Evaluation – Get hands on with Windows Server 2012 and explore the scale and performance possibilities for your server virtualization.
Microsoft Support – Get help with products‚ specific errors‚ virus detection and removal and more.
Microsoft Licensing -Visit the Volume Licensing Portal today to ask questions about volume licensing‚ get a quote‚ activate a product or find the right program for your organization.
*No purchase necessary. Contest open to residents of Canada, excluding Quebec. Contest closes April 11, 2013 at 11:59:59 p.m. ET. Three-Hundred-and-Fifty (350) prizes are available to be won: (i) $10 CDN Tim Horton’s gift card. Skill-testing question required. Odds of winning depend on the number of eligible entries. For full rules, including entry, eligibility requirements and complete prize description, review the full terms and Conditions.
I will be speaking at SQL Saturday #166 in Olympia, WA on November 10. I will be doing my beginner talk on Indexes and Query plans. I am looking forward to it as my first presentation outside of Canada.
SQL Saturdays are free one-day events held all over the world and are part of the great offerings of the Professional Association for SQL Server (www.sqlpass.org). Take a look at the SQL Saturday website (www.sqlsaturday.com) for an event near you and try to make it out to Olympia next Saturday.
I hope to see a familiar face or two!
Hey everybody – the next version of SQL server is going to be available on April 1. Despite being April fool’s day, this release is no joke. With features like AlwaysOn Availability groups and Columnstore indexes, this is a major release. My favourite new thing is the enhancements to intellisense, but I can be strange that way…
There is a whole lot to get up to speed on for this release, and an excellent, fun and potentially rewarding way to do that would be to register for and log into the SQL Server 2012 launch event – it kicks off with live keynotes tomorrow (Wednesday, March 7). Learn stuff, ask live experts questions and win prizes.