PHP MSSQL 备份脚本

php-mssql-backup.php

<?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();
	}
}
?>