# 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
def KillConnections(conn, db):
s = 'USE master 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
# ----------------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: 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 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'D:\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')
BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.BAK'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.BackupDatabase()
print('backup script: ', smo.sqlbackup)
print('testing log backup')
BKFILE = r'D:\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'D:\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'D:\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'D:\SQL2014\DATABASES'
LOGFOLDER = r'D:\SQL2014\DATABASES'
SQLSERVER = r'(local)\sql2014'
print('restoring copy of database')
CONNECTION = {}
CONNECTION['servername'] = SQLSERVER
CONNECTION['username'] = ''
CONNECTION['password'] = ''
CONNECTION['db'] = 'AdventureWorks2012_COPY'
print('Connection dict:', CONNECTION)
DATAFOLDER = r'D:\SQL2014\DATABASES'
LOGFOLDER = r'D:\SQL2014\DATABASES'
SQLSERVER = r'(local)\sql2014'
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 == 'Good':
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 == 'Bad':
print('Cannot restore')
print('testing select query')
conn = {}
conn['servername'] = '(local)\sql2014'
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 7 2014-12-15 00:19:38
+++ revision 8 2014-12-23 20:23:45
@@ -1,5 +1,6 @@
# SQLSMO.py
# Created by Jorge Besada
+
import os
import glob
@@ -57,6 +58,50 @@
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 KillConnections(conn, db):
+
+ s = 'USE master 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
+
# ----------------End of functions----------------------------------------
@@ -78,7 +123,6 @@
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
@@ -92,12 +136,12 @@
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: 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
+ 'restore_type': 'DATABASE', #options: LOG
+ 'recovery': 'RECOVERY', #options: NORECOVERY
+ 'replace': True #option: False
}
def GetDataseInfo(self):
@@ -169,7 +213,7 @@
BackupType 1
HasBackupChecksums False
DifferentialBaseGUID None
- UserName DOMAIN\account
+ UserName CARNIVAL\_sql_service
IsSingleUser False
ForkPointLSN None
DifferentialBaseLSN None
@@ -278,13 +322,6 @@
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
@@ -294,7 +331,6 @@
2) use names based on database name with _Data and _Log suffixes (this is the default)
"""
- #self.GetDataseInfo()
self.GetBackupInfo()
t = ''
@@ -302,9 +338,6 @@
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():
@@ -325,7 +358,6 @@
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:
@@ -366,7 +398,7 @@
if __name__ == '__main__':
print('find latest backup')
- BACKUPFOLDER = r'C:\SQLSERVER\BACKUPS'
+ BACKUPFOLDER = r'D:\SQL2014\BACKUPS'
lst = GetLatestBackup(BACKUPFOLDER, '\AdventureWorks2012*.bak')
BKFILE = lst[-1]
@@ -374,7 +406,7 @@
print(BKFILE)
CONNECTION = {}
- SQLSERVER = r'(local)'
+ SQLSERVER = r'(local)\sql2014'
CONNECTION['servername'] = SQLSERVER
CONNECTION['username'] = ''
CONNECTION['password'] = ''
@@ -383,7 +415,7 @@
print('testing full backup')
- BKFILE = r'C:\SQLSERVER\BACKUPS\AdventureWorks_Backup.BAK'
+ BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.BAK'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.BackupDatabase()
@@ -391,7 +423,7 @@
print('testing log backup')
- BKFILE = r'C:\SQLSERVER\BACKUPS\AdventureWorks_Backup.TRN'
+ BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.TRN'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.backup_options['backup_type'] = 'LOG'
@@ -400,7 +432,7 @@
print('testing differential backup')
- BKFILE = r'C:\SQLSERVER\BACKUPS\AdventureWorks_Backup.DIF'
+ BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.DIF'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.backup_options['backup_type'] = 'DIFFERENTIAL'
@@ -409,7 +441,7 @@
print('testing full backup again')
- BKFILE = r'C:\SQLSERVER\BACKUPS\AdventureWorks_Backup.BAK'
+ BKFILE = r'D:\SQL2014\BACKUPS\AdventureWorks_Backup.BAK'
smo = SQLSMO(CONNECTION, '', '', BKFILE)
smo.noexecute = 0
smo.BackupDatabase()
@@ -418,9 +450,9 @@
BKFILE = lst[-1]
print('Latest backup:')
print(BKFILE)
- DATAFOLDER = r'C:\SQLSERVER\DATA'
- LOGFOLDER = r'C:\SQLSERVER\LOG'
- SQLSERVER = r'(local)'
+ DATAFOLDER = r'D:\SQL2014\DATABASES'
+ LOGFOLDER = r'D:\SQL2014\DATABASES'
+ SQLSERVER = r'(local)\sql2014'
print('restoring copy of database')
CONNECTION = {}
@@ -430,9 +462,9 @@
CONNECTION['db'] = 'AdventureWorks2012_COPY'
print('Connection dict:', CONNECTION)
- DATAFOLDER = r'C:\SQLSERVER\DATA'
- LOGFOLDER = r'C:\SQLSERVER\LOG'
- SQLSERVER = r'(local)'
+ DATAFOLDER = r'D:\SQL2014\DATABASES'
+ LOGFOLDER = r'D:\SQL2014\DATABASES'
+ SQLSERVER = r'(local)\sql2014'
print('testing restores')
smo = SQLSMO(CONNECTION, DATAFOLDER, LOGFOLDER, BKFILE)
@@ -449,7 +481,11 @@
print('Ok to restore')
smo.noexecute = 0
- smo.KillConnections()
+ # 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)
@@ -483,37 +519,10 @@
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 likes single line sql commands for the -Q option
- 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)
+ rows = SyncLogins(smo.conn, smo.currdb)
for x in rows:
print(x)
+
print('SQL RESTORE SCRIPT:', smo.sqlrestore)
@@ -523,7 +532,7 @@
print('testing select query')
conn = {}
- conn['servername'] = '(local)'
+ conn['servername'] = '(local)\sql2014'
conn['username'] = ''
conn['password'] = ''