09-11-2017, Saat: 13:32
(Son Düzenleme: 09-11-2017, Saat: 13:58, Düzenleyen: TescilsizUzman.)
Merhaba,
MSSQL veri tabanını otomatik olarak yedekleyip, sıkıştırıp, bir FTP sunucuya da yedekleyebileceğiniz ve bu tanımların tamamının parametrik olduğu, her şekilde çalıştırabileceğiniz (komut satırı, zamanlanmış görev, elle) bir konsol uygulamasını paylaşıyorum. Faydalı olacağını umarım.
Proje;
mad.ini dosyası içeriği;
MSSQL veri tabanını otomatik olarak yedekleyip, sıkıştırıp, bir FTP sunucuya da yedekleyebileceğiniz ve bu tanımların tamamının parametrik olduğu, her şekilde çalıştırabileceğiniz (komut satırı, zamanlanmış görev, elle) bir konsol uygulamasını paylaşıyorum. Faydalı olacağını umarım.
Proje;
program madbcs; {$APPTYPE CONSOLE} uses SysUtils, IniFiles, Forms, DB, DBAccess, Uni, UniProvider, SQLServerUniProvider, ShellAPI, Windows, Controls, Classes, idftp, IdFTPListTypes, IdFTPCommon, IdFTPList; var conn : TUniConnection; madINIFile : TIniFile; FirstRun : string; db_host : string; db_port : integer; database : string; db_user : string; db_password : string; ftp_host : string; ftp_port : integer; ftp_user : string; ftp_password : string; ftp_path : string; ftp_status : integer; ftp_send_on_compression_error : integer; backup_file : string; backup_file_full : string; compressed_file : string; upload_file : string; backup_path : string; backup_database : string; backup_status : integer; backup_compression : integer; backup_delete_original_when_compression_success : integer; compression_result : Boolean; ErrMsg : String; msg : String; function Encrypt(const AText: WideString; APassword : WideString): WideString; external 'Rijndael.dll'; function Decrypt(const AText: WideString; APassword : WideString): WideString; external 'Rijndael.dll'; function getIniParam(caption : String; param : String; defvalue : String = ''; hashed : Boolean = false) : String; var _INIFile : TIniFile; ret : String; begin try _INIFile := TIniFile.Create(ExtractFilePath(Application.ExeName)+ 'mad.ini'); ret := _INIFile.ReadString(caption,param,defvalue); if hashed then begin ret := Decrypt(ret,'852357jMl???'); end; except // end; _INIFile.Free; result := ret; end; function SearchAndReplace( sSrc, sLookFor, sReplaceWith : string ) : string; var nPos, nLenLookFor : integer; begin nPos := Pos( sLookFor, sSrc ); nLenLookFor := Length( sLookFor ); while(nPos > 0)do begin Delete( sSrc, nPos, nLenLookFor ); Insert( sReplaceWith, sSrc, nPos ); nPos := Pos( sLookFor, sSrc ); end; Result := sSrc; end; procedure Split(const Delimiter: Char; Input: string; const Strings: TStrings) ; begin Assert(Assigned(Strings)) ; Strings.Clear; Strings.Delimiter := Delimiter; Strings.DelimitedText := Input; end; procedure connectDB; begin conn := TUniConnection.Create(nil); conn.ProviderName := 'SQL Server'; conn.Server := db_host; conn.Port := db_port; conn.Database := database; conn.Username := db_user; conn.Password := db_password; conn.LoginPrompt := false; try conn.Connect; Writeln('SQL Server Baglantisi Kuruldu'); except Writeln('SQL Server Baglantisi Kurulamadi'); end; end; procedure disconnectDB; begin if conn.Connected then begin conn.Connected := false; Writeln('SQL Server Baglantisi Kapatildi'); end; end; procedure backup; var dset_backup : TUniQuery; backup_query : string; simdi : tdatetime; saniye, dakika, saat, gun, ay, yil : string; formatstring : string; begin simdi := now; saniye := FormatDateTime('ss', simdi); dakika := FormatDateTime('nn', simdi); saat := FormatDateTime('hh', simdi); gun := FormatDateTime('dd', simdi); ay := FormatDateTime('mm', simdi); yil := FormatDateTime('yyyy', simdi); formatstring := gun + '_' + ay + '_' + yil + '_' + saat + '_' + dakika + '_' + saniye; dset_backup := TUniQuery.Create(nil); dset_backup.Connection := conn; dset_backup.SQL.Clear; dset_backup.SQL.Add('use master'); dset_backup.Execute; dset_backup.SQL.Clear; dset_backup.SQL.Add('select * from master..sysdevices where status=16 and name=''MAD_BACKUP_DEVICE'''); dset_backup.Execute; if dset_backup.RecordCount > 0 then begin dset_backup.SQL.Clear; dset_backup.SQL.Add('exec sp_dropdevice ''MAD_BACKUP_DEVICE'''); dset_backup.Execute; end; backup_file := backup_database + '_' + formatstring + '.bak'; compressed_file := backup_database + '_' + formatstring + '.rar'; backup_file_full := backup_path + backup_file; backup_query := 'BACKUP DATABASE ' + backup_database + ' TO MAD_BACKUP_DEVICE;'; dset_backup.SQL.Clear; dset_backup.SQL.Add('exec sp_addumpdevice ''disk'',''MAD_BACKUP_DEVICE'',''' + backup_file_full + ''''); dset_backup.Execute; WriteLn('Yedekleme Basladi...'); dset_backup.SQL.Clear; dset_backup.SQL.Add(backup_query); dset_backup.Execute; WriteLn('Yedekleme Tamamlandi...'); FreeAndNil(dset_backup); end; function ShellExecute_AndWait(FileName: string; Params: string): bool; var exInfo: TShellExecuteInfo; Ph: DWORD; begin FillChar(exInfo, SizeOf(exInfo), 0); with exInfo do begin cbSize := SizeOf(exInfo); fMask := SEE_MASK_NOCLOSEPROCESS or SEE_MASK_FLAG_DDEWAIT; Wnd := GetActiveWindow(); exInfo.lpVerb := 'open'; exInfo.lpParameters := PChar(Params); lpFile := PChar(FileName); nShow := SW_HIDE; end; if ShellExecuteEx(@exInfo) then Ph := exInfo.hProcess else begin Writeln(SysErrorMessage(GetLastError)); Result := true; exit; end; while WaitForSingleObject(exInfo.hProcess, 50) <> WAIT_OBJECT_0 do Application.ProcessMessages; CloseHandle(Ph); Result := true; end; procedure compress; var filename : string; parameters : string; full_params : PansiChar; begin filename := 'rar.exe'; parameters := 'a ' + backup_path + compressed_file + ' ' + backup_file_full; full_params := pansichar(AnsiString(filename + ' ' + parameters)); WriteLn('SIKISTIRMA Basladi...'); // ShellExecute(0, 'open', pchar(filename), pchar(parameters), '',SW_HIDE); // WinExec(full_params, SW_HIDE); // ShellExecute(0, nil, pchar('cmd.exe'), pchar('/C ' + filename + ' ' + parameters), nil, SW_HIDE); if ShellExecute_AndWait(filename, parameters) then begin WriteLn('SIKISTIRMA Tamamlandi...'); compression_result := true; if backup_delete_original_when_compression_success = 1 then begin DeleteFile(PWideChar(backup_file_full)); WriteLn('Orjinal Yedek Dosyasi Silindi...'); end; end else begin WriteLn('SIKISTIRMA Basarisiz...'); compression_result := false; end; end; procedure sendFTP; var madftp : TIdFTP; mes : string; begin if backup_compression = 1 then begin if compression_result then begin // sıkıştırılmış dosyayı gönder upload_file := compressed_file; end else begin if ftp_send_on_compression_error = 1 then begin // Orjinal dosyayı gönder. upload_file := backup_file; end else begin WriteLn('SIKISTIRMA Hatali Oldugundan FTP ye Gonderilmedi...'); exit; end; end; end else begin upload_file := backup_file; end; madftp := TIdFTP.Create(nil); madftp.Host := ftp_host; madftp.Port := ftp_port; madftp.Passive := true; madftp.authcmd := tAuto; madftp.transfertype := ftBinary; madftp.Username := ftp_user; madftp.Password := ftp_password; madftp.Connect; Writeln('FTP Sunucu Baglantisi Kuruldu'); // madftp.ChangeDir('public_html/stokimages'); if length(trim(ftp_path)) > 0 then begin madftp.ChangeDir(ftp_path); end; Writeln('VT Yedegi FTP ye Yukleniyor -> ' + upload_file); madftp.Put(backup_path + upload_file,upload_file,false); Writeln('VT Yedegi FTP ye Yuklendi -> ' + upload_file); madftp.Disconnect; Writeln('FTP Sunucu Baglantisi Kapatildi'); madftp.Free; end; begin try { TODO -oUser -cConsole Main : Insert code here } Writeln('*** Mahmut Dokumaci - MS SQL Server Backup, Compress and FTP Upload Tool v1.0 ***'); if not FileExists(ExtractFilePath(Application.ExeName)+'mad.ini') then begin WriteLn('Yapilandirma Dosyasi Eksik'); exit; end; madINIFile := TIniFile.Create(ExtractFilePath(Application.ExeName)+'mad.ini'); FirstRun := madINIFile.ReadString('MAD','FirstRun','0'); db_host := madINIFile.ReadString('DB','Server',''); db_port := StrToInt(madINIFile.ReadString('DB','Port','0')); database := madINIFile.ReadString('DB','Database',''); db_user := madINIFile.ReadString('DB','User',''); ftp_host := madINIFile.ReadString('FTP','Server',''); ftp_port := StrToInt(madINIFile.ReadString('FTP','Port','0')); ftp_path := madINIFile.ReadString('FTP','Path',''); ftp_user := madINIFile.ReadString('FTP','User',''); ftp_status := StrToInt(madINIFile.ReadString('FTP','Status','0')); ftp_send_on_compression_error := StrToInt(madINIFile.ReadString('FTP','SendOnCompressionError','0')); backup_path := madINIFile.ReadString('Backup','Path',''); backup_database := madINIFile.ReadString('Backup','Database',''); backup_status := StrToInt(madINIFile.ReadString('Backup','Status','0')); backup_compression := StrToInt(madINIFile.ReadString('Compression','Status','0')); backup_delete_original_when_compression_success := StrToInt(madINIFile.ReadString('Compression','DeleteOriginalWhenSuccess','0')); if FirstRun = '1' then begin db_password := madINIFile.ReadString('DB','Password','-'); ftp_password := madINIFile.ReadString('FTP','Password','-'); Writeln('ilk Yapilandirma Ayarlari Algilandi ve Uyarlandi'); madINIFile.WriteString('MAD','FirstRun','0'); madINIFile.WriteString('DB','Password',Encrypt(db_password,'852357jMl???')); madINIFile.WriteString('FTP','Password',Encrypt(ftp_password,'852357jMl???')); exit; end; db_password := Decrypt(madINIFile.ReadString('DB','Password','-'),'852357jMl???'); ftp_password := Decrypt(madINIFile.ReadString('FTP','Password','-'),'852357jMl???'); connectDB; if backup_status = 1 then begin backup; end else begin Writeln('Yedekleme Pasif Oldugu icin islem iptal edildi...'); disconnectDB; exit; end; disconnectDB; if backup_compression = 1 then begin compress; end else begin Writeln('SIKISTIRMA Pasif...'); end; if ftp_status = 1 then begin sendFTP; end else begin Writeln('FTP Gonderimi Pasif...'); end; except on E: Exception do begin disconnectDB; Writeln(E.ClassName, ': ', E.Message); end; end; end.
mad.ini dosyası içeriği;
[DB] Server=x.y.z.t Port=1433 Database=xyz User=abc Password=AAjcM0WrUSlfbBR5EtcPS8sxyBrqiyZRBwDh3XBBOxCSm4X2OSA= [MAD] FirstRun=1 [FTP] Server=ftp.adres.com Port=21 Path=/ User=abc Password=6l7GU7LYt04pVHc/00d7JuGgEUC0pLPPpGaEAQOqivUMl5PVxeCdDiwO Status=0 SendOnCompressionError=0 [Backup] Path=C:\MADBCS\ Database=xyz Status=1 [Compression] Status=1 DeleteOriginalWhenSuccess=0