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

I needed a Python library equivalent to the SQL Server Management Objects (SMO) and did not find it, so I created my own version. It does not follow the standard SMO objects names. So far it has a set of basic functionality: to make backups, restores with move, sync logins for restored databases, check disk space. I included a good sized testing harness to get you going. This version uses sqlcmd for connectivity. I use this SQLSMO library as an imported module in several of my Python applications. It has been tested with SQL 2012 and SQL 2014, it should function with versions down to 2005.

Python, 706 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
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
# SQLSMO.py
# Created by Jorge Besada
# Last modified 3/16/2015

import os
import glob
import time
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)
    """

    # os, time already imported

    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 DECLARE @kill varchar(8000) = ' + chr(39) + chr(39) + ';'
    s += ' SELECT @kill = @kill + ' + chr(39) + 'kill ' + chr(39) + '  + CONVERT(varchar(5), spid) + ' + chr(39) + ';' + chr(39)
    s += ' FROM master..sysprocesses WHERE dbid = db_id(' + chr(39) + db + chr(39) + ')'
    s += ' select @kill; EXEC(@kill);'

    rows, fnames = [], []
    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 needs single line sql commands for the -Q option
    s = s.replace("\n", ' ')
    sqlsynclogins = 'SET NOCOUNT ON USE [' + db + '] ' + s
    rows, fnames = [], []
    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


def BuildTlogSQL(dbname, sourcedb, bkfolder, recovery = 'NORECOVERY', BACKUP_MASK = '_backup_20*.BAK', LOG_MASK = '_backup_20*.TRN'):
    """
    - check for log backups
    DECLARE backupFiles CURSOR FOR
    SELECT backupFile
    FROM @fileList
    WHERE backupFile LIKE '%.TRN'
    AND backupFile LIKE @dbName + '%'
    AND backupFile > @lastFullBackup
    OPEN backupFiles
    -- Loop through all the files for the database
    FETCH NEXT FROM backupFiles INTO @backupFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
           + @backupPath + @backupFile + ''' WITH NORECOVERY'
       PRINT @cmd
       FETCH NEXT FROM backupFiles INTO @backupFile
    END
    CLOSE backupFiles
    DEALLOCATE backupFiles
    -- 6 - put database in a useable state
    SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
    PRINT @cmd
    """

    BKFILE = '____.BAK'
    try:
        BKFILE = GetLatestBackup(bkfolder, '\\' + sourcedb + BACKUP_MASK)[-1]   # BACKUP_MASK = '_backup_20*.BAK'
    except IndexError:
        pass

    s = ''

    filter = '\\' + sourcedb + LOG_MASK
    filelist = GetLatestBackup(bkfolder, filter )

    if len(filelist) == 0:
        print('No log files for ' + sourcedb)
        if recovery == 'RECOVERY':
            s = 'RESTORE DATABASE ' + dbname + ' WITH RECOVERY' + chr(13) + chr(10) + 'GO ' + chr(13) + chr(10)
            return s
    else:
        for x in filelist:
            if x[:-4] > BKFILE[:-4]:        # this means the log file is older than the latest full backup file
                s += 'RESTORE LOG ' + dbname + ' FROM DISK = ' + chr(39) + x + chr(39) + \
                    ' WITH NORECOVERY' + chr(13) + chr(10) + 'GO ' + chr(13) + chr(10)
        if x[:-4] > BKFILE[:-4]:
            if recovery == 'RECOVERY':
                s += 'RESTORE DATABASE ' + dbname + ' WITH RECOVERY' + chr(13) + chr(10) + 'GO ' + chr(13) + chr(10)
        return s

# ----------------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    #info from existing database --- populated by GetDatabaseInfo
        self.logfiles_size_existing = 0     #info from existing database --- populated by GetDatabaseInfo

        self.dictdbinfo = {}                #info from existing database --- populated by GetDatabaseInfo
        self.dictfiles = {}                 #info from backup file       --- populated by GetBackupInfo
        self.dictheader = {}                #info from backup file       --- populated by GetBackupInfo
        self.dictfreespace = {}             #info on disk space          --- populated by GetFreeSpace

        self.datafiles_size = 0             #info from backup file       --- populated by GetBackupInfo
        self.logfiles_size = 0              #info from backup file       --- populated by GetBackupInfo

        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
        """
        self.logfiles_size_existing  = 0.0
        self.datafiles_size_existing = 0.0

        rows, fnames = [], []
        try:
            # fnames are: name physical_name size data_space_id
            rows, fnames = SqlExecute(self.conn, self.sqlsphelpdb)
        except Exception as inst:
            print('No existing database information', inst)
            self.dictdbinfo = {}
            return rows, fnames


        for x in rows:
            self.dictdbinfo[x[0]] = x[1:]       #using as key file name

        logsize = 0.0
        datasize = 0.0
        for x in rows:
            if x[-1] == '0':                    #x[-1] is data_space_id, 0 is log, anything else is data
                gb = float(x[-2])/1000
                logsize += gb
            else:
                gb = float(x[-2])/1000
                datasize += gb

        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 DOMAIN\_sql_account
        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
        rows, fnames = [], []
        try:
            rows, fnames = SqlExecute(self.conn, self.sqlfilelist)
        except Exception as inst:
            print(inst)
            self.dictfiles = {}

        self.logfiles_size = 0.0
        self.datafiles_size = 0.0
        for x in rows:
            self.dictfiles[x[0]] = x[1:]                                    #using as key file name
            try:
                if x[2] == 'L':                                             #x[2] is Type, D or L
                    self.logfiles_size += float(float(x[4])/1000000000)     #x[4] is Size in bytes
                    self.logfilename = x[0]
            except IndexError:
                print('-Check backup file exists')
                print('-Check backup file version, maybe higher than current SQL version')
                print('-Check SQL server account has access to the backup file')
                exit()
            if x[2] == 'D':
                self.datafiles_size += float(float(x[4])/1000000000)

        # 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 += 1

        return self.dictfiles, self.dictheader

    def GetFreeSpace(self):
        """
        Returns dictionary with drive letters and free space in GB
        C	11.553        D	26.849        E	31.829        F	64.230
        """

        self.dictfreespace = {}
        rows, fnames = [], []
        try:
            rows, fnames = SqlExecute(self.conn, self.sqlxp_fixeddrives)
        except Exception as inst:
            print('Error getting disk space', inst)
            return self.dictfreespace

        i = 0

        for x in rows:
            self.dictfreespace[x[0]] = str(float(rows[i][1])/1000)   # changing to gb
            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
        """
        # Populate these two: self.datafiles_size_existing, self.logfiles_size_existing
        self.GetDataseInfo()

        # Populate these two: self.datafiles_size, self.logfiles_size
        self.GetBackupInfo()

        dict_freespace = self.GetFreeSpace()
        # print('freespace dict')
        # print(dict_freespace)
        data_disk = 0.0
        log_disk = 0.0
        for x in dict_freespace.keys():                             #x is the drive letter
            if self.datafolder[0].upper() == x.upper():                             #if letter of datafolder matches drive letter, add to data_disk variable
                data_disk += float(dict_freespace[x])
            if self.logfolder[0].upper() == x.upper():                              #if letter of logfolder matches drive letter, add to log_disk variable
                log_disk += float(dict_freespace[x])



        # 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)


        # this is for the case the database does not exist:
        # compare disk space with backup file disk space
        # we decide using self.datafiles_size (0 means database does not exist)
        # but we need to check if it is a single drive for both log and data
        if self.datafolder[0] != self.logfolder[0]:     # case log and data in different drives
            # print('log and data different drives')
            if self.datafiles_size_existing == 0:       # case no existing database
                # print('database does not exist, log and data on different drives')
                if (float(data_disk) > float(self.datafiles_size)) and (float(log_disk) > float(self.logfiles_size)):  # disk space > backup space requiered
                    return True
                else:
                    return False
            else:                                       # case database exists
                # print('database exists, log and data on different drives')
                if (float(data_disk) > (float(self.datafiles_size) - float(self.datafiles_size_existing))) \
                    and (float(log_disk) > (float(self.logfiles_size) - float(self.logfiles_size_existing))):
                    return True
                else:
                    return False
        else:                                         # case log and data are in same drive
            # print('log and data same drive')
            if self.datafiles_size_existing == 0:                            # no existing database
                # print('database does not exist, log and data on same drive')
                if float(data_disk) > (float(self.datafiles_size) + float(self.logfiles_size)):
                    return True
                else:
                    return False
            else:
                # print('database exists, log and data on same drive')
                if float(data_disk > (float(self.datafiles_size) + float(self.logfiles_size)) - (float(self.datafiles_size_existing) + float(self.logfiles_size_existing))):
                    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']:
            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 '
        filecount = 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 filecount > 1:           # after 2 values (0 and 1) we add one to the file name
                suffix2 = str(filecount)
            file_renamed = self.currdb + '_' + suffix + suffix2 + t + ext
            s += ' MOVE ' + chr(39) + x + chr(39) + ' TO ' + chr(39) + d + '\\' + file_renamed + chr(39) + ', '
            filecount += 1

        s = s + ' NOUNLOAD, ' + self.restore_options['recovery'] + ', STATS = 10'
        if self.restore_options['replace']:
            s += ', REPLACE'

        self.sqlrestore = s
        if self.noexecute == 1:
            return []

        # Doing the actual restore here. NEW: added code to kill connections in the same execution

        s = 'SET NOCOUNT ON DECLARE @kill varchar(8000) = ' + chr(39) + chr(39) + ';'
        s += ' SELECT @kill = @kill + ' + chr(39) + 'kill ' + chr(39) + '  + CONVERT(varchar(5), spid) + ' + chr(39) + ';' + chr(39)
        s += ' FROM master..sysprocesses WHERE dbid = db_id(' + chr(39) + self.currdb + chr(39) + ')'
        s += ' select @kill; EXEC(@kill); '
        s += self.sqlrestore
        rows, fnames = [], []
        try:
            rows, fnames = SqlExecute(self.conn, s)
        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 += self.backupfile + chr(39) + ' WITH NOFORMAT, INIT, NAME = N' + chr(39)
            s += self.currdb + ' ' + self.backup_options['backup_type'] + ' Backup' + chr(39)
        else:
            s = 'BACKUP DATABASE [' + self.currdb + '] TO DISK = ' + chr(39)
            s += self.backupfile + chr(39) + ' WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N' + chr(39)
            s += self.currdb + ' Differential Backup' + chr(39)
        if self.backup_options['compression']:
            s += ' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'
        else:
            s += ' , SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
        self.sqlbackup = s
        rows, fnames = [], []
        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:\SQL2014\BACKUPS'
    lst = GetLatestBackup(BACKUPFOLDER, '\AdventureWorks2012*.bak')

    BKFILE = lst[-1]
    print('Latest backup:')
    print(BKFILE)

    CONNECTION = {}
    SQLSERVER = r'(local)\sql2014'
    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:\SQL2014\BACKUPS\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:\SQL2014\BACKUPS\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:\SQL2014\BACKUPS\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:\SQL2014\BACKUPS\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:\SQL2014\DATA'
    LOGFOLDER = r'C:\SQL2014\LOG'
    SQLSERVER = r'(local)\sql2014'

    print('restoring copy of database')
    CONNECTION = {}
    CONNECTION['servername'] = SQLSERVER
    CONNECTION['username'] = ''
    CONNECTION['password'] = ''
    CONNECTION['db'] = 'AdventureWorks2012_COPY_NEW'

    print('Connection dict:', CONNECTION)

    DATAFOLDER = r'C:\SQL2014\DATA'
    LOGFOLDER = r'C:\SQL2014\LOG'

    SQLSERVER = r'(local)\sql2014'

    print('testing restores checking using Ok_to_restore')
    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:
        print('Ok to restore')
        print('killing connections is now part of the restore call, no need to call the function KillConnections')
        # Default is RECOVERY, put here as reminder you can change to NORECOVERY
        smo.restore_options['recovery'] = 'RECOVERY'
        print('restore options:', smo.restore_options)
        # Now we restore
        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('Lets do some fixes: 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 += ' USE ' + smo.currdb + ' EXEC dbo.sp_changedbowner @loginame = ' + chr(39) + 'sa' + chr(39) + ', @map = false '
        s += ' DBCC SHRINKFILE (' + chr(39) + smo.logfilename + chr(39) + ' , 0, TRUNCATEONLY) '
        sqlfixes1 = s + 'ALTER DATABASE [' + smo.currdb + '] SET RECOVERY FULL WITH NO_WAIT '
        print('sql 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)
    else:
        print('Cannot restore')
    print('SQL RESTORE SCRIPT:', smo.sqlrestore)

    print('testing select query')
    conn = {}
    conn['servername'] = '(local)\sql2014'
    conn['username'] = ''
    conn['password'] = ''


    #testing select query
    s = 'set nocount on select top 10 BusinessEntityID, FirstName, MiddleName, \
        LastName, ModifiedDate from AdventureWorks2012.Person.Person'
    rows, fnames = SqlExecute(conn, s)
    print(fnames)
    for x in rows:
        print(x)

Latest updates: changed SqlExecute to use subprocess.Popen Did fixes to Ok_to_restore Updated RestoreDatabase (added killconnections to it) Did fix in GetDatabaseInfo for when database does not exist Fixed case sensitive issue in database folder names

1 comment

Jorge Besada (author) 9 years, 4 months ago  # | flag

Some hints on use:

  • you can script your operations and not execute right now (set self.noexecute to 1)
  • easy to act on multiple servers and file locations: just create a source file with the connection info and desired file locations, and loop through it instantiating the smo objects as needed. I use it with threads to launch multiple restores at the same time
Created by Jorge Besada on Sat, 6 Dec 2014 (MIT)
Python recipes (4591)
Jorge Besada's recipes (5)

Required Modules

  • (none specified)

Other Information and Tasks