SQL Server中的备份和还原自动化 [英] Backup and restore automation in SQL server

问题描述

大家好，



我有两台运行SQL Server 2014的服务器位于不同的位置。服务器1看不到服务器2，反之亦然。我有一个共享驱动器，两个服务器都可以访问。在服务器1上，我创建了一个备份作业，并在本地保存备份文件。我需要将备份移动到共享驱动器。然后，在服务器2上，获取备份文件并将其还原。我需要自动化这个过程。



非常感谢任何帮助和解决方案。



Sal



我尝试了什么：



我创建了备份作业和批处理文件以将文件移动到共享驱动器。备份已成功运行，但Move数据库无法使用SQL作业。如果我手动运行批处理文件，它会移动备份文件。

解决方案

一个简单的解决方案是使用SQL Agent自动执行备份。使用 BACKUP（Transact-SQL） [ ^ ]命令用于创建备份到所需位置。然后在另一台服务器上有另一个SQL Agent作业，它将数据库置于单用户模式，使用 RESTORE恢复备份（Transact-SQL） [ ^ ]然后打开数据库以供正常使用（如有必要）。



如果你正在创建一个备用并获得适当的许可，那么更简单的选择就是是如何：设置，维护和联机热备份服务器（ Transact-SQL） [ ^ ]

Hi Everyone,

I have 2 servers running SQL Server 2014 which are in different location. Server 1 does not see Server 2 and vice versa. I have a shared drive where both servers can access. On server 1, I have created a backup job and the backup file is saved locally. I need to move the backup to the shared drive. And then, on Server 2, take the backup file and restore it. I need to automate the process.

Any help and solution is greatly appreciated.

Sal

What I have tried:

I have created the backup job and a batch file to move the file to the shared drive. The backup ran successfully but the Move database does not work using SQL job. If I ran the batch file manually, it does move the backup file.

解决方案

A simple solution would be to automate a backup using SQL Agent. Use the BACKUP (Transact-SQL)[^] command to create the backup to desired location. Then have another SQL Agent job on the other server which puts the database in single user mode, restores the backup using RESTORE (Transact-SQL)[^] and then opens the database for normal use (if necessary).

If you're creating a stand-by and have proper licensing then an easier option would be How to: Set Up, Maintain, and Bring Online a Warm Standby Server (Transact-SQL)[^]

这篇关于SQL Server中的备份和还原自动化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！