#SQLSMO.py
#Created by Jorge Besada
#Last modified 12/5/2014
import os
import glob
import datetime
#--------------Functions-----------------------------------------------------------------------------
def SqlExecute(conn, sqlquery=''):
'''
Executes sqlquery and returns lists with column names and data
The connection info is passed as a dictionary with these required keys:
servername, username,password
If username is empty will use integrated security
These keysare optional:
defdb, colseparator
'''
if 'colseparator' not in conn.keys():
conn['colseparator'] = chr(1)
if conn['username'] == '':
constr = "sqlcmd -E -S" + conn['servername'] + " /w 8192 -W " + ' -s' + conn['colseparator'] + ' '
else:
constr = "sqlcmd -U" + conn['username'] + " -P" + conn['password'] + ' -S' + conn['servername'] + ' /w 8192 -W -s' + conn['colseparator'] + ' '
#now we execute
try:
data = os.popen(constr + '-Q"' + sqlquery + '"').readlines()
except Exception as inst:
print('Exception in SqlExecute:', inst)
return -1
#data[0] column names; data[1] dashed lines, (skip); data[2:] data
records = []
fieldnames = data[0].strip().split(conn['colseparator'])
for x in range(len(data[2:])):
records.append(data[x + 2].strip().split(conn['colseparator']))
return records, fieldnames
def GetLatestBackup(dirpath, filter='\*.*'):
'''
Returns folder contents sorted by modified date
Sample use:
backupfolder = r'\\SERVERNAME\SQLBackups1\SQLBackupUser'
This brings all files
lst = GetLatestBackup(backupfolder)
Here we bring a subset using filter string
filter = '\DATABASE_*.bak'
lst = GetLatestBackup(backupfolder, filter)
'''
a = [s for s in glob.glob(dirpath + filter) if os.path.isfile(os.path.join(dirpath, s))]
a.sort(key=lambda s: os.path.getmtime(os.path.join(dirpath, s)))
return a
#----------------End of functions----------------------------------------------------------------
#-----------------Start of class SQLSMO----------------------------------------------------------
class SQLSMO:
def __init__(self, conn, datafilepath='', logfilepath='', backupfilepath='' ):
self.conn = conn
self.datafolder = datafilepath
self.logfolder = logfilepath
self.logfilename = ''
self.backupfile = backupfilepath
self.currdb = conn['db']
self.defdb = 'master'
self.noexecute = '' #1=no execute, 0=yes to execute
self.sqlrestore = ''
self.sqlbackup = ''
self.sqlfilelist = 'SET NOCOUNT ON RESTORE FILELISTONLY FROM DISK = ' + chr(39) + self.backupfile + chr(39)
self.sqlfileheader = 'SET NOCOUNT ON RESTORE HEADERONLY FROM DISK = ' + chr(39) + self.backupfile + chr(39)
self.sqlxp_fixeddrives = 'SET NOCOUNT ON EXEC master..xp_fixeddrives'
self.sqlkillconnections = 'USE master ALTER DATABASE [' + self.currdb + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE [' + self.currdb + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE'
self.sqlsphelpdb = 'SET NOCOUNT ON select name, physical_name, (size * 8/1000) as size, data_space_id from ['+ self.currdb + '].sys.database_files'
self.datafiles_size_existing = 0
self.logfiles_size_existing = 0
self.dictdbinfo = {}
self.dictfiles = {}
self.dictheader = {}
self.dictfreespace = {}
self.datafiles_size = 0
self.logfiles_size = 0
self.backup_options = {'backup_type':'DATABASE'} #options: LOG, DIFFERENTIAL
self.restore_options = {'dated_file_names': False,
'original_file_names': False,
'restore_type':'DATABASE', #options: LOG
'recovery':'RECOVERY', #options: NORECOVERY
'replace': True #option: False
}
def GetDataseInfo(self):
'''
Returns info about existing database
Return values are rows and column names
It populates these variables
self.logfiles_size_existing
self.datafiles_size_existing
'''
try:
#fnames are: name physical_name size data_space_id
rows, fnames = SqlExecute(self.conn, self.sqlsphelpdb)
except Exception as inst:
print('Error getting existing database information', inst)
self.dictdbinfo = {}
self.logfiles_size_existing = 0
self.datafiles_size_existing = 0
for x in rows:
self.dictdbinfo[x[0]] = x[1:] #using as key file name
logsize = 0
datasize = 0
for x in rows:
if x[-1] == '0': #x[-1] is data_space_id, 0 is log, anything else is data
logsize = logsize + int((x[-2]))
else:
datasize = datasize + int((x[-2]))
self.logfiles_size_existing = logsize
self.datafiles_size_existing = datasize
return rows, fnames
def GetBackupInfo(self):
'''
Returns two dictionaries
---------------dictionary with backup file contents------------------------------------------------------------
'LogicalName', 'PhysicalName', 'Type', 'FileGroupName', 'Size', 'MaxSize',
'FileId', 'CreateLSN', 'DropLSN', 'UniqueId', 'ReadOnlyLSN', 'ReadWriteLSN',
'BackupSizeInBytes', 'SourceBlockSize', 'FileGroupId', 'LogGroupGUID', 'DifferentialBaseLSN',
'DifferentialBaseGUID', 'IsReadOnly', 'IsPresent', 'TDEThumbprint'
The dictionary key is the LogicalName
---------------dictionary with contents of backup header--------------------------------------------------------
BackupTypeDescription Database
FirstRecoveryForkID {32840502-607C-441E-9439-D7364C4BBFFF}
CheckpointLSN 93565000000174200025
FamilyGUID {52405D85-E8F5-4AD8-92C9-5EC262B2A651}
SoftwareVendorId 4608
DatabaseBackupLSN 93487000002002400022
BackupFinishDate 2014-08-27 02:59:05+00:00
CodePage 0
DatabaseVersion 661
ExpirationDate None
SortOrder 52
CompatibilityLevel 100
Collation SQL_Latin1_General_CP1_CI_AS
BindingID {09CD467E-193E-4A29-82C9-709AE5CCA9D4}
HasIncompleteMetaData False
RecoveryForkID {32840502-607C-441E-9439-D7364C4BBFFF}
SoftwareVersionMinor 50
Position 1
SoftwareVersionBuild 2500
SoftwareVersionMajor 10
Containment 0
MachineName SQLSERVERNAME
BackupType 1
HasBackupChecksums False
DifferentialBaseGUID None
UserName CARNIVAL\_sql_service
IsSingleUser False
ForkPointLSN None
DifferentialBaseLSN None
IsCopyOnly False
RecoveryModel FULL
BeginsLogChain False
IsSnapshot False
IsReadOnly False
BackupName DatabaseName_backup_2014_08_27_020001_3668583
FirstLSN 93565000000174200025
BackupSetGUID {CFF54981-E420-477C-ADBB-B32FDC31EAF0}
DeviceType 2
IsDamaged False
BackupSize 233650456576
Compressed 1
UnicodeComparisonStyle 196609
HasBulkLoggedData False
Flags 512
CompressedBackupSize 56790589512
BackupDescription None
ServerName SQLSERVERNAME\INSTANCE
DatabaseName DatabaseName
UnicodeLocaleId 1033
BackupStartDate 2014-08-27 02:18:38+00:00
IsForceOffline False
LastLSN 93565000000878200001
DatabaseCreationDate 2012-06-06 15:47:24+00:00
'''
#backup files info
try:
rows, fnames = SqlExecute(self.conn, self.sqlfilelist)
except Exception as inst:
print(inst)
self.dictfiles = {}
self.logfiles_size = 0
self.datafiles_size = 0
for x in rows:
self.dictfiles[x[0]] = x[1:] #using as key file name
if x[2] == 'L': #x[2] is Type, D or L
self.logfiles_size = self.logfiles_size + int(int(x[4])/1000000) #x[4] is Size in bytes
self.logfilename = x[0]
if x[2] == 'D':
self.datafiles_size = self.datafiles_size + int(int(x[4])/1000000)
#backup header info
try:
rows, fnames = SqlExecute(self.conn, self.sqlfileheader)
except Exception as inst:
print(inst)
self.dictheader = {}
i = 0
for x in fnames:
self.dictheader[x] = rows[0][i]
if x == 'DatabaseName': self.defdb = self.dictheader[x]
i = i + 1
return self.dictfiles, self.dictheader
def GetFreeSpace(self):
'''
Returns dictionary with drive letters and free space
C 11553 D 26849 E 31829 F 64230
'''
try:
rows, fnames = SqlExecute(self.conn, self.sqlxp_fixeddrives)
except Exception as inst:
print('Error getting disk space', inst)
i = 0
self.dictfreespace = {}
for x in rows:
self.dictfreespace[x[0]] = rows[i][1]
i = i + 1
return self.dictfreespace
def Ok_to_restore(self):
'''
Check for space available in destination
If database exists (it is an overwrite) the space used by it is considered
'''
self.GetDataseInfo()
dict_freespace = self.GetFreeSpace()
print('Space in drives in ' + self.conn['servername'])
print(dict_freespace)
for x in dict_freespace.keys(): #x is the drive letter
if self.datafolder[0] == x: #if letter of datafolder matches drive letter, add to data_disk variable
data_disk = int(dict_freespace[x]) #+ self.datafiles_size_existing
if self.logfolder[0] == x: #if letter of logfolder matches drive letter, add to log_disk variable
log_disk = int(dict_freespace[x]) #+ self.logfiles_size_existing
print('Total data_disk available', data_disk)
print('Total log_disk available', log_disk)
print('self.datafiles_size from backup file', self.datafiles_size)
print('self.logfiles_size from backup file',self.logfiles_size)
print('self.datafiles_size_existing', self.datafiles_size_existing)
print('self.logfiles_size_existing',self.logfiles_size_existing)
if (int(data_disk) > int(self.datafiles_size) ) and (int(log_disk) > int(self.logfiles_size) ):
return 'Good'
else:
return 'Bad'
def KillConnections(self):
try:
rows, fnames = SqlExecute(self.conn, self.sqlkillconnections)
except Exception as inst:
print('Error killing connections: ', inst)
return rows
def RestoreDatabase(self):
'''
Restores the database from given backup file to given data and log folders
There 3 options:
1) rename files with a timestamp added to the name
(This is done to avoid file name collisions)
2) keep the original file names (not yet implemented)
3) use names based on database name with _Data and _Log suffixes (this is the default)
'''
t = ''
if self.restore_options['dated_file_names'] == True:
today = str(datetime.date.today())
t = today.replace('-', '')
s = 'USE MASTER RESTORE ' + self.restore_options['restore_type'] + ' [' + self.currdb + '] FROM DISK =' + chr(39) + self.backupfile + chr(39) + ' WITH '
i = 1
for x in self.dictfiles.keys():
if self.dictfiles[x][1] == 'D':
suffix = 'Data'
d = self.datafolder
if self.dictfiles[x][1] == 'L':
suffix = 'Log'
d = self.logfolder
self.logfilename = x
file, ext = os.path.splitext(os.path.basename(self.dictfiles[x][0]))
#file_renamed = file + '_' + t + ext
file_renamed = self.currdb + '_' + suffix + str(i) + '_' + t + ext
s = s + ' MOVE ' + chr(39) + x + chr(39) + ' TO ' + chr(39) + d + '\\' + file_renamed + chr(39) + ', '
i = i + 1
s = s + ' NOUNLOAD, ' + self.restore_options['recovery'] + ', STATS = 10'
if self.restore_options['replace'] == True:
s = s + ', REPLACE'
self.sqlrestore = s
if self.noexecute == 1:
return []
#Doing the actual restore here
try:
rows, fnames = SqlExecute(self.conn, self.sqlrestore)
except Exception as inst:
print('Error restoring database: ', inst)
return rows
def BackupDatabase(self):
if self.backup_options['backup_type'] != 'DIFFERENTIAL':
s = 'BACKUP ' + self.backup_options['backup_type'] + ' [' + self.currdb + '] TO DISK = ' + chr(39)
s = s + self.backupfile + chr(39) + ' WITH NOFORMAT, INIT, NAME = N' + chr(39)
s = s + self.currdb + ' ' + self.backup_options['backup_type'] + ' Backup' + chr(39)
else:
s = 'BACKUP DATABASE [' + self.currdb + '] TO DISK = ' + chr(39)
s = s + self.backupfile + chr(39) + ' WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N' + chr(39)
s = s + self.currdb + ' Differential Backup' + chr(39)
s = s + ' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
self.sqlbackup = s
try:
rows, fnames = SqlExecute(self.conn, s)
except Exception as inst:
print('Error restoring database: ', inst)
return rows
#---------------End of class SQLSMO------------------------------------------
#===============TEST SECTION=====================================================
if __name__ == '__main__':
print('find latest backup')
BACKUPFOLDER = r'C:\SQLSERVER\SQLBACKUPS'
lst = GetLatestBackup(BACKUPFOLDER, '\AdventureWorks2012*.bak')
BKFILE = lst[-1]
print('Latest backup:')
print(BKFILE)
DATAFOLDER = r'C:\SQLSERVER\SQLDATA'
LOGFOLDER = r'C:\SQLSERVER\SQLLOGS'
SQLSERVER = r'(local)'
CONNECTION = {}
CONNECTION['servername'] = SQLSERVER
CONNECTION['username'] = ''
CONNECTION['password'] = ''
CONNECTION['db'] = 'AdventureWorks2012_COPY'
print('Connection dict:', CONNECTION)
print('testing full backup')
BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup.BAK'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing log backup')
BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup.TRN'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.backup_options['backup_type'] = 'LOG'
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing differential backup')
BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup.DIF'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.backup_options['backup_type'] = 'DIFFERENTIAL'
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing restores')
smo = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)
try:
dbfiles, headers = smo.GetBackupInfo()
print('---------------Info on backup files from filelistonly----------------------------')
for x in dbfiles:
print(x, dbfiles[x])
print('---------------Info on backup files from headeronly------------------------------')
for x in headers:
print(x, headers[x])
except Exception as inst:
print(inst)
print('data size from backup', smo.datafiles_size)
print('log size from backup', smo.logfiles_size)
print('-----Info on existing database----------------------------------')
rows, fnames = smo.GetDataseInfo()
print(smo.dictdbinfo)
print('data size from existing db', smo.datafiles_size_existing)
print('log size from existing db', smo.logfiles_size_existing)
print('-----Info on existing database----------------------------------')
go_ahead = smo.Ok_to_restore()
if go_ahead == 'Good':
print('Ok to restore')
smo.noexecute = 0
smo.KillConnections()
smo.restore_options['recovery'] = 'RECOVERY'
print('restore options:',smo.restore_options )
rows_restore = smo.RestoreDatabase()
print('rows of restore')
print(rows_restore)
print('setting db to simple mode, change dbowner to sa, shrink log, back to full mode')
s = 'USE [master] ALTER DATABASE [' + smo.currdb + '] SET RECOVERY SIMPLE WITH NO_WAIT '
s = s + ' USE ' + smo.currdb + ' EXEC dbo.sp_changedbowner @loginame = ' + chr(39) + 'sa' + chr(39) + ', @map = false '
s = s + ' DBCC SHRINKFILE (' + chr(39) + smo.logfilename + chr(39) +' , 0, TRUNCATEONLY) '
sqlfixes1 = s + 'ALTER DATABASE [' + smo.currdb + '] SET RECOVERY FULL WITH NO_WAIT '
print('sqlfixes to apply:')
print(sqlfixes1)
try:
rows, fnames = SqlExecute(smo.conn, sqlfixes1)
except Exception as inst:
print('Error executing database fixes: ', inst)
for x in rows:
print(x)
print('resync logins')
s = '''
DECLARE @UserName nvarchar(255)
DECLARE @SQLCmd nvarchar(511)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
select @UserName + ' user name being resynced'
set @SQLCmd = 'ALTER USER '+@UserName+' WITH LOGIN = '+@UserName
EXEC (@SQLCmd)
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
'''
#sqlcmd needs single line queries!
s = s.replace("\n", ' ')
sqlsynclogins = 'SET NOCOUNT ON USE [' + smo.currdb + '] ' + s
print('smo.conn', smo.conn)
print('sync script', sqlsynclogins)
try:
rows, fnames = SqlExecute(smo.conn, sqlsynclogins)
except Exception as inst:
print('Error executing fix logins: ', inst)
for x in rows:
print(x)
print('SQL RESTORE SCRIPT:', smo.sqlrestore)
if go_ahead == 'Bad':
print('Cannot restore')
#self.backup_options = {'backup_type':'DATABASE'} #options: LOG, DIFFERENTIAL
print('testing select query')
conn = {}
conn['servername'] = '(local)'
conn['username'] = ''
conn['password'] = ''
#testing select query
rows, fnames = SqlExecute(conn, 'set nocount on select top 10 * from AdventureWorks2012.Person.Person')
print(fnames)
for x in rows:
print(x)
