Welcome, guest | Sign In | My Account | Store | Cart
# SQLSMO.py
# Created by Jorge Besada
# Last modified
# 1/5/2015 changed SqlExecute to use subprocess.Popen


import os
import glob
import datetime
import subprocess

# --------------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 = subprocess.Popen(constr + '-Q"' + sqlquery + '"', stdout=subprocess.PIPE).communicate()
    except Exception as inst:
        print('Exception in SqlExecute:', inst)
        return -1

    records = []
    lst = data[0].splitlines()
    # lst[0] column names;  lst[1] dashed lines, (skip); lst[2:] data
    # now we decode
    for x in lst:
        try:
            #try default utf-8 decoding
            line = x.decode()
        except UnicodeDecodeError:
            #in case of weird characters this one works most of the time
            line = x.decode('ISO-8859-1')
        lst2 = line.split(conn['colseparator'])
        records.append(lst2)
    fieldnames = records[0]
    data = records[2:]

    return data, 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

def DeleteOlderFiles(workfolder, days):

    """
    Used to delete older backups in a folder, days is retention days
    Sample use to delete all files in C:temp with created date older than 3 days:
    DeleteOlderFiles(r'c:\temp', 3)
    """
    import os, time

    now = time.time()
    cutoff = now - (days * 86400)

    filelist = os.listdir(workfolder)
    for x in filelist:
            if os.path.isfile( workfolder + '\\' + x):
                t = os.stat( workfolder + '\\' + x )
                c = t.st_ctime

                # delete file if older than a week
                if c < cutoff:
                    print('deleting ' + x)
                    os.remove(workfolder + '\\' + x )


def KillConnections(conn, db):

    """
    Kills connections in database if database exists
    """

    s = 'SET NOCOUNT ON use master declare @db int select @db  = count(*) from master.dbo.sysdatabases where name = ' + chr(39) + \
    db + chr(39) + ' if (@db > 0) ALTER DATABASE [' + \
    db + '] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE [' + \
    db + '] SET  MULTI_USER WITH ROLLBACK IMMEDIATE'

    try:
        rows, fnames = SqlExecute(conn, s)
    except Exception as inst:
            print('Error killing connections: ', inst)
    return rows


def SyncLogins(conn, db):
    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 likes single line sql commands for the -Q option
    s = s.replace("\n", ' ')
    sqlsynclogins = 'SET NOCOUNT ON USE [' + db + '] ' + s
    try:
        rows, fnames = SqlExecute(conn, sqlsynclogins)
    except Exception as inst:
        print('Error executing fix logins: ', inst)
    return rows


def DatedString():
    """
    Returns dated string with this format
    2014_12_30_135857_4581860
    """
    from datetime import datetime
    now = str(datetime.now())
    now = now.replace('-', '_')
    now = now.replace(' ', '_')
    now = now.replace(':', '')
    now = now.replace('.', '_') + '0'

    return now

# ----------------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.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 for backup_type: 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()

        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

        # test prints
        # print('Space in drives in ' + self.conn['servername'])
        # print(dict_freespace)
        # 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 True
        else:
            return False

    def RestoreDatabase(self):
        """
        Restores the database from given backup file to given data and log folders
        There 2 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)
        """

        self.GetBackupInfo()

        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:\SQLSERVER\SQLBACKUPS'
    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 with dated file name')
    BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup_' + DatedString() + '.BAK'
    smo = SQLSMO(CONNECTION, '', '', BKFILE)
    smo.noexecute = 0
    smo.BackupDatabase()
    print('backup script: ', smo.sqlbackup)


    print('testing log backup')
    BKFILE = r'C:\SQLSERVER\SQLBACKUPS\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:\SQLSERVER\SQLBACKUPS\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:\SQLSERVER\SQLBACKUPS\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:\SQLSERVER\DATABASES'
    LOGFOLDER = r'C:\SQLSERVER\DATABASES'
    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:\SQLSERVER\DATABASES'
    LOGFOLDER = r'C:\SQLSERVER\DATABASES'
    SQLSERVER = r'(local)'

    print('testing restores')
    smo = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)

    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 == True:
        print('Ok to restore')
        smo.noexecute = 0

        # smo.KillConnections()
        print('killing connections')
        rows = KillConnections(CONNECTION, CONNECTION['db'])
        for x in rows:
            print(x)
        smo.restore_options['recovery'] = 'RECOVERY'
        print('restore options:', smo.restore_options)

        rows_restore = smo.RestoreDatabase()

        print('---------------Info on backup files from filelistonly----------------------------')
        for x in smo.dictfiles:
            print(x, smo.dictfiles[x])
        print('---------------Info on backup files from headeronly------------------------------')
        for x in smo.dictheader:
            print(x, smo.dictheader[x])

        print('data size from backup', smo.datafiles_size)
        print('log size from backup', smo.logfiles_size)


        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')
        rows = SyncLogins(smo.conn, smo.currdb)
        for x in rows:
            print(x)


    print('SQL RESTORE SCRIPT:', smo.sqlrestore)

    if go_ahead == False:
        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 10 2014-12-24 00:02:17
+++ revision 11 2015-01-08 00:03:40
@@ -1,21 +1,26 @@
 # SQLSMO.py
 # Created by Jorge Besada
+# Last modified
+# 1/5/2015 changed SqlExecute to use subprocess.Popen
 
 
 import os
 import glob
 import datetime
+import subprocess
 
 # --------------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
-    """
+    These keys are optional: defdb, colseparator
+    """
+
 
     if 'colseparator' not in conn.keys():
         conn['colseparator'] = chr(1)
@@ -26,20 +31,28 @@
 
     # now we execute
     try:
-        data = os.popen(constr + '-Q"' + sqlquery + '"').readlines()
+        data = subprocess.Popen(constr + '-Q"' + sqlquery + '"', stdout=subprocess.PIPE).communicate()
     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
-
+    lst = data[0].splitlines()
+    # lst[0] column names;  lst[1] dashed lines, (skip); lst[2:] data
+    # now we decode
+    for x in lst:
+        try:
+            #try default utf-8 decoding
+            line = x.decode()
+        except UnicodeDecodeError:
+            #in case of weird characters this one works most of the time
+            line = x.decode('ISO-8859-1')
+        lst2 = line.split(conn['colseparator'])
+        records.append(lst2)
+    fieldnames = records[0]
+    data = records[2:]
+
+    return data, fieldnames
 
 
 def GetLatestBackup(dirpath, filter='\*.*'):
@@ -58,6 +71,29 @@
     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
+
+def DeleteOlderFiles(workfolder, days):
+
+    """
+    Used to delete older backups in a folder, days is retention days
+    Sample use to delete all files in C:temp with created date older than 3 days:
+    DeleteOlderFiles(r'c:\temp', 3)
+    """
+    import os, time
+
+    now = time.time()
+    cutoff = now - (days * 86400)
+
+    filelist = os.listdir(workfolder)
+    for x in filelist:
+            if os.path.isfile( workfolder + '\\' + x):
+                t = os.stat( workfolder + '\\' + x )
+                c = t.st_ctime
+
+                # delete file if older than a week
+                if c < cutoff:
+                    print('deleting ' + x)
+                    os.remove(workfolder + '\\' + x )
 
 
 def KillConnections(conn, db):
@@ -107,6 +143,21 @@
         print('Error executing fix logins: ', inst)
     return rows
 
+
+def DatedString():
+    """
+    Returns dated string with this format
+    2014_12_30_135857_4581860
+    """
+    from datetime import datetime
+    now = str(datetime.now())
+    now = now.replace('-', '_')
+    now = now.replace(' ', '_')
+    now = now.replace(':', '')
+    now = now.replace('.', '_') + '0'
+
+    return now
+
 # ----------------End of functions----------------------------------------
 
 
@@ -141,7 +192,7 @@
         self.datafiles_size = 0
         self.logfiles_size = 0
 
-        self.backup_options = {'backup_type': 'DATABASE', 'compression': False} #options: LOG, DIFFERENTIAL
+        self.backup_options = {'backup_type': 'DATABASE', 'compression': False} #options for backup_type: LOG, DIFFERENTIAL
         self.restore_options = {'dated_file_names': False,
                                 'original_file_names': False,
                                 'restore_type': 'DATABASE',                     #options: LOG
@@ -307,25 +358,26 @@
         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)
+        # test prints
+        # print('Space in drives in ' + self.conn['servername'])
+        # print(dict_freespace)
+        # 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'
+            return True
         else:
-            return 'Bad'
+            return False
 
     def RestoreDatabase(self):
         """
@@ -403,7 +455,7 @@
 if __name__ == '__main__':
 
     print('find latest backup')
-    BACKUPFOLDER = r'D:\SQL2014\BACKUPS'
+    BACKUPFOLDER = r'C:\SQLSERVER\SQLBACKUPS'
     lst = GetLatestBackup(BACKUPFOLDER, '\AdventureWorks2012*.bak')
 
     BKFILE = lst[-1]
@@ -411,7 +463,7 @@
     print(BKFILE)
 
     CONNECTION = {}
-    SQLSERVER = r'(local)\sql2014'
+    SQLSERVER = r'(local)'
     CONNECTION['servername'] = SQLSERVER
     CONNECTION['username'] = ''
     CONNECTION['password'] = ''
@@ -419,8 +471,8 @@
     print('Connection dict:', CONNECTION)
 
 
-    print('testing full backup')
-    BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.BAK'
+    print('testing full backup with dated file name')
+    BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup_' + DatedString() + '.BAK'
     smo = SQLSMO(CONNECTION, '', '', BKFILE)
     smo.noexecute = 0
     smo.BackupDatabase()
@@ -428,7 +480,7 @@
 
 
     print('testing log backup')
-    BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.TRN'
+    BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup.TRN'
     smo = SQLSMO(CONNECTION, '', '', BKFILE)
     smo.noexecute = 0
     smo.backup_options['backup_type'] = 'LOG'
@@ -437,7 +489,7 @@
 
 
     print('testing differential backup')
-    BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.DIF'
+    BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup.DIF'
     smo = SQLSMO(CONNECTION, '', '', BKFILE)
     smo.noexecute = 0
     smo.backup_options['backup_type'] = 'DIFFERENTIAL'
@@ -446,7 +498,7 @@
 
 
     print('testing full backup again')
-    BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.BAK'
+    BKFILE = r'C:\SQLSERVER\SQLBACKUPS\AdventureWorks_Backup.BAK'
     smo = SQLSMO(CONNECTION, '', '', BKFILE)
     smo.noexecute = 0
     smo.BackupDatabase()
@@ -455,9 +507,9 @@
     BKFILE = lst[-1]
     print('Latest backup:')
     print(BKFILE)
-    DATAFOLDER = r'D:\SQL2014\DATABASES'
-    LOGFOLDER = r'D:\SQL2014\DATABASES'
-    SQLSERVER = r'(local)\sql2014'
+    DATAFOLDER = r'C:\SQLSERVER\DATABASES'
+    LOGFOLDER = r'C:\SQLSERVER\DATABASES'
+    SQLSERVER = r'(local)'
 
     print('restoring copy of database')
     CONNECTION = {}
@@ -467,9 +519,9 @@
     CONNECTION['db'] = 'AdventureWorks2012_COPY'
     print('Connection dict:', CONNECTION)
 
-    DATAFOLDER = r'D:\SQL2014\DATABASES'
-    LOGFOLDER = r'D:\SQL2014\DATABASES'
-    SQLSERVER = r'(local)\sql2014'
+    DATAFOLDER = r'C:\SQLSERVER\DATABASES'
+    LOGFOLDER = r'C:\SQLSERVER\DATABASES'
+    SQLSERVER = r'(local)'
 
     print('testing restores')
     smo = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)
@@ -482,7 +534,7 @@
     print('-----Info on existing database----------------------------------')
 
     go_ahead = smo.Ok_to_restore()
-    if go_ahead == 'Good':
+    if go_ahead == True:
         print('Ok to restore')
         smo.noexecute = 0
 
@@ -531,13 +583,13 @@
 
     print('SQL RESTORE SCRIPT:', smo.sqlrestore)
 
-    if go_ahead == 'Bad':
+    if go_ahead == False:
         print('Cannot restore')
 
 
     print('testing select query')
     conn = {}
-    conn['servername'] = '(local)\sql2014'
+    conn['servername'] = '(local)'
     conn['username'] = ''
     conn['password'] = ''
 

History