Wednesday
Oct182017

Stored Procedure to Restore SQL Database

Note: requires sp_rcb_killtargetdbconns (below)

Whenever I'm restoring a SQL database I hate using the GUI and meticulously having to browse out to each file needed in the recovery effort. So I wrote a SPROC that takes a few parameters (i.e. the path to my backup files). Works great for seeding secondary Availability Group replicas or just recovering a database.

It's called sp_rcb_PitrRestore and you run it like this:

The full SPROC follows. As the comments allude to please note that the latest full backup in you @BackupsFolderFull value is the one you want to use in your restore. Also be sure to check that your filename suffixes (i.e. .BAK, .DIF, et cetera) match what's in the SPROC.

Tuesday
Jul162013

Kill connections for specified database

In non-production environments I'm frequently tearing down old databases and replacing them with new ones with the same name. Rather than kill all current connections manually I prefer to use this handy system stored procedure: Here's an example of usage where myDbName is the name of the target database that I intend to name to myDbName_OLD and need to terminate all active connections first.