ssis 创建ssisdb_使用SSIS创建备份

ssis 创建ssisdb

介绍 (Introduction)

This Article is for people with experience using T-SQL and SQL Server Management Studio, but without experience in SQL Server Integration Services (SSIS).

本文适用于有使用T-SQL和SQL Server Management Studio的经验，但没有SQL Server Integration Services（SSIS）经验的人。

In this article, we will introduce you to the SSIS world and then we will show how to combine different SSIS task to create combined backup tasks.

在本文中，我们将向您介绍SSIS世界，然后将展示如何组合不同的SSIS任务以创建组合的备份任务。

SSIS is a visual tool to Integrate SQL Server with other programs and databases like Oracle, MySQL, Teradata, Excel, MS Access, Visual Basic, C#, Web Services, FTP Sites, etc.

SSIS是将SQL Server与其他程序和数据库（例如Oracle，MySQL，Teradata，Excel，MS Access，Visual Basic，C＃，Web服务，FTP站点等）集成的可视化工具。

You can combine different tasks to work on complex tasks.

您可以组合不同的任务来处理复杂的任务。

Figure 1a

图1a

SSIS is a very powerful tool because you can:

SSIS是一个非常强大的工具，因为您可以：

• Work with parameters and variables.
  使用参数和变量。
• Work with loops.
  使用循环。
• Execute tasks in parallel incrementing the speed to complete the tasks.
  并行执行任务以增加速度来完成任务。

SSIS is commonly used to Migrate Data, but it rarely used to backup databases. This is a tutorial to start using SSIS for these administrative tasks.

SSIS通常用于迁移数据，但很少用于备份数据库。 本教程是开始将SSIS用于这些管理任务的教程。

入门 (Getting started)

1. In order to start, open the SQL Server Data Tools (SSDT)

   为了开始，请打开SQL Server数据工具（SSDT）

   
   
   Figure 1

   
   图1

2. Go to File>New Project

   转到文件>新项目

   
   
   Figure 2

   
   图2

3. Select the Integration Services Project

   选择集成服务项目

   
   
   Figure 3

   
   图3

4. Double click on the Check Database Integrity task. This task verifies the integrity of the logical and physical Database objects. You can run this task before the backup.

   双击“检查数据库完整性”任务。 此任务验证逻辑和物理数据库对象的完整性。 您可以在备份之前运行此任务。

   
   
   Figure 4

   
   图4

5. Double click on the task and press the New button to create a New connection

   双击任务，然后按“新建”按钮以创建新连接

   
   
   Figure 5

   
   图5

6. Specify any name for the connection, the name of the SQL Server and select an authentication method (Windows Authentication is strongly recommended for security reasons) and press OK.

   指定连接的任何名称，SQL Server的名称，然后选择一种身份验证方法（出于安全原因，强烈建议使用Windows身份验证），然后按OK。

   
   
   Figure 6

   
   图6

7. Click in the Databases Combobox and select the Database(s) to backup. As you can see, you can check the integrity of several databases with one single task and backup system databases.

   单击数据库组合框，然后选择要备份的数据库。 如您所见，您可以使用一个任务和备份系统数据库来检查多个数据库的完整性。

   
   
   Figure 7

   
   图7

8. If you press the View T-SQL, you will have the chance to see the T-SQL command equivalent to the Task action

   如果按View T-SQL，您将有机会看到与Task操作等效的T-SQL命令。

   
   
   Figure 8

   
   图8

9. The tasks executes the DBCC CHECKDB statement

   任务执行DBCC CHECKDB语句

   
   
   Figure 9

   
   图9

10. Double click the Execute T-SQL Statement Task and add the tasks with a constraint

    双击“执行T-SQL语句任务”，然后添加具有约束的任务

    
    
    Figure 10

    
    图10

11.  
    USE [master]
    GO
    ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL WITH NO_WAIT
    GO
     
    

    The main purpose of this is to show that we can execute T-SQL tasks with SSIS and combine with other tasks

    其主要目的是表明我们可以使用SSIS执行T-SQL任务并与其他任务结合

    
    
    Figure 11

    
    图11

12. Double click the Backup Database Task and join it with the other tasks using a constraint

    双击“备份数据库任务”，并使用约束将其与其他任务一起加入

    
    
    Figure 12

    
    图12

13. Double click the Back Up Database Task use the current connection, select the Full Backup type and a Database to backup

    双击“备份数据库任务”，使用当前连接，选择“完全备份”类型和要备份的数据库

    
    
    Figure 13

    
    图13

14. You can backup individual files and files groups, you can also use the copy only option which allows you to backup the database without changing the Database and without affecting regular backup procedures. We can also specify expiration dates for the backups and Append or Overwrite the backup if it already exists.

    您可以备份单个文件和文件组，还可以使用仅复制选项，该选项使您可以在不更改数据库且不影响常规备份过程的情况下备份数据库。 我们还可以指定备份的到期日期，如果备份已经存在，则追加或覆盖备份。

    
    
    Figure 14

    
    图14

15. You can also verify the backup integrity and compress the backup

    您还可以验证备份的完整性并压缩备份

    
    
    Figure 15

    
    图15

16. Finally, double click on the Maintenance Cleanup Task and you can clean all databases

    最后，双击维护清理任务，就可以清理所有数据库

    
    
    Figure 16

    
    图16

17. With the Maintenance Cleanup Task, we can delete older backups. It is a good practice if there are obsolete backup that need to be removed. You can remove Backup files and Maintenance Plan text reports. You can also specify the file name or search folders with a specific extension in a specific folder. You can also search backup files in subfolders and specify which folders to delete according to their age.

    通过维护清理任务，我们可以删除较早的备份。 如果需要删除过时的备份，这是一个好习惯。 您可以删除备份文件和维护计划文本报告。 您也可以指定文件名或在特定文件夹中搜索具有特定扩展名的文件夹。 您还可以在子文件夹中搜索备份文件，并根据其使用期限指定要删除的文件夹。

    
    
    Figure 17

    
    图17

18. You can optionally press the view T-SQL button and see the command line used

    您可以选择按视图T-SQL按钮并查看所使用的命令行

    
    
    Figure 18

    
    图18

19. Once done, Start the package and it will run all the tasks created. If everything is OK all the tasks will be on green status.

    完成后，启动程序包，它将运行所有创建的任务。 如果一切正常，所有任务将处于绿色状态。

    
    
    Figure 19
    
    
    图19

20. You can verify if the backup was created to verify that everything worked fine

    您可以验证是否创建了备份以验证一切正常

    
    
    Figure 20

    
    图20

其他任务 (Other Tasks)

您可以使用许多任务来改善SSIS项目。 例如，您可以使用 Web Service Task to call Web Services in SSIS projects. Another powerful task is the Web服务任务在SSIS项目中调用Web服务。 另一个强大的任务是 Script Task. You can program in Visual Basic or C# complex or customized tasks not allowed by other Tasks. The 脚本任务 。 您可以使用Visual Basic或C＃编写的复杂任务或其他任务不允许的自定义任务进行编程。 Data Flow is the most popular and frequently used task to import or Export data to different data sources like Oracle, MySQL, DB2, MS Excel, Posgresql and most of the databases with the help of the Database Drivers. There are also tasks to 数据流是在数据库驱动程序的帮助下将数据导入或导出到不同的数据源（例如Oracle，MySQL，DB2，MS Excel，Posgresql和大多数数据库）中最流行和最常用的任务。 还包括 send emails, 发送电子邮件 ， upload files to an FTP Server, 将文件上传到FTP服务器 ， automate tasks in SQL Server Analysis Services, 在SQL Server Analysis Services中自动执行任务 ， copy files, 复制文件 ， run the command shell (CMD), Work with 运行命令外壳程序（CMD） ，使用 XML files, etc. XML文件等任务。

免费额外的任务 (Free extra tasks)

There are also additional free SSIS components that you can download from the codeplex site.

您还可以从codeplex站点下载其他免费的SSIS组件。

There are task to upload files to the Amazon S3, Connect to Microsoft CRM Dynamics, FTPS, SCP, SSH, SharePoint and more.

有将文件上传到Amazon S3，连接到Microsoft CRM Dynamics，FTPS，SCP，SSH，SharePoint等的任务。

如何安排SSIS包 (How to schedule SSIS packages)

You can schedule your SSIS packages using the SQL Server Agent. That way you can run the packages regularly according to your needs.

您可以使用SQL Server代理计划您的SSIS包。 这样，您可以根据需要定期运行软件包。

For more information about scheduling SSIS packages, review this link:

有关安排SSIS包的更多信息，请查看以下链接：

SQL Server Agent Jobs for Packages

程序包SQL Server代理作业

适用于开发人员的SSIS (SSIS for developers)

If you do not like visual tasks and you love .NET, you can also program SSIS using C#, visual basic or other languages supported by the .NET Framework.

如果您不喜欢视觉任务并且喜欢.NET，则还可以使用C＃，Visual Basic或.NET Framework支持的其他语言对SSIS进行编程。

For more information about the Integration Services Development, please refer to this link:

有关Integration Services开发的更多信息，请参考以下链接：

Integration Services Developer Documentation

Integration Services开发人员文档

创建自己的自定义SSIS任务 (Create your own custom SSIS task)

Sometimes you need to create your custom task and add it to the SSDT to use it frequently. It is possible to create your own task and add it to the ones included by default.

有时，您需要创建自定义任务并将其添加到SSDT中以经常使用它。 可以创建自己的任务，并将其添加到默认包含的任务中。

For more information about creating your own custom SSIS task, please refer to this link:

有关创建自己的自定义SSIS任务的更多信息，请参考以下链接：

Developing a Custom Task

开发自定义任务

结论 (Conclusions)

As you can see, SSIS is a very complete and powerful tool to help you to Administer and Maintain your Database.

如您所见，SSIS是一个非常完整且功能强大的工具，可以帮助您管理和维护数据库。

翻译自: https://www.sqlshack.com/creating-backups-ssis/

ssis 创建ssisdb