Explaining SQL Server in plain english
20 minute video of me setting up and demonstrating a point-in-time database restore in SQL Server.
If you’ve ever been on a SQL DBA interview, more than likely you’ve been asked about this. I was recently, and I’ve been a DBA for 18 years…it never goes away. But, the reason for that is that many DBAs know the concept, but have never actually needed to do one.
Its a basics video, so please don’t be looking for Paul Randal level internals here 🙂
Thanks for watching!
I will be presenting my session “Backups for non-DBAs….the Why, not the How” at SQL Saturday Houston on June 17, 11am.
This is mostly targeted at non-DBAs such as owners, managers, CxOs, developers and sysadmins…but all are more than welcome.
We will be discussing various scenarios to consider as part of an overall DR/Business Continuity plan, as well as sharing a few horror stories from the field…both ancient and current.
The session abstract:
Join me and get some new perspective on what your DBA is doing, and why!
Targeted at the non-DBA such as BI/DW folks, Application Developers, Managers, and System Admins, this session will go over the reasons we back up our databases, our systems, etc. Considerations such as Single points of failure, High Availability/Disaster recovery, Business Continuity and others will be discussed in this interactive conversation. It will be conversation heavy, with supporting slides to download, and one Database backup demo at the end if time permits.
Everyone involved in a technical role needs to at least know that the things they have created will be recoverable in the event of a disaster, or even just an “oops” moment. The CIO/CTO should know how long critical systems will be down when bad things happen. Backups are everyone’s responsibility…whether asking the right questions or implementing the process.
I look forward to seeing you there!
We had a major upgrade to our main application recently, so I thought I would share some of the “thoughts from the aftermath.”
The good: We gave the application back to the business 1/2 of a business day earlier than expected. Win.
The bad: We were hoping to give it back a full day earlier
The ugly: we wasted a full day having to rollback and start over
Some lessons learned:
Those are the big items.
This post inspired by Tim Mitchell‘s post today : Studying Failures
Feel free to share your deployment/upgrade brilliance or derp moments in the comments below.
Kevin3NF
We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.
The catch is that there are close to 300 identical databases involved.
This is easy enough to do if you know where info is stored in MSDB related to the last backup.
I’ll just leave the code I used right here for your amusement:
Use MSDB;
go
--generate the list
Select
max(backup_start_date) as Backup_Date,
database_name,
Max(physical_device_name) as physical_device_name
Into
#lastFullBackup
From
msdb..backupset bs
join msdb..backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
Where 1=1
and backup_start_date > getdate() -1
and type = 'D'
Group By
database_name
Order By
database_name
--Create the restore commands
Select
'restore database ['+database_name+'] From disk = ''' +physical_device_name+''' With replace, recovery'
From
#lastFullBackup
Where
database_name like '%MyDB%[0-9]' -- database1, database2, etc.
--Cleanup
Drop table #lastFullBackup
There are probably much easier or prettier ways to do this, but I’m only 2 cups of coffee in thus far, and one of them got cold…
If any of the above doesn’t make sense, please drop a comment. It works on my system and should be valid for SQL 2000 – 2016…the system tables referenced have not changed.
Have a bright, shiny day full of puppies or muscle cars….whichever you prefer!