Welcome, guest | Sign In | My Account | Store | Cart

This is an example of the use of the SQLSMO module. Using a csv file DBLIST_ACTIONS.csv with list of databases where you can launch multiple different database operations in parallel

Some lines of the configuration file DBLIST_ACTIONS.csv used shown below: SERVERNAME,DBNAME1,SOURCESERVER,DATAFOLDER,LOGFOLDER,DBNAME2,ACTIONS,ENABLED (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY1,RESTOREDBS1.CFG,Y (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY2,RESTOREDBS1.CFG,Y (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY3,RESTOREDBS1.CFG,Y (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY4,RESTOREDBS1.CFG,

Where: SERVERNAME: server where the database to act upon resides

DBNAME1: source database

DBNAME2: destination database (may be different from source when we restore a copy with a different name)

SOURCESERVER: this is the network (or local) folder where backups are placed

DATAFOLDER: folder for data files

LOGFOLDER: folder for log files

ACTIONS: this is the name of the configuration file (.CFG) with the list of actions

ENABLED: a Y value here will mean we want to process the line

For each line (database) you specify a configuration file (in this case RESTOREDBS1.CFG), see sample below: (one line for each, no blank lines)

RESTORE DATABASE

SET DBOWNER: sa

SYNC LOGINS

SET SIMPLE MODE

SHRINK LOG

The program will process each line in the source CSV file and for each one it will perform the set of operations described in the configuration file. This system is being used in my workplace with different configuration files for different databases (there are configuration files for restores, specific restores with more actions, backups, etc, not included here for brevity).

Every time you do a database task you just add a line in the DBLIST_ACTIONS.CSV and create (if needed) a configuration file). If you are going to actually use it include a "Y" in the ENABLED column Note: every line action in the configuration file must have been implemented in the function ActionParsing as one the entries in the big if statement.

Special features: You can specify at the start of the program if you want to really execute or not. You may want to do first a trial run setting NOEXECUTE_OPTION = 1 (instead of the default of 0). In this case the program will run and create the SQL script of the operations, not executing them. Note: it has been implemented in the restores so far, will add it to the other options later.

Threading: by default it will run as many threads as lines in the DBLIST_ACTIONS.CSV file. But you can change this option by setting a value to THREAD_POOL different than 0.

Python, 246 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
# last updated
# 1/28/2015  added sql logging


import sys
import threading
import time
import queue
import csv
from SQLSMO import *



# =============== FUNCTIONS ===========================
def LogSQL(filename, CN, sql):
    """
    Appends to given log file the sql executed with this format:
    -----------------------------------
    --Server:   SERVERNAME
    --Database: DATABASE
    script line 1
    script line 2
    ...
    -----------------------------------
    """

    today = str(datetime.date.today())
    f = open(filename, 'a')
    s = "\n\n-----------------------------------------------------\n\n"
    s = s + '--Server:   ' + CN['servername'] + "\n"
    s = s + '--Database: ' + CN['db'] + "\n"
    s = s + '--Date:     ' + today + "\n\n"
    s = s + sql
    s = s + "\n\n-----------------------------------------------------\n\n"
    f.write(s)
    f.close()

def ReadCSV(filename):
    """
    Returns array with entries marked as ENABLED=Y in source file
    """

    ary = []
    with open(filename, newline='') as f:
        reader = csv.reader(f)
        try:
            for row in reader:
                if row[-1].strip() == 'Y':
                    ary.append(row)
        except csv.Error as e:
            sys.exit('file {}, line {}: {}'.format(filename, reader.line_num, e))
    return ary


def ActionParsing(DBLIST2):
    """
    Executes the actions from the source file, passed as the DBLIST2 array
    """
    f = DBLIST2.pop()


    DESTSERVER = f[0]
    SOURCEDB = f[1]
    BACKUPFOLDER = f[2]
    DATAFOLDER = f[3]
    LOGFOLDER = f[4]
    DESTDB = f[5]
    ACTIONS = f[6]

    # open actions file, column 6
    action_list = []
    with open(ACTIONS, newline='') as g:
        action_l = g.readlines()
    for y in action_l:
        if y[0] != '#':
            action_list.append(y)

    CONNECTION = {}
    CONNECTION['servername'] = DESTSERVER
    CONNECTION['username'] = ''
    CONNECTION['password'] = ''
    CONNECTION['db'] = DESTDB


    msg = "\n"
    SQL = "\n"

    for m in action_list:
        m = m.strip()

        if m == 'BACKUP DATABASE FULL':
            # print('testing full backup')
            DB_BACKUP = BACKUPFOLDER + '\\' + DESTDB + '_backup_' + DatedString() + '.BAK'
            #database to backup is DESTDB
            smobackup = SQLSMO(CONNECTION, '', '', DB_BACKUP)
            smobackup.BackupDatabase()
            msg = msg + ' action:' + m + "\n"
            SQL = SQL + smobackup.sqlbackup + "\n\n"
            # msg = msg + " SQL: \n" + smobackup.sqlbackup + "\n"

        elif m == 'RESTORE DATABASE':
            try:
                BKFILE = GetLatestBackup(BACKUPFOLDER, '\\' + SOURCEDB + BACKUP_MASK)[-1]
            except IndexError:
                print('No backup file!')
                break

            smo3 = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)
            smo3.noexecute = NOEXECUTE_OPTION
            ok_to_restore = smo3.Ok_to_restore()
            if ok_to_restore == True:
                confirm_msg = 'Ok to restore'
            else:
                confirm_msg = 'NOT ok to restore'
                smo3.noexecute = 0

            smo3.RestoreDatabase()

            msg = msg + ' action:' + m + "\n"
            msg = msg + confirm_msg + "\n"
            SQL = SQL + smo3.sqlrestore + "\n\n"
            # msg = msg + " SQL: \n" + smo3.sqlrestore + "\n"
        elif m == 'KILL CONNECTIONS':
            r = KillConnections(CONNECTION, DESTDB)
            msg = msg + ' action:' + m + "\n"
            msg = msg + str(r) + "\n"
        elif m == 'SET DBOWNER: sa':
            s = 'USE [' + DESTDB + '] EXEC dbo.sp_changedbowner @loginame = N' + chr(39)+ 'sa' + chr(39) + ', @map = false'
            try:
                rows, fnames = SqlExecute(CONNECTION, s)
            except Exception as inst:
                print('Error executing set dbwoner sa: ', inst)
            msg = msg + ' action:' + m + "\n"
            SQL = SQL + s + "\n\n"
            # msg = msg + " SQL: \n" + s + "\n"
        elif m == 'SYNC LOGINS':
            rows = SyncLogins(CONNECTION, DESTDB)
            msg = msg + ' action:' + m + "\n"
            for y in rows:
                msg = msg + str(y) + "\n"
        elif m == 'SET SIMPLE MODE':
            s = 'USE [master] ALTER DATABASE [' + DESTDB + '] SET RECOVERY SIMPLE WITH NO_WAIT'
            try:
                rows, fnames = SqlExecute(CONNECTION, s)
            except Exception as inst:
                print('Error executing set simple mode ', inst)
            msg = msg + ' action:' + m + "\n"
            SQL = SQL + s + "\n\n"
            # msg = msg + " SQL: \n" + s + "\n"
        elif m == 'SHRINK LOG':
            s = '''
            declare @logfilename varchar(200)
            select @logfilename = name  from sysfiles where groupid = 0
            DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)
            '''
            s = 'USE [' + DESTDB + '] ' + s
            try:
                rows, fnames = SqlExecute(CONNECTION, s)
            except Exception as inst:
                print('Error executing shrink log ', inst)
            msg = msg + ' action:' + m + "\n"
            SQL = SQL + s + "\n\n"
            # msg = msg +  " SQL: \n" + s + "\n"
    with lock:
        print(msg)
        print(f)
        LogSQL('SqlScripts.sql', CONNECTION, SQL)
    return f


#threading functions

def do_work(item):
    """
    do lengthy work
    """

    start2 = time.perf_counter()         # saving start time
    line = ActionParsing(DBLIST2)

    DICT_RESULTS = {}
    ThreadItem = threading.current_thread().name + '_' + str(item)
    DICT_RESULTS[ThreadItem] = {}
    DICT_RESULTS[ThreadItem]['line processed:'] = line

    time_elapsed = time.perf_counter() - start2
    DICT_RESULTS[ThreadItem]['ELAPSED TIME'] = time_elapsed
    DICT_RESULTS2.append(DICT_RESULTS)
    # -----------End of do_work------------------------------

def worker():
    """
    The worker thread pulls an item from the queue and processes it
    """

    while True:
        item = q.get()
        do_work(item)
        q.task_done()

# ===============End of functions=======================


# ============== Program Start=========================
# some global values
DICT_RESULTS2 = []                      # used to store execution messages, to be displayed at the end
BACKUP_MASK = '*.bak'                   # backup mask for files in backup folder
NOEXECUTE_OPTION = 0                    # 1 = no execution, 0 = yes execution
THREAD_POOL = 0                         # 0 means will process all entries in source file in its own thread

# csv file with list of Servers,DBs and desired actions
SOURCEFILE = r'C:\Users\python\PycharmProjects\codecamp\DBLIST_ACTIONS.csv'
DBLIST2 = ReadCSV(SOURCEFILE)
items_to_process = len(DBLIST2)
if THREAD_POOL == 0:
    THREAD_POOL = items_to_process

print('Total items to process:', items_to_process)
print('Thread pool (concurrent processes): ', THREAD_POOL)
if NOEXECUTE_OPTION == 0:
    print('Execution option is yes')
else:
    print('No execution')

# lock to serialize console output
lock = threading.Lock()

# Create the queue and thread pool.
q = queue.Queue()
for i in range(THREAD_POOL):
    t = threading.Thread(target=worker)
    t.daemon = True  # thread dies when main thread (only non-daemon thread) exits.
    t.start()

# stuff work items on the queue.
start1 = time.perf_counter()         # saving start time
for item in range(len(DBLIST2)):
    # print('item:', item)
    q.put(item)

q.join()       # block until all tasks are done


print('time:', time.perf_counter() - start1)
for x in DICT_RESULTS2:
    print(x)

The production version of this program includes a reset of the ENABLED column (clear the Y value) in each run, to avoid accidental re-execution of processed lines. I removed the code for clarity. The NOEXECUTE_OPTION = 1 has proven to be an useful feature. In the case of restores, which is one of the more complex tasks; you can check in advance if the restore is possible (by means of the Ok_to_restore call from SQLSMO ) and generate the SQL script of the restore (file SqlScripts.SQL). With time you will have a big DBLIST_ACTIONS.CSV with a smaller set of configuration files that can do many of your daily tasks: just enable adding a Y to the line