-
-
Save hoaivan/a6961f5d942064588ef2b1dfcfe9dcd7 to your computer and use it in GitHub Desktop.
PHP MSSQL 备份脚本
This file contains 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 | |
define('MSSQL_HOST', '127.0.0.1'); | |
define('MSSQL_USER', '[USERNAME]'); | |
define('MSSQL_PASS', '[PASSWORD]'); | |
define('BACKUP_TEMP_DIR', 'd:\\mssql_backup\\tmp\\'); | |
define('BACKUP_DIR', 'd:\\mssql_backup\\'); | |
$start_time = time(); | |
// 创建MSSQL连接 | |
$DB = MSSQL::getInstance(); | |
// 获取用户数据库列表,忽略系统数据库 | |
$dblist = array(); | |
$sys_dblist = array('master', 'model', 'msdb', 'tempdb'); | |
$DB->query('SELECT name FROM master..sysdatabases'); | |
while ($result = $DB->fetch_array()) { | |
if (!in_array($result['name'], $sys_dblist)) | |
$dblist[] = $result['name']; | |
} | |
// 创建必须目录 | |
if (!is_dir(BACKUP_TEMP_DIR)) | |
mkdir(BACKUP_TEMP_DIR, 0755, TRUE); | |
if (!is_dir(BACKUP_DIR)) | |
mkdir(BACKUP_DIR, 0755, TRUE); | |
// 备份数据库 | |
$command_templete = "BACKUP DATABASE %s TO DISK = '%s'"; | |
foreach ($dblist as $dbname) { | |
echo "Backup {$dbname}...\r\n"; | |
$save_path = BACKUP_TEMP_DIR . $dbname . '.bak'; | |
$sql = sprintf($command_templete, $dbname, $save_path); | |
$DB->native_query($sql); // 疑似微软PHP-MSSQL驱动的BUG:直接用DRIVER执行备份命令的话,虽然备份成功了,但是会raise一个warning。所以这里直接调用osql去执行备份命令 | |
} | |
// 压缩备份 | |
echo "Compress backup...\r\n"; | |
$zip = new ZipArchive(); | |
$zip_path = BACKUP_DIR . date('Y-m-d') . '.zip'; | |
$zip->open($zip_path, ZipArchive::OVERWRITE); | |
foreach ($dblist as $dbname) { | |
$save_path = BACKUP_TEMP_DIR . $dbname . '.bak'; | |
$zip->addFile($save_path, $dbname . '.bak'); | |
} | |
$zip->close(); | |
// 删除旧的备份文件 | |
echo "Delete old backup...\r\n"; | |
$backup_filelist = glob(BACKUP_DIR . '*.zip'); | |
foreach ($backup_filelist as $each_file) { | |
if ($start_time - fileatime($each_file) > 604800) { | |
echo "Delete {$each_file}\r\n"; | |
unlink($each_file); | |
} | |
} | |
// 删除临时文件 | |
echo "Delete temp file...\r\n"; | |
foreach ($dblist as $dbname) { | |
unlink(BACKUP_TEMP_DIR . $dbname . '.bak'); | |
} | |
rmdir(BACKUP_TEMP_DIR); | |
// 清理MSSQL备份记录 | |
echo "Clean MSSQL backup record...\r\n"; | |
$del_date = date('m/d/Y', time() + 86400); | |
$sql = "sp_delete_backuphistory @oldest_date = '{$del_date}'"; | |
$DB->query('USE msdb'); | |
$DB->query($sql); | |
// 总耗时 | |
$DB->close(); | |
echo "All done, cost " . (time() - $start_time) . " seconds\r\n"; | |
class MSSQL { | |
private $conn; | |
private $result; | |
private static $instance = NULL; | |
const IGNORE_ERROR = TRUE; | |
private function __construct() { | |
if (!function_exists('sqlsrv_connect')) { | |
echo "服务器尚未安装MSSQL Driver"; | |
die(); | |
} | |
$this->conn = sqlsrv_connect(MSSQL_HOST, array('UID' => MSSQL_USER, 'PWD' => MSSQL_PASS)); | |
if ($this->conn === FALSE) | |
$this->error(); | |
} | |
public static function getInstance() { | |
if (self::$instance == NULL) { | |
self::$instance = new MSSQL(); | |
} | |
return self::$instance; | |
} | |
public function query($sql, $ignore_error = FALSE) { | |
$this->result = sqlsrv_query($this->conn, $sql); | |
if ($ignore_error === FALSE && $this->result === FALSE) | |
$this->error(); | |
} | |
public function native_query($sql) { | |
$command_templete = 'sqlcmd -S %s -U %s -P %s -Q "%s"'; | |
$command = sprintf($command_templete, MSSQL_HOST, MSSQL_USER, MSSQL_PASS, $sql); | |
`$command`; | |
} | |
public function fetch_array() { | |
return sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC); | |
} | |
public function close() { | |
sqlsrv_close($this->conn); | |
} | |
private function error() { | |
$messages = sqlsrv_errors(); | |
$templete = "Error %s: %s\r\n"; | |
foreach ($messages as $message) { | |
printf($templete, $message['SQLSTATE'], $message['message']); | |
} | |
die(); | |
} | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment