# SQLSMO.py
# Created by Jorge Besada
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 keys are 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 = 0 #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', 'compression': False} #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) 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 = 0
suffix2 = ''
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]))
if i > 1: #after 2 values (0 and 1) we add one to the file name
suffix2 = str(i)
file_renamed = self.currdb + '_' + suffix + suffix2 + '_' + 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)
if self.backup_options['compression'] == True:
s = s + ' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
else:
s = s + ' , SKIP, NOREWIND, NOUNLOAD, STATS = 10'
self.sqlbackup = s
if self.noexecute == 0:
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:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP'
lst = GetLatestBackup(BACKUPFOLDER, '\AdventureWorks2012*.bak')
BKFILE = lst[-1]
print('Latest backup:')
print(BKFILE)
CONNECTION = {}
SQLSERVER = r'(local)'
CONNECTION['servername'] = SQLSERVER
CONNECTION['username'] = ''
CONNECTION['password'] = ''
CONNECTION['db'] = 'AdventureWorks2012'
print('Connection dict:', CONNECTION)
print('testing full backup')
BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.BAK'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing log backup')
BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.TRN'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.backup_options['backup_type'] = 'LOG'
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing differential backup')
BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.DIF'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.backup_options['backup_type'] = 'DIFFERENTIAL'
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing full backup again')
BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.BAK'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
BKFILE = lst[-1]
print('Latest backup:')
print(BKFILE)
DATAFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
LOGFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
SQLSERVER = r'(local)'
print('restoring copy of database')
CONNECTION = {}
CONNECTION['servername'] = SQLSERVER
CONNECTION['username'] = ''
CONNECTION['password'] = ''
CONNECTION['db'] = 'AdventureWorks2012_COPY'
print('Connection dict:', CONNECTION)
DATAFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
LOGFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
SQLSERVER = r'(local)'
print('testing restores')
smo = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)
smo.restore_options['original_file_names'] = True
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')
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)
Diff to Previous Revision
--- revision 2 2014-12-06 14:09:19
+++ revision 3 2014-12-10 18:26:54
@@ -1,21 +1,22 @@
-#SQLSMO.py
-#Created by Jorge Besada
-#Last modified 12/5/2014
+# SQLSMO.py
+# Created by Jorge Besada
+
import os
import glob
import datetime
-#--------------Functions-----------------------------------------------------------------------------
+# --------------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
- '''
+ These keys are optional:
+ defdb, colseparator
+ """
+
if 'colseparator' not in conn.keys():
conn['colseparator'] = chr(1)
if conn['username'] == '':
@@ -23,14 +24,14 @@
else:
constr = "sqlcmd -U" + conn['username'] + " -P" + conn['password'] + ' -S' + conn['servername'] + ' /w 8192 -W -s' + conn['colseparator'] + ' '
- #now we execute
+ # 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
+ # data[0] column names; data[1] dashed lines, (skip); data[2:] data
records = []
fieldnames = data[0].strip().split(conn['colseparator'])
@@ -42,7 +43,7 @@
def GetLatestBackup(dirpath, filter='\*.*'):
- '''
+ """
Returns folder contents sorted by modified date
Sample use:
backupfolder = r'\\SERVERNAME\SQLBackups1\SQLBackupUser'
@@ -51,7 +52,9 @@
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
@@ -70,7 +73,7 @@
self.backupfile = backupfilepath
self.currdb = conn['db']
self.defdb = 'master'
- self.noexecute = '' #1=no execute, 0=yes to execute
+ self.noexecute = 0 #1=no execute, 0=yes to execute
self.sqlrestore = ''
self.sqlbackup = ''
@@ -91,22 +94,24 @@
self.datafiles_size = 0
self.logfiles_size = 0
- self.backup_options = {'backup_type':'DATABASE'} #options: LOG, DIFFERENTIAL
+ self.backup_options = {'backup_type': 'DATABASE', 'compression': False} #options: LOG, DIFFERENTIAL
self.restore_options = {'dated_file_names': False,
'original_file_names': False,
- 'restore_type':'DATABASE', #options: LOG
- 'recovery':'RECOVERY', #options: NORECOVERY
+ '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
@@ -134,7 +139,7 @@
return rows, fnames
def GetBackupInfo(self):
- '''
+ """
Returns two dictionaries
---------------dictionary with backup file contents------------------------------------------------------------
'LogicalName', 'PhysicalName', 'Type', 'FileGroupName', 'Size', 'MaxSize',
@@ -196,7 +201,7 @@
IsForceOffline False
LastLSN 93565000000878200001
DatabaseCreationDate 2012-06-06 15:47:24+00:00
- '''
+ """
#backup files info
try:
@@ -231,10 +236,11 @@
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:
@@ -248,10 +254,10 @@
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()
@@ -268,10 +274,10 @@
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.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) ):
+ 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'
@@ -284,14 +290,13 @@
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)
- '''
+ 2) use names based on database name with _Data and _Log suffixes (this is the default)
+ """
t = ''
if self.restore_options['dated_file_names'] == True:
@@ -299,7 +304,8 @@
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
+ i = 0
+ suffix2 = ''
for x in self.dictfiles.keys():
if self.dictfiles[x][1] == 'D':
suffix = 'Data'
@@ -309,10 +315,12 @@
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) + ', '
+ if i > 1: #after 2 values (0 and 1) we add one to the file name
+ suffix2 = str(i)
+ file_renamed = self.currdb + '_' + suffix + suffix2 + '_' + 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'
@@ -338,37 +346,84 @@
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'
+ if self.backup_options['compression'] == True:
+ s = s + ' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
+ else:
+ s = s + ' , SKIP, NOREWIND, NOUNLOAD, 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 self.noexecute == 0:
+ 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'
+ BACKUPFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP'
lst = GetLatestBackup(BACKUPFOLDER, '\AdventureWorks2012*.bak')
BKFILE = lst[-1]
print('Latest backup:')
print(BKFILE)
- DATAFOLDER = r'C:\SQLSERVER\SQLDATA'
- LOGFOLDER = r'C:\SQLSERVER\SQLLOGS'
+
+ CONNECTION = {}
SQLSERVER = r'(local)'
-
-
+ CONNECTION['servername'] = SQLSERVER
+ CONNECTION['username'] = ''
+ CONNECTION['password'] = ''
+ CONNECTION['db'] = 'AdventureWorks2012'
+ print('Connection dict:', CONNECTION)
+
+
+ print('testing full backup')
+ BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.BAK'
+ smo = SQLSMO(CONNECTION, '', '', BKFILE)
+ smo.noexecute = 0
+ smo.BackupDatabase()
+ print('backup script: ', smo.sqlbackup)
+
+
+ print('testing log backup')
+ BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.TRN'
+ smo = SQLSMO(CONNECTION, '', '', BKFILE)
+ smo.noexecute = 0
+ smo.backup_options['backup_type'] = 'LOG'
+ smo.BackupDatabase()
+ print('backup script: ', smo.sqlbackup)
+
+
+ print('testing differential backup')
+ BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.DIF'
+ smo = SQLSMO(CONNECTION, '', '', BKFILE)
+ smo.noexecute = 0
+ smo.backup_options['backup_type'] = 'DIFFERENTIAL'
+ smo.BackupDatabase()
+ print('backup script: ', smo.sqlbackup)
+
+
+ print('testing full backup again')
+ BKFILE = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\BACKUP\AdventureWorks_Backup.BAK'
+ smo = SQLSMO(CONNECTION, '', '', BKFILE)
+ smo.noexecute = 0
+ smo.BackupDatabase()
+ print('backup script: ', smo.sqlbackup)
+
+
+
+ BKFILE = lst[-1]
+ print('Latest backup:')
+ print(BKFILE)
+ DATAFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
+ LOGFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
+ SQLSERVER = r'(local)'
+
+ print('restoring copy of database')
CONNECTION = {}
CONNECTION['servername'] = SQLSERVER
CONNECTION['username'] = ''
@@ -377,29 +432,14 @@
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)
+ DATAFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
+ LOGFOLDER = r'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
+ SQLSERVER = r'(local)'
print('testing restores')
smo = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)
+ smo.restore_options['original_file_names'] = True
try:
dbfiles, headers = smo.GetBackupInfo()
@@ -487,6 +527,8 @@
if go_ahead == 'Bad':
print('Cannot restore')
+
+
print('testing select query')
conn = {}
conn['servername'] = '(local)'