Skip to content

Backups

Przykład skryptu. Skrypt sklada sie z 2 części plik bat i sql

Wymagania:

  • directory c:\backups
  • envirnment variables %BACKUP_USER% %BACKUP_PASS%

Plik bat

Batchfile
 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
:: SKRYPT BACKUP
:: LAST Version 2024-12-23 

:: 01 ZMIENNE KTORE MOZNA WYKORZYSTAC POZNIEJ DO DAT  %yy%%mm%%dd%
   FOR /f "tokens=1-4 delims=/-. " %%G IN ('date /t') DO (call :s_fixdate %%G %%H %%I %%J)
   goto :s_print_the_date

   :s_fixdate
   if "%1:~0,1%" GTR "9" shift
   FOR /f "skip=1 tokens=2-4 delims=(-)" %%G IN ('echo.^|date') DO (
       set %%G=%1&set %%H=%2&set %%I=%3)
   goto :eof
   :s_print_the_date

::md %yy%%mm%%dd%

:: 02 DB  BACKUP  DODAC C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\
"SQLCMD.EXE" -S localhost -p -i  c:\backups\backup.sql >> c:\BACKUPS\log.txt

:: 03 DB ZIP  -sdel usuwa po sukcesie, opcjonalnie del *.7z /q
c:
cd c:\backups
FOR %%i IN (*.bak) DO "C:\Program Files\7-Zip\7z.exe" a "%%~ni.7z" "%%i" -sdel

:: 06  MAPOWANIE
:: Uwaga w przypadku hetznera subaccount maja taki tempalte sciezek , dla uzytkownika glonwego jest to \backup
NET USE \\%BACKUP_USER%.your-storagebox.de\%BACKUP_USER% /d
NET USE \\%BACKUP_USER%.your-storagebox.de\%BACKUP_USER% /u:%BACKUP_USER% %BACKUP_PASS%

:: 07 MOVE SQL BACKUPS /x exclude hidden , 
Robocopy c:\backups \\%BACKUP_USER%.your-storagebox.de\%BACKUP_USER%\DB-BACKUP  *.7z  /MOV  /W:5 /Z /XA:H

:: 0 Mirror DATASTORE 
Robocopy C:\DATA\DATASTORE \\%BACKUP_USER%.your-storagebox.de\%BACKUP_USER%\DATASTORE-MIRROR /MIR /W:5 /Z /XA:H

:: 09 MIRROR IIS
Robocopy C:\inetpub\wwwroot \\%BACKUP_USER%.your-storagebox.de\%BACKUP_USER%\IIS-MIRROR /MIR /W:5 /Z 

:: OPCJA DB SYNC , eliminacja tabel konfiguracyjnych i logów kv store jest wylaczony specjalnie
:: db.sync.exe --silent --source Data Source=10.10.10.10;Initial Catalog=platforma-api;User ID=username;password=password --target  Data Source=localhost;Initial Catalog=platforma-api;User ID=paandauser;Password=password --exclude [app].[log],[Hangfire].*,[app].[Audit],[app].[log_ddl],[dbo].[request],[app].[hosting],[app].[app],[app].[kvstore]

:: OPCJA SEND TO AWS  / wyamga dodanie credentiali
:: aws s3 mv c:\backups\ s3://backup-7272351852/surchem/sql/ --recursive --exclude "*" --include "*.7z"
:: aws s3 sync c:\data\client.platformacrm.pl\ s3://backup-7272351852/client/data/

:: OPCJA JASPER
:: c:
:: cd "C:\Program Files\jasperreports-server-cp-4.7.0\buildomatic"
:: js-export --everything --output-zip d:\data\backup\%yy%%mm%%dd%\%yy%%mm%%dd%!jasper.zip
:: pause

Plik SQL

SQL
 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
--sqlcmd.exe -S localhost -p -i  c:\backups\backup.sql >> c:\backups\log.txt

DECLARE @name VARCHAR(50)       -- database name 
DECLARE @fileName VARCHAR(256)  -- filename for backup 
DECLARE @path VARCHAR(256) = 'c:\BACKUPS\'  -- path for backup files 
DECLARE @fileDate VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),112)  -- used for file name



DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name not in ('master' , 'tempdb', 'model','msdb')  --exclude

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    SET @fileName = @path + @fileDate +'_'+ @name +  '.BAK' 
    BACKUP DATABASE @name TO DISK = @fileName -- WITH COMPRESSION is not supported on express

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor