Welcome, guest | Sign In | My Account | Store | Cart
# 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)'

History