Auto Backup SQL Database Windows Server and send to Linux Server

Database SQL akan dibackup dan dikirm ke remote server dalam format ZIP file, semua database yang ada dalam SQL Server akan dibackup menjadi file *.BAK dan kemudian akan di archive menjadi file *.zip

Download tools yang dibutuhkan:
- https://the.earth.li/~sgtatham/putty/latest/w32/pscp.exe
- http://www.7-zip.org/a/7z1701-x64.exe

Copy pscp.exe ke direktori C:\DB-BACUP\

Via CMD
copy C:\Users\%USERNAME%\Downloads\pscp.exe C:\DB_BACKUP\

Buat folder DB-BACKUP di Drive C:\

Via CMD
mkdir C:\DB-BACKUP

Buat folder DB-ALL di direktori C:\DB-BACKUP\DB-ALL\. Direktori ini akan menjadi tempat dimana SQL DB akan backup

Via CMD
mkdie C:\DB-BACKUP\DB-ALL\

Buat Folder LOG di direktori  C:\DB-BACKUP. Folder log ini akan menampung setiap kali script.bat dijalankan pada proses scheduler.

Via CMD
mkdir  C:\DB-BACKUP\log

Buat file sqlbackup.bat di dalam folder  C:\DB-BACKUP\ dan copy-paste script berikut

@ECHO OFF
SET hr=%time:~0,2%
IF %hr% lss 10 SET hr=0%hr:~1,1%
Set TODAY=%date:~4,2%-%date:~7,2%-%date:~10,4%-%hr%%time:~3,2%%time:~6,2%%time:~9,2%
ECHO "Backup SQL DB"
sqlcmd -i C:\DB-BACKUP\semua.sql > C:\DB-BACKUP\log\backup-db-%TODAY%.log
ECHO "Hapus data berumur 7 hari"
forfiles -p "C:\DB-BACKUP\DB-ALL" -s -m *.* /D -7 /C "cmd /c del @path"
forfiles -p "C:\DB-BACKUP\log" -s -m *.* /D -7 /C "cmd /c del @path"
ECHO "Zip Archive .BAK :D"
"C:\Program Files\7-Zip\7z.exe" -mx3 a C:\DB-BACKUP\DB-ALL\SQL-DB-%TODAY%.zip -r "C:\DB-BACKUP\DB-ALL\*.BAK" > C:\DB-BACKUP\log\DB-backup-zip-%TODAY%.log
echo "Hapus file .BAK "
del C:\DB-BACKUP\DB-ALL\*.BAK
ECHO "Backup selesai xD"
ECHO "Kirim Backup DB SQL"
C:\DB-BACKUP\pscp.exe -P 22 -pw <password> C:\DB-BACKUP\DB-ALL\*.zip username@ipserver:/home/db/ > C:\DB-BACKUP\log\db-kirim-%TODAY%.log
Echo "Hapus File lama di server Happy Sys. Admin day"
C:\DB-BACKUP\plink.exe ipserver -P 22 -l <username> -pw<password> -m C:\DB-BACKUP\hapus.sh > C:\DB-BACKUP\log\db-kirim-%TODAY%.log
ECHO "Kirim backup DB SQL selesai"
timeout 15

Pada server Linux pastikan anda membuat folder db di direktori /home/. Semua database SQL yang ada di windows server akan dikirim ke server linux .
Yang perlu anda perhitkan adalah text miring dan berwarna orange diatas diganti sesuai dengan credentials server linux anda.

Membuat direktori /home/db/ Via Terminal
mkdir /home/db/

Selanjutnya pada windows server kita akan membuat Task Scheduler menggunakan CMD. Task Scjheduler dijadwalkan running setiap hari pada pukul 03.00AM.

SchTasks /Create /SC DAILY /TN “My Task” /TR “C:\DB-BACKUP\sqlbackup.bat” /ST 03:00

Buat file dengan nama semua.sql di direktori C:\DB-BACKUP\ dan copy-paste script berikut

DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256) 
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'C:\DB-BACKUP\DB-ALL\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR READ_ONLY FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
FETCH NEXT FROM db_cursor INTO @name 
END
CLOSE db_cursor 
DEALLOCATE db_cursor

Next akan saya buatkan script yang nantinya otomatis tanpa perlu membuat directory 1/1.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

*