This months T-SQL Tuesday is about automation and I thought I’d write about extending existing automation. One of my favorite scripts for automation is Ola Hallengren’s Backup & Maintenance solution. Ola’s scripts are a fantastic way to automate highly configurable backups and maintenance on your SQL Server instances. If you’re not using them, you should seriously consider looking into why.
This solution serves as an outstanding base but like anything else its can be useful to tweak things a bit. Extending the initial automation provided by his scripts is what this post is all about.
In particular, I’ve modified Ola’s scripts to generate the files needed to restore all of the databases that have been backed up with his solution. In particular, having the ability to easily restore the whole server in the case of a disaster. Though, you could easily pull out one DB to only restore it. This script is currently only written for litespeed since that’s what I use for backups. However, it could easily be changed to support native backups or any of the other backup products that Ola’s scripts can be configured for. Perhaps Ill work on those in the future if it would be useful.
The idea is that every time you take a backup the backup job will create a .sql file on the server filesystem in the backup directory that can be used to restore to the point of the backups that were just taken.
This solution includes three pieces, an additional stored procedure, an additional step in both of the backup jobs to execute that stored procedure and lastly a step in the cleanup procedure to remove the restore scripts from the filesystem that have aged.
A couple of notes of caution:
As with anything you find on the internet, please use at your own risk in a development/test system and proceed with caution.
This script makes several assumptions including
- That you’ve installed Ola’s commands into the master database
- That you’re using litespeed
- That logging to the commandlog table is enabled
The stored procedure is relatively simple and accepts a single parameter @type “LOG” will generate the script as of the last log backup taken or for any other parameter, I happen to use “FULL”, it generates the script based on the last full backup.
CREATE PROCEDURE [dbo].[GenerateRestoreScript] (@type NCHAR(30) = 'LOG') AS DECLARE @ID INT DECLARE @DB NVARCHAR(128) SET NOCOUNT ON SELECT @ID = MAX(database_id) FROM sys.databases IF @type = 'LOG' SET @type = 'xp_backup_log' ELSE SET @type = '' --These intentionally not commented in the script as a precaution (to generate an error) SELECT 'ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-ALERT-' SELECT 'PLEASE BACKUP THE TAIL OF THE LOG SELECT 'OTHERWISE THIS COULD BECOME A RGE (GOOGLE THE ACRONYM!)' SELECT 'IF YOU ARE OK REPLACING THE DB AND LOOSING DATA IN THE TAIL LOG RUN THIS SCRIPT WITHOUT THESE COMMENTS ' SELECT 'RAISERROR(N''ARE YOU SURE YOU WANT TO DO THIS?'', 25,1) WITH Log;' SELECT '--------' WHILE @ID > 2 BEGIN SELECT @DB = NAME FROM sys.databases WHERE database_id = @ID SELECT @ID = @ID - 1 SELECT '----' + @DB + '-----------------------------------------------------' SELECT 'EXECUTE ' + REPLACE(Command, '_backup_', '_restore_') + ', @filenumber = 1, @with = N''' + CASE WHEN rn <> 1 THEN 'NO' ELSE '' END + 'RECOVERY''' + CASE WHEN CommandType = 'xp_backup_database' THEN ', @with = N''REPLACE'';' ELSE ';' END FROM ( SELECT SUBSTRING(LEFT (Command, CHARINDEX(''', @with =',Command)),CHARINDEX('[master]',Command),LEN(Command)) AS Command , ROW_NUMBER() OVER (ORDER BY cl.ID DESC) AS rn , CommandType FROM [master].[dbo].[CommandLog] cl WHERE cl.DatabaseName = @DB AND (cl.CommandType = 'xp_backup_database' OR cl.CommandType = @type) AND cl.ID >= ( SELECT MAX(ID) FROM CommandLog c WHERE CommandType IN ( 'xp_backup_database' ) AND cl.DatabaseName = c.DatabaseName ) ) AS rntab ORDER BY rn DESC END
To execute the stored procedure, this needs to be added as an additional cmdexec job step to the Full backup job (make sure to change the directory where you want the .sql files stored (H:\SERVERNAME below))
sqlcmd -l 30 -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -y 0 -b -Q "EXEC [dbo].[GenerateRestoreScript] ''FULL''" –o”H:\SERVERNAME\DRFULL_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))_RESTORE.sql" –w50000
To execute the stored procedure, this needs to be added as an additional cmdexec job step to the Transaction log backup job (make sure to change the directory where you want the .sql files stored (H:\SERVERNAME below))
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -y 0 -b -Q "EXEC [dbo].[GenerateRestoreScript]" -o"H:\SERVERNAME\DRLOG_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))_RESTORE.sql" –w50000
This cmdexec Job step that needs to be added to the output file cleanup job to clean up old .sql files (make sure to change the directory where the .sql files stored (H:\SERVERNAME below))
Note: currently this configuration keeps the files from the past 3 days but the actual files kept depends on when the cleanup job is scheduled.
cmd /q /c "For /F "tokens=1 delims=" %v In (''ForFiles /P "H:\SERVERNAME" /m *RESTORE.sql /d -3 2^>^&1'') do if EXIST "H:\SERVERNAME"\%v echo del "H:\SERVERNAME"\%v& del "H:\SERVERNAME"\%v"
I have these steps scripted into Ola’s original solution .sql so the folder names are set properly and job creation is completely automated. Ill leave that part of extending automation to you, dear reader, as homework.
Since SQL Server 2005 we’ve been able to grant one login the ability to impersonate another in order to have a different set of effective permissions. More than a few times I’ve found this paradigm useful in order to solve problems while adhering to the principal of least privilege and likewise more than a few times I’ve tried to find information about who has impersonation privileges in SSMS and have been severely disappointed to find that it is buried. This extremely simple script solves that problem and gives all of the information about who has impersonation rights on an instance.
If you want to read more about all the fantastic things you can do with EXECUTE AS and impersonation, MSDN is a good place to start
SELECT grantee_principal.name AS WhoCanImpersonate , grantee_principal.type_desc AS ImpersonatorType , sp.name AS WhoCanTheyImpersonate , sp.type_desc AS ImpersonateeLoginType FROM sys.server_permissions AS prmssn INNER JOIN sys.server_principals AS sp ON sp.principal_id = prmssn.major_id AND prmssn.class = 101 INNER JOIN sys.server_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id WHERE prmssn.state = 'G'
This years election results are officially in the books. Unfortunately I’ve come up four votes short in my effort to be elected.
I’d like to congratulate the winners, I’m sure y’all will do great things. Dont worry, we’ll all be watching to see you make good on those campaign promises!
I’d also like to thank all of my supporters, public and private. Your words helped me more than you’ll likely ever know.
Andy Warren in particular was abolutely critical in helping me strategize through this years campaign. I wouldn’t have made it this far without his support and encouragement over the years.
According to the published timelinethe 2013 iteration of the PASS Board elections will be over in just a few hours. As I wrap up this year’s campaign I wanted to send out a huge thank you to the SQL community members who have supported me in my endeavor to have a second term on the Board. I am humbled to have such a great and supporting group of friends and colleagues.
I hope that my writings have proven helpful by detailing my visions for PASS and that you have already chosen to select my name on the ballot. However, if you haven’t voted yet, or just haven’t seen enough of my ideas for making PASS a better place, please check out my elections page for plenty more information.
I’m Allen Kinsel, I hope you’ll vote for me and I approve this message
Happily, It’s Friday and as I was looking at all the digital bits I’ve spilled in this campaign, I realized there was one more topic I wanted to draw your quick attention to: Things I want to do better this time
- I need to remember that communication is key and telling you what I ‘m working on is at least as important as what I’m working on. In my first term, particularly towards the end, I sometimes sacrificed communication in order to get something done. You need to see what I’m doing (or not doing) and be able to give feedback if you think I’ve headed in the wrong direction.
- I want to get back to doing a better job of utilizing volunteers. Aligning work and volunteers is hard. As anyone who has lead a chapter, a SQLSaturday or any kind of volunteer effort know, it often feels easier and faster to just do things than to delegate things. I had good success with delegation in the program committee and am prepared to return to a similar model during this term. My success should not be measured by how much I do personally but rather by how much gets done within my portfolio.
- I want to think “International” every time I start on something new. Not because it’s the cool community buzzword but because I want PASS to serve everyone and I know I don’t have enough experience to know what will or won’t work in other countries.
That’s it for the week. Thanks for reading and thanks for voting! Have a great weekend!