Last active
September 5, 2024 06:49
-
-
Save radityopw/3b052824494ad04e621c0cfca5f1104d to your computer and use it in GitHub Desktop.
sqlserver backup restore utility
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 1. tools ini melaklukan backup pada database (SQLServer) user (selain sys database) di server windows ke server SFTP berbasis linux | |
| kebutuhan : | |
| 1. PHP : php.net | |
| 2. sqlcmd : https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16 | |
| 3. bcp : https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16&tabs=windows | |
| 4. pscp : bagian dari putty (https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html) | |
| 5. script untuk transfer login : https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances | |
| 6. aws cli : https://min.io/docs/minio/linux/integrations/aws-cli-with-minio.html |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $file = $argv[1]; | |
| $a_data = explode("\n",file_get_contents($file)); | |
| foreach($a_data as $data){ | |
| echo "BACKUP DB ".$data.PHP_EOL; | |
| $file_backup_db_raw = $data."_diff_"; | |
| $file_backup_db = $temp_dir."\\".$file_backup_db_raw.time(); | |
| unlink($file_backup_db.'.bak'); | |
| $sql = "BACKUP DATABASE [".$data."] TO DISK = '".$file_backup_db.".bak' WITH FORMAT, DIFFERENTIAL, INIT, COMPRESSION"; | |
| $cmd = 'sqlcmd -S '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -Q "'.$sql.'"'; | |
| passthru($cmd); | |
| $cmd = 'pscp -P '.escapeshellarg($ssh_port).' -pw '.escapeshellarg($ssh_pass).' '.escapeshellarg($file_backup_db.'.bak').' '.escapeshellarg($ssh_user).'@'.escapeshellarg($ssh_host).':'.escapeshellarg($ssh_dir); | |
| passthru($cmd); | |
| unlink($file_backup_db.'.bak'); | |
| // hapus data yang di server yang lama2 | |
| $cmd = 'plink -P '.$ssh_port.' -ssh -batch -pw '.$ssh_pass.' '.$ssh_user.'@'.$ssh_host.' "ls '.$ssh_dir.'/'.$file_backup_db_raw.'*"'; | |
| //echo $cmd.PHP_EOL; | |
| exec($cmd, $output, $return_var); | |
| if($return_var === 0){ | |
| rsort($output); | |
| $iterator = 0; | |
| foreach($output as $file){ | |
| $iterator++; | |
| if($iterator > $max_file_full){ | |
| echo "menghapus ".$file.PHP_EOL; | |
| $cmd = 'plink -P '.$ssh_port.' -ssh -batch -pw '.$ssh_pass.' '.$ssh_user.'@'.$ssh_host.' "rm '.$file.'"'; | |
| passthru($cmd); | |
| } | |
| } | |
| }else{ | |
| var_dump($output); | |
| die(); | |
| } | |
| unset($return_var); | |
| unset($output); | |
| } | |
| unlink($file); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $file = $argv[1]; | |
| $a_data = explode("\n",file_get_contents($file)); | |
| foreach($a_data as $data){ | |
| echo "BACKUP DB ".$data.PHP_EOL; | |
| $file_backup_db_raw = $data."_full_"; | |
| $file_backup_db = $temp_dir."\\".$file_backup_db_raw.time(); | |
| unlink($file_backup_db.'.bak'); | |
| $sql = "BACKUP DATABASE [".$data."] TO DISK = '".$file_backup_db.".bak' WITH FORMAT, INIT, COMPRESSION"; | |
| $cmd = 'sqlcmd -S '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -Q "'.$sql.'"'; | |
| passthru($cmd); | |
| $cmd = 'pscp -P '.escapeshellarg($ssh_port).' -pw '.escapeshellarg($ssh_pass).' '.escapeshellarg($file_backup_db.'.bak').' '.escapeshellarg($ssh_user).'@'.escapeshellarg($ssh_host).':'.escapeshellarg($ssh_dir); | |
| passthru($cmd); | |
| // kirim ke aws jika ada | |
| if($aws_endpoint) | |
| { | |
| $cmd = "aws --endpoint-url $aws_endpoint s3 cp ".$file_backup_db.".bak ".$aws_bucket.""; | |
| if($aws_endpoint == "s3") | |
| { | |
| $cmd = "aws s3 cp ".$file_backup_db.".bak ".$aws_bucket.""; | |
| } | |
| passthru($cmd); | |
| } | |
| unlink($file_backup_db.'.bak'); | |
| // hapus data yang di server yang lama2 | |
| $cmd = 'plink -P '.$ssh_port.' -ssh -batch -pw '.$ssh_pass.' '.$ssh_user.'@'.$ssh_host.' "ls '.$ssh_dir.'/'.$file_backup_db_raw.'*"'; | |
| //echo $cmd.PHP_EOL; | |
| exec($cmd, $output, $return_var); | |
| if($return_var === 0){ | |
| rsort($output); | |
| $iterator = 0; | |
| foreach($output as $file){ | |
| $iterator++; | |
| if($iterator > $max_file_full){ | |
| echo "menghapus ".$file.PHP_EOL; | |
| $cmd = 'plink -P '.$ssh_port.' -ssh -batch -pw '.$ssh_pass.' '.$ssh_user.'@'.$ssh_host.' "rm '.$file.'"'; | |
| passthru($cmd); | |
| } | |
| } | |
| }else{ | |
| var_dump($output); | |
| die(); | |
| } | |
| unset($return_var); | |
| unset($output); | |
| } | |
| unlink($file); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $file = $argv[1]; | |
| $a_data = explode("\n",file_get_contents($file)); | |
| foreach($a_data as $data){ | |
| echo "BACKUP DB ".$data.PHP_EOL; | |
| $file_backup_db_raw = $data."_log_"; | |
| $file_backup_db = $temp_dir."\\".$file_backup_db_raw.time(); | |
| unlink($file_backup_db.'.bak'); | |
| $sql = "BACKUP LOG [".$data."] TO DISK = '".$file_backup_db.".bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION"; | |
| $cmd = 'sqlcmd -S '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -Q "'.$sql.'"'; | |
| passthru($cmd); | |
| $cmd = 'pscp -P '.escapeshellarg($ssh_port).' -pw '.escapeshellarg($ssh_pass).' '.escapeshellarg($file_backup_db.'.bak').' '.escapeshellarg($ssh_user).'@'.escapeshellarg($ssh_host).':'.escapeshellarg($ssh_dir); | |
| passthru($cmd); | |
| unlink($file_backup_db.'.bak'); | |
| // hapus data yang di server yang lama2 | |
| $cmd = 'plink -P '.$ssh_port.' -ssh -batch -pw '.$ssh_pass.' '.$ssh_user.'@'.$ssh_host.' "ls '.$ssh_dir.'/'.$file_backup_db_raw.'*"'; | |
| //echo $cmd.PHP_EOL; | |
| exec($cmd, $output, $return_var); | |
| if($return_var === 0){ | |
| rsort($output); | |
| $iterator = 0; | |
| foreach($output as $file){ | |
| $iterator++; | |
| if($iterator > $max_file_full){ | |
| echo "menghapus ".$file.PHP_EOL; | |
| $cmd = 'plink -P '.$ssh_port.' -ssh -batch -pw '.$ssh_pass.' '.$ssh_user.'@'.$ssh_host.' "rm '.$file.'"'; | |
| passthru($cmd); | |
| } | |
| } | |
| }else{ | |
| var_dump($output); | |
| die(); | |
| } | |
| unset($return_var); | |
| unset($output); | |
| } | |
| unlink($file); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $sql = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND database_id > 4;"; | |
| $file_list_db = $temp_dir."\\list-db-diff.csv"; | |
| unlink($file_list_db); | |
| $cmd = 'bcp "'.$sql.'" queryout '.$file_list_db.' -S '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -c -t, -r\n -k'; | |
| //echo $cmd.PHP_EOL; | |
| passthru($cmd); | |
| $handle = fopen($file_list_db, 'r'); | |
| if ($handle === false) { | |
| die("Gagal membuka file list-db-diff."); | |
| } | |
| //buat file per job | |
| $list_file = array(); | |
| $list_database = array(); | |
| for($i=1;$i<=$max_job_full;$i++){ | |
| $list_file[$i-1] = $temp_dir."\\job-diff-".$i; | |
| } | |
| while (($data = fgetcsv($handle)) !== false) { | |
| $data = trim($data[0]); | |
| $list_database[] = $data; | |
| } | |
| fclose($handle); | |
| unlink($file_list_db); | |
| // membagi file | |
| shuffle($list_database); | |
| $length = count($list_database); | |
| $part_size = ceil($length / $max_job_full); | |
| $list_database = array_chunk($list_database, $part_size); | |
| for($i=1;$i<=$max_job_full;$i++){ | |
| unlink($list_file[$i-1]); | |
| if(isset($list_database[$i-1]) && count($list_database[$i-1]) > 0) { | |
| $isi_file = implode("\n",$list_database[$i-1]); | |
| file_put_contents($list_file[$i-1],$isi_file); | |
| $cmd = 'start "" cmd /c "php -f _diff.php '.$list_file[$i-1].'"'; | |
| pclose(popen($cmd,"r")); | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $sql = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND database_id > 4;"; | |
| $file_list_db = $temp_dir."\\list-db.csv"; | |
| unlink($file_list_db); | |
| $cmd = 'bcp "'.$sql.'" queryout '.$file_list_db.' -S '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -c -t, -r\n -k'; | |
| //echo $cmd.PHP_EOL; | |
| passthru($cmd); | |
| $handle = fopen($file_list_db, 'r'); | |
| if ($handle === false) { | |
| die("Gagal membuka file list-db."); | |
| } | |
| //buat file per job | |
| $list_file = array(); | |
| $list_database = array(); | |
| for($i=1;$i<=$max_job_full;$i++){ | |
| $list_file[$i-1] = $temp_dir."\\job-full-".$i; | |
| } | |
| while (($data = fgetcsv($handle)) !== false) { | |
| $data = trim($data[0]); | |
| $list_database[] = $data; | |
| } | |
| fclose($handle); | |
| unlink($file_list_db); | |
| // membagi file | |
| shuffle($list_database); | |
| $length = count($list_database); | |
| $part_size = ceil($length / $max_job_full); | |
| $list_database = array_chunk($list_database, $part_size); | |
| for($i=1;$i<=$max_job_full;$i++){ | |
| unlink($list_file[$i-1]); | |
| if(isset($list_database[$i-1]) && count($list_database[$i-1]) > 0) { | |
| $isi_file = implode("\n",$list_database[$i-1]); | |
| file_put_contents($list_file[$i-1],$isi_file); | |
| $cmd = 'start "" cmd /c "php -f _full.php '.$list_file[$i-1].'"'; | |
| pclose(popen($cmd,"r")); | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $sql = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND database_id > 4 AND recovery_model_desc = 'FULL';"; | |
| $file_list_db = $temp_dir."\\list-db-log.csv"; | |
| unlink($file_list_db); | |
| $cmd = 'bcp "'.$sql.'" queryout '.$file_list_db.' -S '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -c -t, -r\n -k'; | |
| //echo $cmd.PHP_EOL; | |
| passthru($cmd); | |
| $handle = fopen($file_list_db, 'r'); | |
| if ($handle === false) { | |
| die("Gagal membuka file list-db-log."); | |
| } | |
| //buat file per job | |
| $list_file = array(); | |
| $list_database = array(); | |
| for($i=1;$i<=$max_job_full;$i++){ | |
| $list_file[$i-1] = $temp_dir."\\job-diff-".$i; | |
| } | |
| while (($data = fgetcsv($handle)) !== false) { | |
| $data = trim($data[0]); | |
| $list_database[] = $data; | |
| } | |
| fclose($handle); | |
| unlink($file_list_db); | |
| // membagi file | |
| shuffle($list_database); | |
| $length = count($list_database); | |
| $part_size = ceil($length / $max_job_full); | |
| $list_database = array_chunk($list_database, $part_size); | |
| for($i=1;$i<=$max_job_full;$i++){ | |
| unlink($list_file[$i-1]); | |
| if(isset($list_database[$i-1]) && count($list_database[$i-1]) > 0) { | |
| $isi_file = implode("\n",$list_database[$i-1]); | |
| file_put_contents($list_file[$i-1],$isi_file); | |
| $cmd = 'start "" cmd /c "php -f _log.php '.$list_file[$i-1].'"'; | |
| pclose(popen($cmd,"r")); | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| require_once 'config.php'; | |
| $file = $temp_dir."\\users.sql"; | |
| unlink($file); | |
| $cmd = 'sqlcmd -C -H '.$db_host.','.$db_port.' -U '.$db_user.' -P '.$db_pass.' -Q "EXEC sp_help_revlogin" -o '.$file; | |
| passthru($cmd); | |
| $cmd = 'pscp -P '.escapeshellarg($ssh_port).' -pw '.escapeshellarg($ssh_pass).' '.escapeshellarg($file).' '.escapeshellarg($ssh_user).'@'.escapeshellarg($ssh_host).':'.escapeshellarg($ssh_dir); | |
| passthru($cmd); | |
| unlink($file); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| $temp_dir = "d:\\temp"; | |
| $db_host = "127.0.0.1"; | |
| $db_port = "1433"; | |
| $db_user = "sa"; | |
| $db_pass = "password"; | |
| $max_file_full = 3; | |
| $max_file_diff = 3; | |
| $max_file_log = 3; | |
| $max_job_full = 2; | |
| $max_job_diff = 2; | |
| $max_job_log = 2; | |
| $ssh_host = "127.0.0.1"; | |
| $ssh_port = 22; | |
| $ssh_user = ""; | |
| $ssh_pass = ""; | |
| $ssh_dir = ""; | |
| // pastikan install aws cli https://min.io/docs/minio/linux/integrations/aws-cli-with-minio.html | |
| // jika menggunakan aws s3, maka diisi s3, tapi jika bukan, maka isi endpoint yang tepat http://,..... | |
| $aws_endpoint = ""; | |
| $aws_bucket = ""; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment