Module: SqlCmd

Defined in:
lib/sql_cmd/agent.rb,
lib/sql_cmd/azure.rb,
lib/sql_cmd/query.rb,
lib/sql_cmd/config.rb,
lib/sql_cmd/format.rb,
lib/sql_cmd/backups.rb,
lib/sql_cmd/database.rb,
lib/sql_cmd/security.rb,
lib/sql_cmd/always_on.rb

Defined Under Namespace

Modules: Agent, AlwaysOn, Azure, Database, Security

Class Method Summary collapse

Class Method Details

.apply_log_level ⇒ Object 



56
57
58
59
 
# File 'lib/sql_cmd/config.rb', line 56

def apply_log_level
  config_level = config['logging']['level']
  EasyIO.logger.level = EasyIO.levels[config_level]
end

.assign_database_roles(connection_string, database_name, user, roles = ['db_owner'], retries: 3, retry_delay: 5) ⇒ Object

roles: array of database roles



301
302
303
304
305
306
307
308
 
# File 'lib/sql_cmd/query.rb', line 301

def assign_database_roles(connection_string, database_name, user, roles = ['db_owner'], retries: 3, retry_delay: 5) # roles: array of database roles
  values = { 'databasename' => database_name, 'user' => user, 'databaseroles' => roles.join(',') }
  sql_script = ::File.read("#{sql_script_dir}/Security/AssignDatabaseRoles.sql")
  EasyIO.logger.info "Assigning #{roles.join(', ')} access to [#{user}] for database [#{database_name}]..."
  result = execute_query(connection_string, sql_script, return_type: :first_row, values: values, retries: retries, retry_delay: retry_delay)
  raise "Failed to assign SQL database roles for user: [#{user}]!" if result.nil? || result['name'].nil?
  result
end

.azure_blob_storage_list_backup_files(backup_url, backup_basename, storage_account_name, storage_access_key) ⇒ Object

Get a list of backup files from blob storage



51
52
53
54
55
 
# File 'lib/sql_cmd/backups.rb', line 51

def azure_blob_storage_list_backup_files(backup_url, backup_basename, , storage_access_key)
  files = SqlCmd::Azure::AttachedStorage.list(, storage_access_key, storage_url: backup_url, filename_prefix: backup_basename)
  base_url = backup_url[%r{[a-z]+://[^/]+/[^/]+}i] # URL without directories
  files.map { |f, properties| ["#{base_url}/#{f}", properties[:last_modified]] }.to_h
end

.azure_blob_storage_url?(backup_url) ⇒ Boolean

Returns:

  • (Boolean) 


45
46
47
48
 
# File 'lib/sql_cmd/backups.rb', line 45

def azure_blob_storage_url?(backup_url)
  return false if backup_url.nil? || backup_url.empty?
  (backup_url =~ /blob\.core\.windows\.net/i) >= 0
end

.backup_basename_from_path(backup_path) ⇒ Object

get the basename of the backup based on a full file_path such as the SQL value from [backupmediafamily].



88
89
90
91
 
# File 'lib/sql_cmd/format.rb', line 88

def backup_basename_from_path(backup_path)
  return nil if backup_path.nil? || backup_path.empty?
  ::File.basename(backup_path).gsub(/(\.part\d+)?\.(bak|trn)/i, '')
end

.backup_fileset_names(backup_files) ⇒ Object

Returns a single string to be used for the source for a RESTORE command from an array of backup file paths



104
105
106
107
108
109
110
111
112
113
 
# File 'lib/sql_cmd/backups.rb', line 104

def backup_fileset_names(backup_files)
  result = ''
  backup_files.each do |backup_file|
    backup_type = backup_file =~ /^http/i ? 'URL' : 'DISK'
    current_file = " #{backup_type} = N''#{backup_file}'',"
    current_file = backup_type =~ /url/i ? current_file.tr('\\', '/') : current_file.tr('/', '\\')
    result << current_file
  end
  result.chomp(',')
end

.backup_location_and_basename(start_time, connection_string, database_name, options = {}, backup_folder: nil, backup_url: nil, backup_basename: nil) ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/sql_cmd/backups.rb', line 57

def backup_location_and_basename(start_time, connection_string, database_name, options = {}, backup_folder: nil, backup_url: nil, backup_basename: nil)
  start_time = SqlCmd.unify_start_time(start_time)
  database_info = SqlCmd::Database.info(connection_string, database_name)
  server_settings = get_sql_server_settings(connection_string)
  backup_type = backup_url.nil? || backup_url.empty? ? 'DISK' : 'URL'
  if database_info['DatabaseNotFound']
    backup_basename = "#{database_name}_#{EasyTime.yyyymmdd(start_time)}"
    return [backup_url, backup_basename] if backup_type == 'URL'
    backup_unc_location = SqlCmd.config['sql_cmd']['backups']['backup_to_host_sql_server'] ? "\\\\#{server_settings['ServerName']}\\#{SqlCmd.config['sql_cmd']['backups']['default_backup_share']}" : SqlCmd.config['sql_cmd']['backups']['default_destination']
    return Dir.glob("#{backup_unc_location}/#{backup_basename}*".tr('\\', '/')).empty? ? [nil, nil] : [backup_unc_location, backup_basename]
  end

  backup_file_path = database_info['BackupFileLocation']
  if backup_file_path =~ /^\{.*\}$/
    backup_basename ||= "#{database_name}_#{EasyTime.yyyymmdd(start_time)}"
    backup_files, backup_basename = SqlCmd::Database.existing_backup_files(server_settings, options, backup_folder: backup_folder, backup_url: backup_url, backup_basename: backup_basename, log_only: options['logonly'])
    backup_file_path = backup_files.first
  end
  backup_file = ::File.basename(backup_file_path)
  backup_basename = backup_basename_from_path(backup_file)
  return [nil, backup_basename] if backup_type == 'URL'
  backup_unc_location = to_unc_path(::File.dirname(backup_file_path), server_settings['ServerName'])
  backup_folder = if ::File.exist?("#{backup_unc_location}/#{backup_file}")
                    backup_unc_location
                  elsif ::File.exist?("\\\\#{server_settings['ServerName']}\\#{SqlCmd.config['sql_cmd']['backups']['default_backup_share']}\\#{backup_file}")
                    "\\\\#{server_settings['ServerName']}\\#{SqlCmd.config['sql_cmd']['backups']['default_backup_share']}"
                  end
  return [nil, nil] unless defined?(backup_folder)
  [backup_folder, backup_basename]
end

.backup_sets_from_backup_files(backup_files) ⇒ Object 



228
229
230
231
232
233
234
235
236
 
# File 'lib/sql_cmd/backups.rb', line 228

def backup_sets_from_backup_files(backup_files)
  backup_sets = {}
  backup_files.each do |file|
    current_basename = ::File.basename(file).sub(/(\.part\d+)?\.(bak|trn)$/i, '') # determine basename of current file
    backup_sets[current_basename] = [] if backup_sets[current_basename].nil?
    backup_sets[current_basename].push(file)
  end
  backup_sets
end

.backup_sets_from_unc_path(sql_server_settings, backup_folder, backup_basename, log_only: false, database_backup_header: nil, restored_database_lsn: nil) ⇒ Object

sql_server_settings can be for any server that has network access to these files



89
90
91
92
93
94
 
# File 'lib/sql_cmd/backups.rb', line 89

def backup_sets_from_unc_path(sql_server_settings, backup_folder, backup_basename, log_only: false, database_backup_header: nil, restored_database_lsn: nil)
  backup_files, backup_basename = get_unc_backup_files(sql_server_settings, backup_folder, backup_basename, log_only: log_only, all_time_stamps: log_only)
  backup_sets = log_only ? relevant_log_backup_sets(sql_server_settings, backup_files, database_backup_header, restored_database_lsn) : { backup_basename => backup_files }
  EasyIO.logger.debug "Database backup sets found: #{JSON.pretty_generate(backup_sets)}"
  backup_sets
end

.backup_sets_from_url(sql_server_settings, backup_url, backup_basename, options, log_only: false, database_backup_header: nil, restored_database_lsn: nil) ⇒ Object 



96
97
98
99
100
101
 
# File 'lib/sql_cmd/backups.rb', line 96

def backup_sets_from_url(sql_server_settings, backup_url, backup_basename, options, log_only: false, database_backup_header: nil, restored_database_lsn: nil)
  backup_files, backup_basename = get_url_backup_files(sql_server_settings, backup_url, backup_basename, options, log_only: log_only, all_time_stamps: log_only)
  backup_sets = log_only ? relevant_log_backup_sets(sql_server_settings, backup_files, database_backup_header, restored_database_lsn) : { backup_basename => backup_files }
  EasyIO.logger.debug "Database backup sets found: #{JSON.pretty_generate(backup_sets)}"
  backup_sets
end

.check_header_date(sql_backup_header, backup_start_time, messages = :none) ⇒ Object

messages:

:none
:prebackup || :prerestore - Output info messages pertaining to a backup or restore

returns:

:current
:outdated
:nobackup



267
268
269
270
271
272
273
274
275
276
277
278
279
280
 
# File 'lib/sql_cmd/backups.rb', line 267

def check_header_date(sql_backup_header, backup_start_time, messages = :none)
  backup_start_time = SqlCmd.unify_start_time(backup_start_time)
  return :nobackup if sql_backup_header.nil? || sql_backup_header.empty?
  EasyIO.logger.info "Last backup for [#{sql_backup_header['DatabaseName']}] completed: #{sql_backup_header['BackupFinishDate']}" if [:prebackup, :prerestore].include?(messages)
  backup_finish_time = sql_backup_header['BackupFinishDate']
  raise "BackupFinishDate missing from backup header: #{sql_backup_header}" if backup_finish_time.nil?
  if backup_finish_time > backup_start_time
    EasyIO.logger.info 'Backup is current. Bypassing database backup.' if messages == :prebackup
    EasyIO.logger.info 'Backup is current. Proceeding with restore...' if messages == :prerestore
    :current
  else
    :outdated
  end
end

.compress_all_tables(connection_string) ⇒ Object 



333
334
335
336
337
338
339
340
341
342
343
344
345
 
# File 'lib/sql_cmd/query.rb', line 333

def compress_all_tables(connection_string)
  uncompressed_count_script = ::File.read("#{sql_script_dir}/Status/UncompressedTableCount.sql")
  compress_tables_script = ::File.read("#{sql_script_dir}/Database/CompressAllTables.sql")
  EasyIO.logger.info 'Checking for uncompressed tables...'
  uncompressed_count = execute_query(connection_string, uncompressed_count_script, return_type: :scalar, retries: 3)
  if uncompressed_count > 0
    EasyIO.logger.info "Compressing #{uncompressed_count} tables..."
    execute_query(connection_string, compress_tables_script)
    EasyIO.logger.info 'Compression complete.'
  else
    EasyIO.logger.info 'No uncompressed tables.'
  end
end

.config ⇒ Object 



4
5
6
 
# File 'lib/sql_cmd/config.rb', line 4

def config
  @config ||= EasyJSON.config(defaults: defaults, required_keys: required_keys)
end

.connection_string_accessible?(connection_string, suppress_failure: true, retries: 3, retry_delay: 5) ⇒ Boolean

Returns:

  • (Boolean) 


155
156
157
158
159
160
161
 
# File 'lib/sql_cmd/query.rb', line 155

def connection_string_accessible?(connection_string, suppress_failure: true, retries: 3, retry_delay: 5)
  sql_script = 'SELECT @@SERVERNAME AS [ServerName]'
  !execute_query(connection_string, sql_script, return_type: :scalar, retries: retries, retry_delay: retry_delay).nil?
rescue
  raise unless suppress_failure
  false
end

.connection_string_credentials_from_hash(**credentials_hash) ⇒ Object 



93
94
95
96
97
98
 
# File 'lib/sql_cmd/format.rb', line 93

def connection_string_credentials_from_hash(**credentials_hash)
  credentials_hash = Hashly.symbolize_all_keys(credentials_hash.dup)
  windows_authentication = credentials_hash[:windows_authentication]
  windows_authentication = credentials_hash[:user].nil? || credentials_hash[:password].nil? if windows_authentication.nil? # If windows authentication wasn't specified, set it if user or pass is nil
  windows_authentication ? 'integrated security=SSPI;' : "user id=#{credentials_hash[:user]};password=#{credentials_hash[:password]}"
end

.connection_string_from_hash(**connection_hash) ⇒ Object

generates a connection string from the hash provided. Example hash: { ‘server’ => ‘someservername’, ‘database’ => ‘somedb’, ‘user’ => ‘someuser’, ‘password’ => ‘somepass’, ‘windows_authentication’ => false }



101
102
103
104
105
106
107
 
# File 'lib/sql_cmd/format.rb', line 101

def connection_string_from_hash(**connection_hash)
  connection_hash = Hashly.symbolize_all_keys(connection_hash.dup)
  credentials_segment = connection_string_credentials_from_hash(**connection_hash)
  database_name = connection_hash[:database]
  database_segment = database_name.nil? || database_name.strip.empty? ? '' : "database=#{database_name};"
  "server=#{connection_hash[:server]};#{database_segment}#{credentials_segment}"
end

.connection_string_part(connection_string, part, value_only: true) ⇒ Object

options: :server, :database, :credentials, :readonly



23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/sql_cmd/format.rb', line 23

def connection_string_part(connection_string, part, value_only: true) # options: :server, :database, :credentials, :readonly
  raise 'Connection string provided is nil or empty!' if connection_string.nil? || connection_string.empty?
  case part
  when :server, :database, :applicationintent
    connection_string[connection_string_part_regex(part)]
  when :user, :password
    credentials = connection_string_part(connection_string, :credentials)
    return nil if credentials.nil?
    return credentials[part]
  when :credentials # array of user/password or integrated security
    connection_string[connection_string_part_regex(:user)]
    user = Regexp.last_match(3)
    connection_string[connection_string_part_regex(:password)]
    password = Regexp.last_match(3)
    return { user: user, password: password } unless user.nil? || password.nil?
    return connection_string[connection_string_part_regex(:integrated)]
  end
  return Regexp.last_match(3) if value_only
  result = (Regexp.last_match(1) || '') + '=' + (Regexp.last_match(3) || '') + (Regexp.last_match(4) || '')
  result.empty? ? nil : result
end

.connection_string_part_regex(part) ⇒ Object 



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
# File 'lib/sql_cmd/format.rb', line 4

def connection_string_part_regex(part)
  case part
  when :server
    /(server|data source)(\s*\=\s*)([^;]*)(;)?/i
  when :database
    /(database|initial catalog)(\s*\=\s*)([^;]*)(;)?/i
  when :user # array of user/password or integrated security
    /(user id|uid)(\s*\=\s*)([^;]*)(;)?/i
  when :password
    /(password|pwd)(\s*\=\s*)([^;]*)(;)?/i
  when :integrated
    /integrated security\s*\=\s*[^;]*(;)?/i
  when :applicationintent
    /applicationintent\s*\=\s*[^;]*(;)?/i
  else
    raise "#{part} is not a supported connection string part!"
  end
end

.convert_powershell_tables_to_hash(json_string, return_type = :all_tables, at_timezone: 'UTC', string_to_time_by: '%Y-%m-%d %H:%M:%S %z') ⇒ Object

options: :all_tables, :first_table, :first_row



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/sql_cmd/query.rb', line 100

def convert_powershell_tables_to_hash(json_string, return_type = :all_tables, at_timezone: 'UTC', string_to_time_by: '%Y-%m-%d %H:%M:%S %z') # options: :all_tables, :first_table, :first_row
  EasyIO.logger.debug "Output from sql command: #{json_string}" if SqlCmd.config['logging']['verbose']
  parsed_json = JSON.parse(to_utf8(json_string.sub(/[^{\[]*/, ''))) # Ignore any leading characters other than '{' or '['
  timezone_table = parsed_json.delete(parsed_json.keys.last)
  sqlserver_timezone = timezone_table.first.values.first # The last table should be the SQL server's time zone - get the value and remove it from the dataset
  result_hash = if json_string.empty?
                  {}
                else
                  convert_powershell_time_objects(parsed_json, at_timezone: at_timezone, string_to_time_by: string_to_time_by, timezone_override: sqlserver_timezone)
                end

  raise 'No tables were returned by specified sql query!' if result_hash.values.first.nil? && return_type != :all_tables
  case return_type
  when :first_table
    result_hash.values.first
  when :first_row
    result_hash.values.first.first
  when :scalar
    return nil if result_hash.values.first.first.nil?
    result_hash.values.first.first.values.first # Return first column of first row of first table
  else
    result_hash.values
  end
end

.convert_powershell_time_objects(value, at_timezone: 'UTC', string_to_time_by: '%Y-%m-%d %H:%M:%S %z', timezone_override: nil, override_strings: false, utc_column: false) ⇒ Object

at_timezone: convert all times to this time zone timezone_override: overwrite the timezone on the time without changing the timestamp value - this occurs before :at_timezone string_to_time_by: Provide the format of the string to be parsed - see ruby-doc.org/stdlib-2.4.1/libdoc/time/rdoc/Time.html#method-c-strptime

- Set it to nil or false to not parse any strings



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/sql_cmd/query.rb', line 129

def convert_powershell_time_objects(value, at_timezone: 'UTC', string_to_time_by: '%Y-%m-%d %H:%M:%S %z', timezone_override: nil, override_strings: false, utc_column: false)
  case value
  when Array
    value.map { |v| convert_powershell_time_objects(v, at_timezone: at_timezone, string_to_time_by: string_to_time_by, timezone_override: timezone_override, utc_column: utc_column) }
  when Hash
    Hash[value.map { |k, v| [k, convert_powershell_time_objects(v, at_timezone: at_timezone, string_to_time_by: string_to_time_by, timezone_override: timezone_override, utc_column: k =~ /utc/i)] }]
  else
    timezone_override = 'UTC' if utc_column
    return value unless value.is_a?(String)
    time_from_js = EasyTime.from_javascript_format(value)
    if time_from_js # A java script time was found
      value = EasyTime.stomp_timezone(time_from_js, timezone_override)
      at_timezone ? EasyTime.at_timezone(value, at_timezone) : value
    else
      return value unless string_to_time_by
      begin
        value = Time.strptime(value, string_to_time_by)
        value = EasyTime.stomp_timezone(value, timezone_override) if override_strings
        at_timezone ? EasyTime.at_timezone(value, at_timezone) : value
      rescue ArgumentError # If an ArgumentError is thrown, the string was not in the :string_to_time_by format expected so it's probably not a time. Return it as is.
        value
      end
    end
  end
end

.create_sql_login(connection_string, user, password, update_existing: false, retries: 3, retry_delay: 5) ⇒ Object 



214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/sql_cmd/query.rb', line 214

def (connection_string, user, password, update_existing: false, retries: 3, retry_delay: 5)
  sid_script = ::File.read("#{sql_script_dir}/Security/GetUserSID.sql")
  raise "SQL password for login [#{user}] must not be empty!" if password.nil? || password.empty?
  values = { 'user' => user, 'password' => password }
  EasyIO.logger.info "Checking for existing SqlLogin: #{user}..."
   = execute_query(connection_string, sid_script, return_type: :scalar, values: values, readonly: true, retries: retries, retry_delay: retry_delay)
  if .nil?
    sql_script = ::File.read("#{sql_script_dir}/Security/CreateSqlLogin.sql")
    EasyIO.logger.info "Creating SqlLogin: #{user}..."
    result = execute_query(connection_string, sql_script, return_type: :first_row, values: values, retries: retries, retry_delay: retry_delay)
    raise "Failed to create SQL login: [#{user}]!" if result.nil? || result['name'].nil?
     = result['sid']
  elsif update_existing
    sql_script = ::File.read("#{sql_script_dir}/Security/UpdateSqlPassword.sql")
    EasyIO.logger.info "Login [#{user}] already exists... updating password."
    execute_query(connection_string, sql_script, return_type: :first_row, values: values, retries: retries, retry_delay: retry_delay)
  else
    EasyIO.logger.info "Login [#{user}] already exists..."
  end
  EasyIO.logger.debug "SqlLogin [#{user}] sid: #{}"
  
end

.database_roles_assigned?(connection_string, database_name, user, roles, sid) ⇒ Boolean

Returns:

  • (Boolean) 


310
311
312
313
314
315
 
# File 'lib/sql_cmd/query.rb', line 310

def database_roles_assigned?(connection_string, database_name, user, roles, sid)
  values = { 'databasename' => database_name, 'user' => user, 'sid' => sid }
  validation_script = ::File.read("#{sql_script_dir}/Security/ValidateDatabaseRoles.sql")
  validation_result = execute_query(connection_string, validation_script, return_type: :first_table, values: values, readonly: true, retries: 3)
  roles.all? { |role| validation_result.any? { |row| row['name'].casecmp(role) == 0 } }
end

.defaults ⇒ Object 



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/sql_cmd/config.rb', line 19

def defaults
  {
    'paths' => {
      'cache' => Dir.tmpdir,
    },
    'environment' => {
      'timezone' => 'UTC',
    },
    'logging' => {
      'level' => 'info',
      'verbose' => false, # show queries and returned json
    },
    'sql_cmd' => {
      'backups' => {
        'always_on_backup_temp_dir' => nil, # where backups will go when adding to availability groups and seeding_mode is manual or nil
        'always_on_backup_url' => nil, # where backups will go when adding to availability groups and seeding_mode is manual or nil - supercedes temp dir above
        'default_destination' => nil, # where backups will go by default
        'backup_to_host_sql_server' => false, # if set to true, will backup databases to the SQL host instead of the default destination
        'default_backup_share' => nil, # the name of the windows share relative to SQL hosts where backups go when set to backup to sql hosts
        'free_space_threshold' => 5, # raises an exception if a backup or restore operation would bring free space on the target location below this threshold
        'compress_backups' => false,
      },
      'exports' => {
        'include_table_permissions' => false,
      },
      'paths' => {
        'sql_script_dir' => ::File.expand_path("#{__dir__}/../../sql_scripts"),
        'powershell_helper_script' => ::File.expand_path("#{__dir__}/sql_helper.ps1"),
      },
    },
  }
end

.ensure_sql_agent_is_running(connection_string) ⇒ Object 



327
328
329
330
331
 
# File 'lib/sql_cmd/query.rb', line 327

def ensure_sql_agent_is_running(connection_string)
  sql_script = ::File.read("#{sql_script_dir}/Agent/SQLAgentStatus.sql")
  sql_server_settings = get_sql_server_settings(connection_string)
  raise "SQL Agent is not running on #{sql_server_settings['ServerName']}!" if execute_query(connection_string, sql_script, return_type: :scalar, retries: 3) == 0
end

.execute_query(connection_string, sql_query, return_type: :all_tables, values: nil, timeout: 172_800, readonly: false, ignore_missing_values: false, at_timezone: SqlCmd.config['environment']['timezone'], string_to_time_by: '%Y-%m-%d %H:%M:%S %z', retries: 0, retry_delay: 5) ⇒ Object

Execute a SQL query and return a dataset, table, row, or scalar, based on the return_type specified

return_type: :all_tables, :first_table, :first_row, :scalar
values: hash of values to replace sqlcmd style variables.
        EG: sql_query = SELECT * FROM $(databasename)
            values = { 'databasename' => 'my_database_name' }
readonly: if true, sets the connection_string to readonly (Useful with AOAG)
retries: number of times to re-attempt failed queries
retry_delay: how many seconds to wait between retries



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/sql_cmd/query.rb', line 15

def execute_query(connection_string, sql_query, return_type: :all_tables, values: nil, timeout: 172_800, readonly: false, ignore_missing_values: false, at_timezone: SqlCmd.config['environment']['timezone'], string_to_time_by: '%Y-%m-%d %H:%M:%S %z', retries: 0, retry_delay: 5)
  sql_query = insert_values(sql_query, values) unless values.nil?
  missing_values = sql_query.reverse.scan(/(\)[0-9a-z_]+\(\$)(?!.*--)/i).uniq.join(' ,').reverse # Don't include commented variables
  raise "sql_query has missing variables! Ensure that values are supplied for: #{missing_values}\n" unless missing_values.empty? || ignore_missing_values
  connection_string_updated = connection_string.dup
  connection_string_updated = replace_connection_string_part(connection_string, :applicationintent, 'readonly') if readonly
  raise 'Connection string is nil or incomplete' if connection_string_updated.nil? || connection_string_updated.empty?
  EasyIO.logger.debug "Executing query with connection_string: \n\t#{hide_connection_string_password(connection_string_updated)}"
  EasyIO.logger.debug sql_query if SqlCmd.config['logging']['verbose'] && sql_query.length < 8096
  start_time = Time.now.utc.strftime('%y%m%d_%H%M%S-%L')
  ps_script = <<-EOS.strip
      . "#{@scripts_cache_windows}\\sql_helper_#{start_time}.ps1"

      $connectionString = '#{connection_string_updated}'
      $sqlCommand = '#{sql_query.gsub('\'', '\'\'')}'
      $dataset = Invoke-SQL -timeout #{timeout} -connectionString $connectionString -sqlCommand $sqlCommand
      ConvertSqlDatasetTo-Json -dataset $dataset
    EOS

  ps_script_file = "#{@scripts_cache}/ps_script-thread_id-#{Thread.current.object_id}.ps1"
  FileUtils.mkdir_p ::File.dirname(ps_script_file)
  FileUtils.cp(SqlCmd.config['sql_cmd']['paths']['powershell_helper_script'], "#{@scripts_cache}/sql_helper_#{start_time}.ps1")
  ::File.write(ps_script_file, ps_script)
  retry_count = 0
  begin
    result = ''
    exit_status = ''
    Open3.popen3("powershell -File \"#{ps_script_file}\"") do |_stdin, stdout, stderr, wait_thread|
      buffers = [stdout, stderr]
      queued_buffers = IO.select(buffers) || [[]]
      queued_buffers.first.each do |buffer|
        case buffer
        when stdout
          while (line = buffer.gets)
            stdout_split = line.split('#return_data#:')
            raise "SQL exception: #{stdout_split.first} #{stdout.read} #{stderr.read}\nConnectionString: '#{hide_connection_string_password(connection_string)}'\n#{EasyIO::Terminal.line('=')}\n" if stdout_split.first =~ /error 50000, severity (1[1-9]|2[0-5])/i
            EasyIO.logger.info "SQL message: #{stdout_split.first.strip}" unless stdout_split.first.empty?
            result = stdout_split.last + buffer.read if stdout_split.count > 1
          end
        when stderr
          error_message = stderr.read
          raise "SQL exception: #{error_message}\nConnectionString: '#{hide_connection_string_password(connection_string)}'\n#{'=' * 120}\n" unless error_message.empty?
        end
      end
      exit_status = wait_thread.value
    end
    EasyIO.logger.debug "Script exit status: #{exit_status}"
    EasyIO.logger.debug "JSON result: #{result}"
  rescue
    retry_message = 'Executing SQL query failed! '
    retry_message += if retries == 0
                       'No retries specified. Will not reattempt. '
                     else
                       retry_count < retries ? "Retry #{(retry_count + 1)} of #{retries}" : "All #{retries} retries attempted."
                     end
    EasyIO.logger.info retry_message
    if (retry_count += 1) <= retries
      EasyIO.logger.info "Retrying in #{retry_delay} seconds..."
      sleep(retry_delay)
      retry
    end
    raise
  end

  begin
    convert_powershell_tables_to_hash(result, return_type, at_timezone: at_timezone, string_to_time_by: string_to_time_by)
  rescue # Change it to use terminal size instead of 120 chars in the error here and above
    EasyIO.logger.fatal "Failed to convert SQL data to hash! ConnectionString: '#{hide_connection_string_password(connection_string)}'\n#{EasyIO::Terminal.line('=')}\n"
    raise
  end
ensure
  retry_count = 0
  retries = 3
  retry_delay = 3
  begin
    ::File.delete "#{@scripts_cache_windows}\\sql_helper_#{start_time}.ps1" if defined?(start_time) && ::File.exist?("#{@scripts_cache_windows}\\sql_helper_#{start_time}.ps1")
  rescue # Try to delete the file 3 times
    if (retry_count += 1) <= retries
      sleep(retry_delay)
      retry
    end
    raise
  end
end

.execute_script_file(connection_string, import_script_filename, values: nil, readonly: false, retries: 0, retry_delay: 15) ⇒ Object 



295
296
297
298
299
 
# File 'lib/sql_cmd/query.rb', line 295

def execute_script_file(connection_string, import_script_filename, values: nil, readonly: false, retries: 0, retry_delay: 15)
  return nil if import_script_filename.nil? || import_script_filename.empty?
  sql_script = ::File.read(import_script_filename)
  execute_query(connection_string, sql_script, values: values, readonly: readonly, retries: retries, retry_delay: retry_delay)
end

.export_logins(start_time, connection_string, database_name, remove_existing_logins: true) ⇒ Object 



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/sql_cmd/query.rb', line 257

def export_logins(start_time, connection_string, database_name, remove_existing_logins: true)
  start_time = SqlCmd.unify_start_time(start_time)
  export_folder = "#{SqlCmd.config['paths']['cache']}/sql_cmd/logins"
  server_name = connection_string_part(connection_string, :server)
  import_script_filename = "#{export_folder}/#{EasyFormat::File.windows_friendly_name(server_name)}_#{database_name}_logins.sql"
  if SqlCmd::Database.info(connection_string, database_name)['DatabaseNotFound']
    warning_message = 'Source database was not found'
    unless ::File.exist?(import_script_filename)
      EasyIO.logger.warn "#{warning_message}. Unable to export logins! Ensure logins are migrated or migrate them manually!"
      return import_script_filename
    end

    warning_message += if File.mtime(import_script_filename) >= start_time
                         ' but the import logins script file already exists. Proceeding...'
                       else
                         ' and the import logins script file is out of date. Ensure logins are migrated or migrate them manually!'
                       end
    EasyIO.logger.warn warning_message
    return import_script_filename # TODO: attempt to create logins anyway instead of returning a non-existent script file
  end
  sql_script = ::File.read("#{sql_script_dir}/Security/GenerateCreateLoginsScript.sql")
  values = { 'databasename' => database_name, 'removeexistinglogins' => remove_existing_logins }
  EasyIO.logger.info "Exporting logins associated with database: [#{database_name}] on [#{server_name}]..."
  import_script = execute_query(connection_string, sql_script, return_type: :scalar, values: values, readonly: true, retries: 3)
  return nil if import_script.nil? || import_script.empty?
  FileUtils.mkdir_p(export_folder)
  ::File.write(import_script_filename, import_script)
  import_script_filename
end

.gap_in_log_backups?(sql_server_settings, database_backup_header, restored_database_lsn, backup_folder, backup_basename, options = {}) ⇒ Boolean

Returns:

  • (Boolean) 


126
127
128
129
130
131
132
133
134
135
 
# File 'lib/sql_cmd/backups.rb', line 126

def gap_in_log_backups?(sql_server_settings, database_backup_header, restored_database_lsn, backup_folder, backup_basename, options = {})
  last_full_backup_lsn = database_backup_header['LastLSN']
  return true if last_full_backup_lsn.nil? # if the last full backup does not exist, behave as if there is a gap in the log backups
  backup_sets = backup_sets_from_unc_path(sql_server_settings, backup_folder, backup_basename, database_backup_header: database_backup_header, restored_database_lsn: restored_database_lsn, log_only: true)
  return true if backup_sets.nil? # nil is returned if the backup is too new for the restored database LSN, therefore there's a gap
  return false if backup_sets.empty? # if no log backup sets were current, behave as if there is no gap since a log backup hasn't yet been taken since the backup
  first_lsn_from_log_backups = get_sql_backup_headers(sql_server_settings['connection_string'], backup_sets.first.last, options).first['FirstLSN']
  EasyIO.logger.debug "LastLSN from full backup: #{last_full_backup_lsn} | First LSN from log backups: #{first_lsn_from_log_backups}"
  last_full_backup_lsn < first_lsn_from_log_backups && restored_database_lsn < first_lsn_from_log_backups
end

.get_backup_file_info(connection_string, backup_files, options) ⇒ Object

Returns the data and log file information contained in the backup files.



138
139
140
141
142
143
144
145
 
# File 'lib/sql_cmd/backups.rb', line 138

def get_backup_file_info(connection_string, backup_files, options)
  EasyFormat.validate_parameters(method(__method__), binding)
  disk_backup_files = backup_fileset_names(backup_files)
  sql_script = ::File.read("#{sql_script_dir}/Database/GetFileInfoFromBackup.sql")
  options['bkupfiles'] = disk_backup_files
  options['credential'] ||= options['storage_account_name'] || ''
  execute_query(connection_string, sql_script, return_type: :first_table, values: options, retries: 3)
end

.get_backup_files(sql_server_settings, options = {}, backup_folder: nil, backup_url: nil, backup_basename: nil) ⇒ Object

get a list of backup files and the backup basename

options:

log_only: determines whether to look for log backup files or normal backup files. (.trn vs .bak) default: false
all_time_stamps: returns all backup sets found matching the basename and not just the most recent. default: false
storage_account_name: (required if using Azure blob storage) Azure blob storage account name.
storage_access_key: (required if using Azure blob storage) Azure blob storage access key.



245
246
247
248
249
250
251
252
253
 
# File 'lib/sql_cmd/backups.rb', line 245

def get_backup_files(sql_server_settings, options = {}, backup_folder: nil, backup_url: nil, backup_basename: nil)
  if !backup_url.nil? && !backup_url.empty?
    get_url_backup_files(sql_server_settings, backup_url, backup_basename, options)
  elsif backup_folder.start_with?('\\\\')
    get_unc_backup_files(sql_server_settings, backup_folder, backup_basename, log_only: options['log_only'])
  else
    sql_server_backup_files(sql_server_settings, backup_basename, log_only: log_only)
  end
end

.get_backup_logical_names(connection_string, backup_files, options) ⇒ Object 



147
148
149
150
151
152
153
 
# File 'lib/sql_cmd/backups.rb', line 147

def get_backup_logical_names(connection_string, backup_files, options)
  EasyFormat.validate_parameters(method(__method__), binding)
  sql_backup_file_info = SqlCmd.get_backup_file_info(connection_string, backup_files, options)
  data_file_logical_name = sql_backup_file_info.select { |file| file['Type'] == 'D' }.first['LogicalName']
  log_file_logical_name = sql_backup_file_info.select { |file| file['Type'] == 'L' }.first['LogicalName']
  [data_file_logical_name, log_file_logical_name]
end

.get_backup_size(sql_backup_header) ⇒ Object

Get size of uncompressed database from backup header in MB



256
257
258
 
# File 'lib/sql_cmd/backups.rb', line 256

def get_backup_size(sql_backup_header)
  sql_backup_header['BackupSize'].to_f / 1024 / 1024
end

.get_backup_sql_server_settings(connection_string) ⇒ Object 



6
7
8
9
10
11
 
# File 'lib/sql_cmd/backups.rb', line 6

def get_backup_sql_server_settings(connection_string)
  sql_server_settings = get_sql_server_settings(connection_string)
  sql_server_settings = get_sql_server_settings(to_integrated_security(connection_string)) if sql_server_settings.nil? || sql_server_settings['BackupDir'].nil? || sql_server_settings['BackupDir'] == 'null'
  raise "FATAL: Current user #{ENV['user'] || ENV['username']} does not have access to backup database!" if sql_server_settings.nil? || sql_server_settings['BackupDir'].nil? || sql_server_settings['BackupDir'] == 'null'
  sql_server_settings
end

.get_database_size(connection_string, database_name, log_only: false, retries: 3, retry_delay: 15) ⇒ Object

Returns the database size or log size in MB



202
203
204
205
206
 
# File 'lib/sql_cmd/query.rb', line 202

def get_database_size(connection_string, database_name, log_only: false, retries: 3, retry_delay: 15)
  sql_script = ::File.read("#{sql_script_dir}/Status/DatabaseSize.sql")
  connection_string = remove_connection_string_part(connection_string, :database)
  execute_query(connection_string, sql_script, values: { 'databasename' => database_name, 'logonly' => log_only }, return_type: :scalar, readonly: true, retries: retries, retry_delay: retry_delay).to_f
end

.get_sql_backup_headers(connection_string, backup_files, options = {}) ⇒ Object

Returns the headers from the backup set provided. Pass an array of path strings to the backup files.



116
117
118
119
120
121
122
123
124
 
# File 'lib/sql_cmd/backups.rb', line 116

def get_sql_backup_headers(connection_string, backup_files, options = {})
  EasyFormat.validate_parameters(method(__method__), binding, %W(options))
  disk_backup_files = backup_fileset_names(backup_files)
  sql_script = ::File.read("#{sql_script_dir}/Database/GetBackupHeaders.sql")
  options['bkupfiles'] = disk_backup_files
  options['credential'] ||= options['storage_account_name'] || ''
  SqlCmd::Security.create_credential(connection_string, options['credential'], options['storage_account_name'], options['storage_access_key'], options) unless options['credential'].nil? || options['credential'].empty?
  execute_query(connection_string, sql_script, return_type: :first_table, values: options, retries: 3, retry_delay: 10)
end

.get_sql_disk_space(connection_string, target_folder, retries: 3, retry_delay: 15) ⇒ Object

Returns a hash with the following fields: Available_MB, Total_MB, Percent_Free



209
210
211
212
 
# File 'lib/sql_cmd/query.rb', line 209

def get_sql_disk_space(connection_string, target_folder, retries: 3, retry_delay: 15)
  sql_script = ::File.read("#{sql_script_dir}/Status/DiskSpace.sql")
  execute_query(connection_string, sql_script, return_type: :first_row, values: { 'targetfolder' => target_folder }, retries: retries, retry_delay: retry_delay)
end

.get_sql_server_settings(connection_string, retries: 3, retry_delay: 15) ⇒ Object 



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/sql_cmd/query.rb', line 163

def get_sql_server_settings(connection_string, retries: 3, retry_delay: 15)
  get_sql_settings_script = ::File.read("#{sql_script_dir}/Status/SQLSettings.sql")
  sql_server_settings = execute_query(SqlCmd.remove_connection_string_part(connection_string, :database), get_sql_settings_script, return_type: :first_row, retries: retries, retry_delay: retry_delay)
  EasyIO.logger.debug "sql_server_settings: \n#{JSON.pretty_generate(sql_server_settings)}"
  return nil if sql_server_settings.nil? || sql_server_settings['ServerName'].nil?

  direct_connection_string = connection_string.gsub(sql_server_settings['DataSource'], sql_server_settings['ServerName'])
  application_connection_string = connection_string.gsub(sql_server_settings['ServerName'], sql_server_settings['DataSource'])
  secondary_replica_connection_string = if sql_server_settings['SecondaryReplica'].nil?
                                          nil
                                        else
                                          cnstr = SqlCmd.replace_connection_string_part(connection_string, :server, sql_server_settings['SecondaryReplica'])
                                          SqlCmd.remove_connection_string_part(cnstr, :database)
                                        end
  sql_server_settings['direct_connection_string'] = direct_connection_string # Does not use AlwaysOn listener
  sql_server_settings['connection_string'] = application_connection_string
  sql_server_settings['secondary_replica_connection_string'] = secondary_replica_connection_string
  sql_server_settings['DataDir'] = EasyFormat::Directory.ensure_trailing_slash(sql_server_settings['DataDir'])
  sql_server_settings['LogDir'] = EasyFormat::Directory.ensure_trailing_slash(sql_server_settings['LogDir'])
  sql_server_settings['BackupDir'] = EasyFormat::Directory.ensure_trailing_slash(sql_server_settings['BackupDir'])
  sql_server_settings
end

.get_unc_backup_files(sql_server_settings, backup_folder, backup_basename, log_only: false, all_time_stamps: false) ⇒ Object

sql_server_settings can be for any server that has network access to these files



14
15
16
17
18
19
20
21
22
 
# File 'lib/sql_cmd/backups.rb', line 14

def get_unc_backup_files(sql_server_settings, backup_folder, backup_basename, log_only: false, all_time_stamps: false)
  backup_folder = EasyFormat::Directory.ensure_trailing_slash(backup_folder).tr('\\', '/')
  backup_file_extension = backup_basename.slice!(/\.(trn|bak)/i)
  backup_file_extension ||= log_only ? 'trn' : 'bak'
  backup_file_extension = backup_file_extension.reverse.chomp('.').reverse
  backup_files = Dir.glob("#{backup_folder}#{backup_basename}*").grep(/#{Regexp.escape(backup_basename)}(_\d{6})?(\.part\d+)?\.#{backup_file_extension}$/i)
  return [backup_files, backup_basename] if all_time_stamps
  most_recent_backup_files_and_basename(sql_server_settings, backup_files, backup_basename)
end

.get_url_backup_files(sql_server_settings, backup_url, backup_basename, options = {}, log_only: false, all_time_stamps: false) ⇒ Object

get a list of backup files from a URL

options:

log_only: determines whether to look for log backup files or normal backup files. (.trn vs .bak) default: false
all_time_stamps: returns all backup sets found matching the basename and not just the most recent. default: false
storage_account_name: (required if using Azure blob storage) Azure blob storage account name.
storage_access_key: (required if using Azure blob storage) Azure blob storage access key.



31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/sql_cmd/backups.rb', line 31

def get_url_backup_files(sql_server_settings, backup_url, backup_basename, options = {}, log_only: false, all_time_stamps: false)
  if azure_blob_storage_url?(backup_url)
    backup_file_extension = backup_basename.slice!(/\.(trn|bak)/i)
    backup_file_extension ||= log_only ? 'trn' : 'bak'
    backup_file_extension = backup_file_extension.reverse.chomp('.').reverse
    backup_files = azure_blob_storage_list_backup_files(backup_url, backup_basename, options['storage_account_name'], options['storage_access_key'])
    backup_files.select! { |f, _modified| f =~ /#{Regexp.escape(backup_file_extension)}$/i }
    return [backup_files.keys, backup_basename] if options['all_time_stamps']
    most_recent_backup_files_and_basename(sql_server_settings, backup_files.keys, backup_basename, options)
  else
    EasyIO.logger.warn 'Non-azure blob storage URLs are not currently supported.'
  end
end

.hide_connection_string_password(connection_string) ⇒ Object 



52
53
54
55
56
57
 
# File 'lib/sql_cmd/format.rb', line 52

def hide_connection_string_password(connection_string)
  credentials = connection_string_part(connection_string, :credentials)
  credentials[:password] = credentials[:password].gsub(/(.)([^(.$)]*)(.$)/) { Regexp.last_match(1) + ('*' * Regexp.last_match(2).length) + Regexp.last_match(3) } if credentials.is_a?(Hash) && !credentials[:password].nil? && credentials[:password].length > 2
  raise "Connection string missing authentication information! Connection string: '#{connection_string}'" if credentials.nil? || credentials.empty?
  replace_connection_string_part(connection_string, :credentials, credentials)
end

.insert_values(sql_query, values, case_sensitive: false) ⇒ Object

Substitute sqlcmd style variables with values provided in a hash. Don’t include $() Example values: ‘varname’ => ‘Some value’, ‘varname2’ => ‘some other value’



188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/sql_cmd/query.rb', line 188

def insert_values(sql_query, values, case_sensitive: false)
  return sql_query if values.nil? || values.all? { |i, _j| i.nil? || i.empty? }

  EasyIO.logger.debug "Inserting variable values into query: #{JSON.pretty_generate(values)}"
  sql_query = to_utf8(sql_query)
  values.each do |key, value|
    regexp_key = case_sensitive ? "$(#{key})" : /\$\(#{Regexp.escape(key)}\)/i
    sql_query.gsub!(regexp_key) { value }
  end

  sql_query
end

.migrate_logins(start_time, source_connection_string, destination_connection_string, database_name) ⇒ Object 



245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/sql_cmd/query.rb', line 245

def migrate_logins(start_time, source_connection_string, destination_connection_string, database_name)
  start_time = SqlCmd.unify_start_time(start_time)
  import_script_filename = export_logins(start_time, source_connection_string, database_name)
  return if import_script_filename.nil?
  if ::File.exist?(import_script_filename)
    EasyIO.logger.info "Importing logins on [#{connection_string_part(destination_connection_string, :server)}]..."
    execute_script_file(destination_connection_string, import_script_filename)
  else
    EasyIO.logger.warn 'Unable to migrate logins. Ensure they exist or manually create them.'
  end
end

.most_recent_backup_files_and_basename(sql_server_settings, backup_files, backup_basename, options = {}) ⇒ Object 



173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/sql_cmd/backups.rb', line 173

def most_recent_backup_files_and_basename(sql_server_settings, backup_files, backup_basename, options = {})
  backup_sets = backup_sets_from_backup_files(backup_files)
  if backup_sets.keys.count > 1 # if there is more than 1 backup set, find the most recent
    backup_headers = {}
    backup_sets.each do |basename, files|
      backup_headers[basename] = get_sql_backup_headers(sql_server_settings['connection_string'], files, options).first
    end
    backup_basename = backup_headers.max_by { |_basename, header| header['BackupFinishDate'] }.first
  elsif backup_sets.keys.count == 0 # if there are no backup sets, use an empty array
    backup_sets[backup_basename] = []
  end
  [backup_sets[backup_basename], backup_basename]
end

.relevant_log_backup_sets(sql_server_settings, backup_files, database_backup_header, restored_database_lsn, options = {}) ⇒ Object 



187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/sql_cmd/backups.rb', line 187

def relevant_log_backup_sets(sql_server_settings, backup_files, database_backup_header, restored_database_lsn, options = {})
  restored_database_lsn ||= 0
  backup_sets = backup_sets_from_backup_files(backup_files)
  database_backup_lsn = database_backup_header['DatabaseBackupLSN']
  EasyIO.logger.debug "Database backup LSN: #{database_backup_lsn}"
  backup_headers = backup_sets.each_with_object({}) { |(basename, files), headers| headers[basename] = get_sql_backup_headers(sql_server_settings['connection_string'], files, options).first }
  backup_sets = backup_sets.sort_by { |basename, _files| backup_headers[basename]['LastLSN'] }.to_h
  backup_headers = backup_headers.sort_by { |_basename, backup_header| backup_header['LastLSN'] }.to_h
  EasyIO.logger.debug "Backup sets after sorting: #{JSON.pretty_generate(backup_sets)}"
  backup_sets.each { |basename, _files| EasyIO.logger.debug "Backup header for #{basename}: FirstLSN: #{backup_headers[basename]['FirstLSN']} | LastLSN: #{backup_headers[basename]['LastLSN']}" }
  start_lsn = nil
  current_lsn = database_backup_header['LastLSN']
  backup_headers.each do |basename, backup_header|
    start_lsn ||= backup_header['FirstLSN']
    EasyIO.logger.debug "Current LSN: #{current_lsn}"
    EasyIO.logger.debug "Current header (#{basename}) - FirstLSN: #{backup_headers[basename]['FirstLSN']} | LastLSN: #{backup_headers[basename]['LastLSN']} | DatabaseBackupLSN: #{backup_headers[basename]['DatabaseBackupLSN']}"
    unless backup_header['DatabaseBackupLSN'] == database_backup_lsn
      EasyIO.logger.debug "Current backup is from a different database backup as the DatabaseBackupLSN (#{backup_header['DatabaseBackupLSN']}) doesn't match the database backup LSN (#{database_backup_lsn}). Removing backup set..."
      backup_sets.delete(basename)
      next
    end
    if backup_header['LastLSN'] < database_backup_lsn || backup_header['LastLSN'] < restored_database_lsn
      EasyIO.logger.debug "Current backup LastLSN (#{backup_header['FirstLSN']}) older than database backup LSN (#{database_backup_lsn}) or restored database LSN (#{restored_database_lsn}). Removing backup set..."
      backup_sets.delete(basename)
      next
    end
    if backup_header['FirstLSN'] > current_lsn # remove previous backup sets if there's a gap
      EasyIO.logger.debug "Gap found between previous backup LastLSN (#{current_lsn}) and current backup FirstLSN #{backup_header['FirstLSN']}. Updating starting point..." unless current_lsn == 0
      start_lsn = backup_header['FirstLSN']
      if start_lsn > restored_database_lsn # if the starting point is beyond the restored database LastLSN, no backups can be applied
        EasyIO.logger.warn "Gap found in log backups. The previous backup ends at LSN #{current_lsn} and the next log backup starts at LSN #{backup_header['FirstLSN']}!"
        return nil
      end
    end
    current_lsn = backup_header['LastLSN']
  end
  backup_headers.each { |basename, backup_header| backup_sets.delete(basename) unless backup_header['LastLSN'] > start_lsn } # remove any obsolete backup sets
  EasyIO.logger.debug "Backup sets after removing obsolete sets: #{JSON.pretty_generate(backup_sets)}"
  backup_sets
end

.remove_connection_string_part(connection_string, part) ⇒ Object

part options: :server, :database, :credentials, :applicationintent



45
46
47
48
49
50
 
# File 'lib/sql_cmd/format.rb', line 45

def remove_connection_string_part(connection_string, part) # part options: :server, :database, :credentials, :applicationintent
  connection_string_new = connection_string.dup
  parts = part == :credentials ? [:user, :password, :integrated] : [part]
  parts.each { |p| connection_string_new.gsub!(connection_string_part_regex(p), '') } # unless full_part.nil? }
  connection_string_new
end

.replace_connection_string_part(connection_string, part, replacement_value) ⇒ Object

Supply entire value (EG: ‘user id=value;password=value;’ or ‘integrated security=SSPI;’) as the replacement_value if the part is :credentials or provide a hash containing a username and password { user: ‘someuser’, password: ‘somepassword’, }



67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/sql_cmd/format.rb', line 67

def replace_connection_string_part(connection_string, part, replacement_value)
  EasyFormat.validate_parameters(method(__method__), binding)
  new_connection_string = remove_connection_string_part(connection_string, part)
  new_connection_string = case part
                          when :credentials
                            replacement_value = "User Id=#{replacement_value[:user]};Password=#{replacement_value[:password]}" if replacement_value.is_a?(Hash)
                            "#{new_connection_string};#{replacement_value}"
                          else
                            "#{part}=#{replacement_value};#{new_connection_string};"
                          end
  new_connection_string.gsub!(/;+/, ';')
  new_connection_string
end

.required_keys ⇒ Object 



8
9
10
11
12
13
14
15
16
17
 
# File 'lib/sql_cmd/config.rb', line 8

def required_keys
  {
    'sql_cmd' => {
      'backups' => {
        'always_on_backup_temp_dir' => nil,
        'default_destination' => nil,
      },
    },
  }
end

.run_sql_as_job(connection_string, sql_script, sql_job_name, sql_job_owner = 'sa', values: nil, retries: 0, retry_delay: 15) ⇒ Object 



317
318
319
320
321
322
323
324
325
 
# File 'lib/sql_cmd/query.rb', line 317

def run_sql_as_job(connection_string, sql_script, sql_job_name, sql_job_owner = 'sa', values: nil, retries: 0, retry_delay: 15)
  sql_script = insert_values(sql_script, values) unless values.nil?
  ensure_sql_agent_is_running(connection_string)
  job_values = { 'sqlquery' => sql_script.gsub('\'', '\'\''),
                 'jobname' => sql_job_name,
                 'jobowner' => sql_job_owner }
  sql_job_script = ::File.read("#{sql_script_dir}/Agent/CreateSQLJob.sql")
  execute_query(connection_string, sql_job_script, values: job_values, retries: retries, retry_delay: retry_delay)
end

.sql_login_exists?(connection_string, login, retries: 3, retry_delay: 15) ⇒ Boolean

Returns:

  • (Boolean) 


237
238
239
240
241
242
243
 
# File 'lib/sql_cmd/query.rb', line 237

def (connection_string, , retries: 3, retry_delay: 15)
  sid_script = ::File.read("#{sql_script_dir}/Security/GetUserSID.sql")
  raise "SQL password for login [#{}] must not be empty!" if password.nil? || password.empty?
  values = { 'user' => , 'password' => password }
  EasyIO.logger.info "Checking for existing SqlLogin: #{}..."
  execute_query(connection_string, sid_script, return_type: :scalar, values: values, readonly: true, retries: retries, retry_delay: retry_delay)
end

.sql_script_dir ⇒ Object 



52
53
54
 
# File 'lib/sql_cmd/config.rb', line 52

def sql_script_dir
  SqlCmd.config['sql_cmd']['paths']['sql_script_dir']
end

.sql_server_backup_files(sql_server_settings, backup_basename, log_only: false) ⇒ Object 



155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/sql_cmd/backups.rb', line 155

def sql_server_backup_files(sql_server_settings, backup_basename, log_only: false)
  values = { 'targetfolder' => sql_server_settings['BackupDir'],
             'bkupname' => backup_basename,
             'logonly' => log_only }
  sql_script = ::File.read("#{sql_script_dir}/Database/GetBackupFiles.sql")
  backup_files_results = execute_query(sql_server_settings['connection_string'], sql_script, return_type: :first_table, values: values, retries: 3)
  backup_files = []
  backup_files_results.each do |file|
    backup_files.push("#{EasyFormat::Directory.ensure_trailing_slash(sql_server_settings['BackupDir'])}#{file['FileName']}")
  end
  if log_only
    database_backup_files = sql_server_backup_files(sql_server_settings, backup_basename)
    database_backup_header = get_sql_backup_headers(sql_server_settings['connection_string'], database_backup_files).first
    return relevant_log_backup_sets(sql_server_settings, backup_files, database_backup_header, 0)
  end
  most_recent_backup_files_and_basename(sql_server_settings, backup_files, backup_basename)
end

.to_integrated_security(connection_string, server_only: false) ⇒ Object

Ensures a connection string is using integrated security instead of SQL Authentication.



110
111
112
113
114
115
116
117
118
 
# File 'lib/sql_cmd/format.rb', line 110

def to_integrated_security(connection_string, server_only: false)
  raise 'Failed to convert connection string to integrated security. Connection string is nil!' if connection_string.nil?
  parts = connection_string.split(';')
  new_connection_string = ''
  ommitted_parts = ['user id', 'uid', 'password', 'pwd', 'integrated security', 'trusted_connection']
  ommitted_parts += ['database', 'initial catalog'] if server_only
  parts.each { |part| new_connection_string << "#{part};" unless part.downcase.strip.start_with?(*ommitted_parts) } # only keep parts not omitted
  "#{new_connection_string}Integrated Security=SSPI;"
end

.to_unc_path(path, server_name) ⇒ Object

converts a path to unc_path if it contains a drive letter. Uses the server name provided



82
83
84
85
 
# File 'lib/sql_cmd/format.rb', line 82

def to_unc_path(path, server_name)
  return nil if path.nil? || path.empty? || server_name.nil? || server_name.empty?
  path.gsub(/(\p{L})+(:\\)/i) { "\\\\#{server_name}\\#{Regexp.last_match(1)}$\\" } # replace local paths with network paths
end

.to_utf8(text) ⇒ Object

Convert encoding of a string to utf8



121
122
123
 
# File 'lib/sql_cmd/format.rb', line 121

def to_utf8(text)
  text.encode('UTF-8', 'binary', invalid: :replace, undef: :replace, replace: '')
end

.unify_start_time(start_time, timezone: ) ⇒ Object 



59
60
61
62
63
 
# File 'lib/sql_cmd/format.rb', line 59

def unify_start_time(start_time, timezone: SqlCmd.config['environment']['timezone'])
  return EasyTime.at_timezone(Time.now, timezone) if start_time.nil? || start_time.to_s.strip.empty?
  return EasyTime.at_timezone(start_time, timezone) if start_time.is_a?(Time)
  EasyTime.stomp_timezone(start_time, timezone) # Stomp the timezone with the config timezone. If no start_time was provided, use the current time
end

.update_sql_compatibility(connection_string, database_name, compatibility_level) ⇒ Object

compatibility_level options: :sql_2008, :sql_2012, :sql_2016, :sql_2017, :sql_2019



347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
 
# File 'lib/sql_cmd/query.rb', line 347

def update_sql_compatibility(connection_string, database_name, compatibility_level) # compatibility_level options: :sql_2008, :sql_2012, :sql_2016, :sql_2017, :sql_2019
  sql_compatibility_script = ::File.read("#{sql_script_dir}/Database/SetSQLCompatibility.sql")
  compatibility_levels =
    {
      sql_2008: 100,
      sql_2012: 110,
      sql_2014: 120,
      sql_2016: 130,
      sql_2017: 140,
      sql_2019: 150,
    }
  values = { 'databasename' => database_name, 'compatibility_level' => compatibility_levels[compatibility_level] }
  EasyIO.logger.info "Ensuring SQL compatibility is set to #{compatibility_level}..."
  compatibility_result = execute_query(connection_string, sql_compatibility_script, return_type: :scalar, values: values, retries: 3)
end

.validate_logins_script(connection_string, database_name) ⇒ Object 



287
288
289
290
291
292
293
 
# File 'lib/sql_cmd/query.rb', line 287

def validate_logins_script(connection_string, database_name)
  server_name = connection_string_part(connection_string, :server)
  sql_script = ::File.read("#{sql_script_dir}/Security/GenerateValidateLoginsScript.sql")
  values = { 'databasename' => database_name }
  EasyIO.logger.debug "Creating validate logins script for logins associated with database: [#{database_name}] on [#{server_name}]..."
  execute_query(connection_string, sql_script, return_type: :scalar, values: values, readonly: true, retries: 3)
end