SQL Archives - DallasDBAs.com

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:

Full backups ahead of time are a great idea – check
Restore scripts for those backups are a great idea – check
Using SQLCMD to apply vendor .sql files is a great idea – check
Forgetting the -I parameter in SQLCMD is a bad idea (not -i, but -I for Quoted Identifiers, which is false by deafult in SQLCMD, but True in SSMS)
Not having a full setup lab environment to test everything – bad
Using the default settings in SSDT Schema Compare – bad
Re-ordering column order in tables for cosmetic reasons – bad (this one is on the vendor…we had to re-write some code that broke)
KrispyKreme donuts in the war room followed by a huge box of cupcakes – bad. Didn’t affect the upgrade but I ate too much 😉

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!

Kevin3NF

SQL

Analyzing an Upgrade after the fact

Restore all databases at once