sql安装弹出sqlcmd_讨论使用SQLCMD和SQL Server代理进行备份和还原自动化

程序员文章站 2024-03-15 17:58:36

...

sql安装弹出sqlcmd

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

通常要求数据库管理员刷新数据库，主要是为了创建生产数据库到测试或开发环境的实时副本。这样做是为了使开发或测试环境与生产环境非常相似。这样，当在生产环境中运行应用程序时，我们可以防止许多不良问题。

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

有时，许多开发人员需要在数据库的生产副本上编写和调试代码，因此定期刷新数据库更有意义。

Let’s build the process to automate the database refresh activity using a simple backup-and-restore method and scheduling it using SQL Server Agent.

让我们构建一个过程，以使用简单的备份和还原方法自动执行数据库刷新活动，并使用SQL Server Agent对其进行调度。

Restore the database to the same server with different name

将数据库还原到具有不同名称的同一服务器
Restore the database to different server using sqlcmd and Robocopy utility

使用sqlcmd和Robocopy实用程序将数据库还原到其他服务器
Schedule the job
安排工作

Let’s take a closer look at the process to see how this works.

让我们仔细看看该过程，以了解其工作原理。

First, create a new database, ProdSQLShackDemo, and a table, SQLShackAuthor. Let’s go ahead and populate the SQLShackauthor table with some records. Now, back this up to a desired location; in this case it’s F:\PowerSQL\. Let’s name this full backup as ProdSQLShackDemo.BAK.

首先，创建一个新数据库ProdSQLShackDemo和一个表SQLShackAuthor。让我们继续，用一些记录填充SQLShackauthor表。现在，将其备份到所需位置；在这种情况下，它是F：\ PowerSQL \。让我们将此完整备份命名为ProdSQLShackDemo.BAK。

-- create a new database
CREATE DATABASE ProdSQLShackDemo;
GO
USE ProdSQLShackDemo;
GO
-- Set the recovery model of SQLShackDemo to FULL
ALTER DATABASE ProdSQLShackDemo SET RECOVERY FULL;
GO
 
USE ProdSQLShackDemo;
GO
-- Create the table SQLShackAuthor
CREATE TABLE SQLShackAuthor (
    ID int IDENTITY(1,1) PRIMARY KEY,
    AuthorName nvarchar(100) NOT NULL
);
GO
--Add records to SQLShackAuthor table
INSERT SQLShackAuthor
    VALUES  ('Brain Lockwood'),
            ('Samir Behara'),
            ('Ahmad Yaseen'),
			('Sifiso W. Ndlovu'),
			('Marko Radakovic'),
			('Bojan Petrovic'),
			('Robert Seles'),
			('Marko Zivkovic'),
			('Luna Cvetkovic')
			;
GO

sql安装弹出sqlcmd_讨论使用SQLCMD和SQL Server代理进行备份和还原自动化

--Select the records of the table SQLShackAuthor
SELECT * FROM SQLShackAuthor;
 
GO

The backup database command is used with the FORMAT clause. The format option overwrites any existing backups and creates a new backup set.

backup database命令与FORMAT子句一起使用。格式选项将覆盖所有现有备份并创建一个新的备份集。

BACKUP DATABASE ProdSQLShackDemo
    TO DISK = 'F:\PowerSQL\ProdSQLShackDemo.BAK'
    WITH FORMAT;
GO

The RESTORE FILELISTONLY SQL requires a path to the backup file. This will return a table with corresponding logical and physical records of each file inside of the backup.

RESTORE FILELISTONLY SQL需要备份文件的路径。这将返回一个表，其中包含备份中每个文件的相应逻辑和物理记录。

-- List of filenames within the backup set
RESTORE FILELISTONLY
   FROM DISK = 'F:\PowerSQL\ProdSQLShackDemo.bak';

In this section, we will see how to restore the database with a different name: TestSQLShackDemo.

在本节中，我们将看到如何使用其他名称还原数据库：TestSQLShackDemo。

Use the F:\PowerSQL\ProdSQLShackDemo.bak as a reference backup file.
使用F：\ PowerSQL \ ProdSQLShackDemo.bak作为参考备份文件。
The MOVE clause is used to move the files to a different file location.
MOVE子句用于将文件移动到其他文件位置。
The keyword RECOVERY is used, since we don’t have any further backups to restore.
使用了关键字RECOVERY，因为我们没有其他要还原的备份。

-- restore the backup to a new database
RESTORE DATABASE TestSQLShackDemo
   FROM DISK = 'F:\PowerSQL\ProdSQLShackDemo.BAK'
   WITH MOVE 'ProdSQLShackDemo' TO 'F:\PowerSQL\TestSQLShackDemo.BAK',
        MOVE 'ProdSQLShackDemo_log' TO 'F:\PowerSQL\TestSQLShackDemo_log.BAK',
        RECOVERY;
GO

We can see that the new TestSQLShackDemo has been created.

我们可以看到已经创建了新的TestSQLShackDemo。

To verify the restoration process, select everything out of these two tables.

要验证还原过程，请从这两个表中选择所有内容。

-- compare the results
SELECT * FROM ProdSQLShackDemo..SQLShackAuthor
SELECT * FROM TestSQLShackDemo..SQLShackAuthor

Restore the database to a different server. This is to simulate the real time scenario: we back up the production database and restore it to the test environment.

将数据库还原到其他服务器。这是为了模拟实时场景：我们备份生产数据库并将其还原到测试环境。

We use SQLCMD for the proof of concept. Let’s now go ahead and automate this process.

我们使用SQLCMD进行概念验证。现在让我们继续进行此过程自动化。

Declare the variable
1. Source production database: DB
2. Source server: SRC
3. Target server: TGT
4. Source database backup path: BACKUP_PATH
5. Target database restore path: RESTORE_PATH
6. Source database data file name: DATAFILENAME
7. Source database log filename: LOGFILENAME
8. Target server data file location: RESTORE_DATA_PATH
9. Target Server log file location: RESTORE_LOG_PATH
声明变量
1. 源生产数据库：DB
2. 源服务器：SRC
3. 目标服务器：TGT
4. 源数据库备份路径：BACKUP_PATH
5. 目标数据库还原路径：RESTORE_PATH
6. 源数据库数据文件名：DATAFILENAME
7. 源数据库日志文件名：LOGFILENAME
8. 目标服务器数据文件位置：RESTORE_DATA_PATH
9. 目标服务器日志文件位置：RESTORE_LOG_PATH
Connect to source and target to check the existence of the database
连接到源和目标以检查数据库是否存在
Backup the database
备份数据库
Use the robocopy utility for the copy operation. This way we quickly transfer data across the network.

使用robocopy实用程序进行复制操作。这样，我们可以在网络上快速传输数据。
Before restoring, check the existence of the target database, and then use the alter database statement to set the target database to single user mode; issue a drop database command to drop the target database.
在还原之前，请检查目标数据库是否存在，然后使用alter database语句将目标数据库设置为单用户模式。发出drop database命令删除目标数据库。
Restore the database
恢复数据库
Validate the output
验证输出

---define variables and its values
 
:setvar DB ProdSQLShackDemo
:setvar SRC HQDBSP18
:setvar TGT HQDBt01
:setvar BACKUP_PATH f:\PowerSQL
:setvar RESTORE_PATH f:\PowerSQL
:setvar DATAFILENAME ProdSQLShackDemo
:setvar LOGFILENAME ProdSQLShackDemo_log
:setvar RESTORE_DATA_PATH "f:\PowerSQL"
:setvar RESTORE_LOG_PATH "f:\PowerSQL"
:setvar COPYPATH f$\PowerSQL
:setvar Timeout 100
 
---Precheck for an existence of DB
 
:CONNECT $(SRC)
SELECT @@Servername
select * from sys.databases  where name='$(DB)'
Go
:CONNECT $(TGT)
SELECT @@Servername
select * from sys.databases where name='$(DB)'
GO
 
:CONNECT $(SRC)
-- Compression Option is set
BACKUP DATABASE $(DB) TO DISK = '$(BACKUP_PATH)\$(DB).bak'
WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = '$(DB) Full DB Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 5,COMPRESSION
GO
----2. Copy the files from SRC to TGT , Refer below link for more --information
 
print '*** Copy DB $(DB) from SRC server $(SRC) to TGT server $(TGT) ***'
!!ROBOCOPY $(BACKUP_PATH)\ \\$(TGT)\$(COPYPATH) $(DB).*
GO
---–3. Restore DB to TGT
print '*** Restore full backup of DB $(DB) ***'
:CONNECT $(TGT)
GO
USE [master]
GO
IF EXISTS (select * from sys.databases where name='$(DB)')
BEGIN
ALTER DATABASE $(DB) SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE $(DB)
END
RESTORE DATABASE $(DB)
   FROM disk = '$(RESTORE_PATH)\$(DB).bak'
  WITH RECOVERY, NOUNLOAD,  STATS = 10,REPLACE,
  MOVE '$(DATAFILENAME)' TO 
'$(RESTORE_DATA_PATH)\$(DATAFILENAME).mdf',
      MOVE '$(LOGFILENAME)'
TO '$(RESTORE_DATA_PATH)\$(LOGFILENAME).ldf'
GO
 
---Post Check for an existence of DB on both SRC and TGT
 
:CONNECT $(SRC)
SELECT @@Servername
select * from sys.databases where name='$(DB)'
GO
SELECT @@Servername
:CONNECT $(TGT)
select * from sys.databases where name='$(DB)'

In the first run, the target database was created for the first time. You can check the create_date column to confirm. In the first result set we can see the absence of the target database. The result is empty.

在第一次运行中，目标数据库是第一次创建的。您可以检查create_date列进行确认。在第一个结果集中，我们可以看到没有目标数据库。结果为空。

In the second result, the database was created.

在第二个结果中，创建了数据库。

In the second run, though, the database exists at the target SQL instance (because of the first run). Therefore, the target database is dropped and recreated during this run. Notice the create_date column; the first highlight is after the first run. In the second highlight, we see that ProdSQLShackDemo’s create_date is different, which shows that the database was dropped and created again.

但是，在第二次运行中，数据库位于目标SQL实例中（因为第一次运行）。因此，在此运行期间将删除并重新创建目标数据库。注意create_date列；第一个亮点是在第一次运行之后。在第二个亮点中，我们看到ProdSQLShackDemo的create_date是不同的，这表明已删除并重新创建了数据库。

Automate the backup and restore process using a SQL Server agent job

使用SQL Server代理作业自动执行备份和还原过程

To automate this process follow the below steps

要自动执行此过程，请执行以下步骤

Save the sqlcmd script to a file BackupandRestoreAutomation.sql
将sqlcmd脚本保存到文件BackupandRestoreAutomation.sql
To test that sqlcmd is working, execute the SQL Script file from the command prompt.

要测试sqlcmd是否正常运行，请从命令提示符处执行SQL脚本文件。

After successful execution, proceed with the following steps

成功执行后，请继续以下步骤

Create a new job on the SQL server agent.
1. Browse object explorer, expand the SQL Server agent folder.
2. Select the jobs folder and right click and create a New Job…
在SQL Server代理上创建一个新作业。
1. 浏览对象资源管理器 ，展开SQL Server代理文件夹。
2. 选择作业文件夹，然后右键单击并创建一个新作业…
In the general properties, enter the name of the job. Let’s call it BackupandRestoreAutomation.

在常规属性中，输入作业的名称。我们称其为BackupandRestoreAutomation 。
Switch the selection on the left from general to steps
1. In the general tab of the Job Steps, enter the step name; in this case, the step name is “Execute the sqlcmd script”
2. Choose the type as Operating system (CmdExec)
3. In the command option, type the command that we want to execute; in this case it’s the sqlcmd command that invokes the script file using the –i option.
```
Sqlcmd –i f:\powersql\BackupandRestoreAutomation.sql
```
将左侧的选择从常规切换到步骤
1. 在作业步骤的常规标签中，输入步骤名称；在这种情况下，步骤名称为“执行sqlcmd脚本”
2. 选择类型作为操作系统（CmdExec）
3. 在命令选项中，键入我们要执行的命令。在这种情况下，是使用-i选项调用脚本文件的是sqlcmd命令。
```
Sqlcmd –i f:\powersql\BackupandRestoreAutomation.sql
```
Click on the OK button.
单击确定按钮。
Next, switch the selection on the left from steps to schedules
接下来，将左侧的选择从步骤切换到时间表
To add a schedule, click the New button
要添加时间表，请单击“ 新建”按钮
The name of the schedule is BackupAndRestoreSchedule. Choose the frequency and best time to run the job as per your requirements.
计划的名称是BackupAndRestoreSchedule。根据您的要求选择运行作业的频率和最佳时间。
Click OK to save the schedule

单击确定以保存计划
The job is now created.

现在创建了作业。

Instead of waiting, let’s manually activate these jobs. Right-click and choose the start job option:

让我们无需等待，而手动**这些作业。右键单击并选择开始作业选项：

This takes care of the automated backup and restore of the databases.

这负责自动备份和还原数据库。

结语 (Wrapping up)

In this article, we carried out a backup-and-restore of a database in two ways:

在本文中，我们以两种方式执行了数据库的备份和还原：

Using SQLCMD with the source and target being on the same instance on the same server.
在源和目标位于同一服务器的同一实例上使用SQLCMD。
Using SQLCMD and SQL Server Agent, wherein the source and the target were on different machines. We used the ROBOCOPY utility to perform the database backup file transfer.
使用SQLCMD和SQL Server代理，其中源和目标位于不同的计算机上。我们使用了ROBOCOPY实用程序来执行数据库备份文件的传输。

That’s all for now. Stay tuned for more on SQL Server backups!

目前为止就这样了。请继续关注有关SQL Server备份的更多信息！