"restoredb.rb" restores/refreshes MSSQL databases from data bag settings
# | |
# Cookbook Name:: smashrun | |
# Recdbe:: restoredb | |
# | |
# Copyright 2010, Smashrun, Inc. | |
# Author:: Steven Craig <support@smashrun.com> | |
# | |
# Licensed under the Apache License, Version 2.0 (the "License"); | |
# you may not use this file except in compliance with the License. | |
# You may obtain a copy of the License at | |
# | |
# http://www.apache.org/licenses/LICENSE-2.0 | |
# | |
# Unless required by applicable law or agreed to in writing, software | |
# distributed under the License is distributed on an "AS IS" BASIS, | |
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
# See the License for the specific language governing permissions and | |
# limitations under the License. | |
# | |
# http://msdn.microsoft.com/en-us/library/ms175477(v=SQL.90).aspx | |
# http://msdn.microsoft.com/en-us/library/ms190217(v=SQL.90).aspx | |
# http://msdn.microsoft.com/en-us/library/ms190440(v=SQL.90).aspx | |
# http://sqlpost.blogspot.com/2009/06/sql-server-working-with-lsn-for-restore.html | |
# | |
# todo: createdb needs to set flags on the databases for "refreshability" | |
log("begin restoredb.rb") { level :debug } | |
log("running restoredb.rb") { level :info } | |
# #{restoretype} will be "refresh" under most circumstances (if this is running on QA database) | |
# #{restoretype} would be "restore" if running on same server to attempt database recovery | |
restoretype = "#{node[:restoredb][:type]}" | |
# these are the databases to be refreshed / restored | |
["msdb","smashrun"].each do |database| | |
# lockfile is deleted every 24 hours (currently, only refresh once per day) | |
unless File.exists?("#{node[:restoredb][:sqlscripts]}\\#{restoretype}-#{database}-#{node[:restoredb][:basesql]}.log") | |
# ensure necessary dirs are setup and available | |
["#{node[:restoredb][:tempdir]}", "#{node[:restoredb][:sqlscripts]}", "#{node[:restoredb][:restoredir]}"].each do |dir| | |
# log("create #{dir} directory if necessary") { level :debug } | |
directory "#{dir}" do | |
action :create | |
not_if { File.exists?("#{dir}") } | |
recursive true | |
end | |
end | |
# always ensure templated batch file to execute transact-sql statements is up-to-date | |
log("create #{node[:restoredb][:sqlscripts]}\\#{node[:createdb][:executesql_bat]} if necessary") { level :debug } | |
template "#{node[:restoredb][:sqlscripts]}\\#{node[:createdb][:executesql_bat]}" do | |
source "#{node[:createdb][:executesql_template]}" | |
variables({ | |
:author_name => "#{node[:restoredb][:author_name]}", | |
:author_email => "#{node[:restoredb][:author_email]}", | |
:working_dir => "#{node[:restoredb][:sqlscripts]}", | |
:sqlcmd_path => "#{node[:createdb][:sqlcmd_path]}", | |
:sqlsausername => "#{node[:createdb][:sqlsausername]}", | |
:executesql_bat => "#{node[:createdb][:executesql_bat]}", | |
:executesql_template => "#{node[:createdb][:executesql_template]}" | |
}) | |
end | |
# delete the lockfile so that the refresh sql script will run afterwards | |
log("deleting postrefresh lock file") { level :debug } | |
execute "deletepostrefresh_lock" do | |
cwd "#{node[:restoredb][:sqlscripts]}" | |
only_if { ::File.exists?("#{node[:restoredb][:sqlscripts]}\\deploysql-#{node[:deploy][:tag]}.log") } | |
command %Q(del /S /Q #{node[:restoredb][:sqlscripts]}\\deploysql-#{node[:deploy][:tag]}.log) | |
timeout 30 | |
action :run | |
end | |
# organize restores | |
log("begin organize restores") { level :debug } | |
# two arrays to which we'll add the necessary database information | |
# running_database holds which databases run on which database hosts | |
# database_info holds location and file information | |
running_database = [] | |
database_info = [] | |
search(:running_database, "id:#{node[:hostname]}") do |host| | |
running_database << host["database"].split(",") | |
running_database.flatten! | |
running_database.each do |dbname| | |
search(:database_info, "id:#{dbname}") do |db| | |
dbinfo = { "id" => db["id"], | |
"dbname" => db["id"], | |
"backupdrive" => db["backupdrive"], | |
"backupdir" => db["backupdir"], | |
"collation" => db["collation"], | |
"comment" => db["comment"], | |
"datadrive" => db["datadrive"], | |
"datadir" => db["datadir"], | |
"datafile" => db["datafile"], | |
"datainitsize" => db["datainitsize"], | |
"datamaxsize" => db["datamaxsize"], | |
"owner" => db["owner"], | |
"restoredrive" => db["restoredrive"], | |
"restoredir" => db["restoredir"], | |
"transdrive" => db["transdrive"], | |
"transdir" => db["transdir"], | |
"transfile" => db["transfile"], | |
"transinitsize" => db["transinitsize"], | |
"transmaxsize" => db["transmaxsize"] | |
} | |
database_info << dbinfo | |
end | |
end | |
# log(database_info.inspect) {level :debug} | |
info = [] | |
database_info.each do |db| | |
if "#{database}" == db["id"] | |
info = { "id" => db["id"], | |
"dbname" => db["id"], | |
"backupdrive" => db["backupdrive"], | |
"backupdir" => db["backupdir"], | |
"collation" => db["collation"], | |
"comment" => db["comment"], | |
"datadrive" => db["datadrive"], | |
"datadir" => db["datadir"], | |
"datafile" => db["datafile"], | |
"datainitsize" => db["datainitsize"], | |
"datamaxsize" => db["datamaxsize"], | |
"owner" => db["owner"], | |
"restoredrive" => db["restoredrive"], | |
"restoredir" => db["restoredir"], | |
"transdrive" => db["transdrive"], | |
"transdir" => db["transdir"], | |
"transfile" => db["transfile"], | |
"transinitsize" => db["transinitsize"], | |
"transmaxsize" => db["transmaxsize"] | |
} | |
end | |
end | |
# master has no valid translogs and therefore must be "refresh"-type (full restore only) | |
if "#{database}" =~ ( /master/ ) | |
restoretype = "refresh" | |
end | |
# set the hour depending on restoretype and then only unzip the necessary translogs | |
if "#{restoretype}" =~ /restore/ | |
time = Time.new | |
# throw away the most recent translog - we are unsure when precisely we are running | |
hour = (time.hour - 1) | |
log("unzip #{database} translogs") { level :debug } | |
(0..hour).each { |hr| | |
execute "unzipdbbackup_#{database}-#{hr}" do | |
cwd "#{node[:createdb][:restoredir]}" | |
timeout 30 | |
command %Q(#{node[:sevenzip][:installdir]}\\7z.exe x -y -tzip -o#{node[:createdb][:restoredir]} #{node[:createdb][:restoredir]}\\PDB001-trans-#{database}-#{hr}.zip) | |
# not_if { ::File.exists?("#{node[:createdb][:restoredir]}\\trans-#{database}-#{hr}.bak") } | |
end | |
} | |
else | |
hour = 0 | |
end | |
# always unzip the full database restore | |
log("unzip #{database} full backup") { level :debug } | |
execute "unzipdbbackup_#{database}" do | |
cwd "#{node[:createdb][:restoredir]}" | |
timeout 300 | |
command %Q(#{node[:sevenzip][:installdir]}\\7z.exe x -y -tzip -o#{node[:createdb][:restoredir]} #{node[:createdb][:restoredir]}\\PDB001-full-#{database}-0.zip) | |
# not_if { ::File.exists?("#{node[:createdb][:restoredir]}\\full-#{database}-0.bak") } | |
end | |
# use transact-sql according to restore type | |
# ensure transact-sql scripts to restoredb are current and execute restoredb if necessary | |
templated = nil | |
begin | |
templated = resources(:template => "#{node[:restoredb][:basesql]}.erb") | |
rescue Chef::Exceptions::ResourceNotFound | |
templated = template "#{node[:restoredb][:sqlscripts]}\\#{restoretype}-#{database}-#{node[:restoredb][:basesql]}" do | |
source "restoredb-#{node[:restoredb][:basesql]}.erb" | |
cookbook "smashrun" | |
backup false | |
variables({ | |
:hour => "#{hour}", | |
:restoretype => "#{restoretype}", | |
:dbname => "#{database}", | |
:backupdrive => info["backupdrive"], | |
:backupdir => info["backupdir"], | |
:restoredrive => info["restoredrive"], | |
:restoredir => info["restoredir"], | |
:author_name => "#{node[:restoredb][:author_name]}", | |
:author_email => "#{node[:restoredb][:author_email]}", | |
:basesql => "#{restoretype}-#{database}-#{node[:restoredb][:basesql]}", | |
:basesql_template => "#{restoretype}-#{database}-#{node[:restoredb][:basesql]}.erb" | |
}) | |
end | |
end | |
# prior to refresh, must place DBs into single user mode - this will bring down the website | |
# run refresh on current database | |
execute "#{restoretype}-#{node[:createdb][:executesql_bat]}" do | |
cwd "#{node[:restoredb][:sqlscripts]}" | |
command "#{node[:restoredb][:sqlscripts]}\\#{node[:createdb][:executesql_bat]} SQL-SAPASSWD #{restoretype}-#{database}-#{node[:restoredb][:basesql]} #{restoretype}-#{database}-#{node[:restoredb][:basesql]}" | |
# not_if { File.exists?("#{node[:restoredb][:sqlscripts]}\\#{restoretype}-#{database}-#{node[:restoredb][:basesql]}.log") } | |
timeout 300 | |
end | |
end | |
end | |
end | |
log("end restoredb.rb") { level :info } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment