精华内容
下载资源
问答
  • dbatools is PowerShell module that you may think of like a command-line SQL Server Management Studio. The project initially started out as just Start-SqlMigration.ps1, but has now grown into a ...
  • plsql插件DBATools

    2017-10-25 08:56:33
    DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件,主要功能是提供许多DBA数据库管理的功能,查看与调整表空间资源插件。
  • DBATools for plsql

    2018-05-02 09:46:49
    DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件,主要功能是提供许多DBA数据库管理的功能,使ORACLE工程师不仅可以使用PL/SQL Developer进行开发设计,同时也可以进行数据库管理及监控,大大提高...
  • docs docs.dbatools.io的文件
  • DBATools 非常好用PLSQL导出帮手,你值得拥有,你可以用他导出表结构,可以方便修改ORA
  • web:dbatools.io网站-源码

    2021-05-09 07:45:41
    网路 dbatools.io网站
  • 软件名称:DBATools For PL/SQL Developer 发布版本:1.1.0 Beta 1 发布日期:2010-01-28 软件简介: PL/SQL Developer是Oracle数据库当前最流行的开发工具之一,它在ORACLE数据库开发设计方面功能强大,使用方便,...
  • DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件,主要功能是提供许多DBA数据库管理的功能,使ORACLE工程师不仅可以使用PL/SQL Developer进行开发设计,同时也可以进行数据库管理及监控,大大提高...
  • PLSQL插件DBATools,支持了win10_64位,解决win10 64位系统无法注册midas.ll问题和相关说明文档
  • DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件,主要功能是提供许多DBA数据库管理的功能,使ORACLE工程师不仅可以使用PL/SQL Developer进行开发设计,同时也可以进行数据库管理及监控,大大提高...
  • DBATools is an open source PowerShell module with useful commands to do the administrative task in SQL Server. In my earlier articles on DBATools (see TOC at the bottom) we explored installation a...

    DBATools is an open source PowerShell module with useful commands to do the administrative task in SQL Server. In my earlier articles on DBATools (see TOC at the bottom) we explored installation and performing database backups, restoration, and validation with DBATools.

    DBATools是一个开源PowerShell模块,其中包含用于执行SQL Server中管理任务的有用命令。 在我先前关于DBATools的文章(请参见底部的TOC )中,我们探讨了安装以及使用DBATools执行数据库备份,还原和验证。

    In this article, let’s explore creating a SQL Server database using DBATools.

    在本文中,让我们探索使用DBATools创建SQL Server数据库。

    DBATools中的New-DbaDatabase命令 (New-DbaDatabase command in DBATools)

    Usually, we create a SQL Server database using a graphical user interface in SSMS or Transact-SQL. We can create a database using open source PowerShell module DBATools command New-DbaDatabase.

    通常,我们使用SSMS或Transact-SQL中的图形用户界面创建SQL Server数据库。 我们可以使用开源PowerShell模块DBATools命令New-DbaDatabase创建数据库。

    We can search for a list of available functions in DBATools with the following command.

    我们可以使用以下命令在DBATools中搜索可用功能的列表。

    > Get-Help *Database*
    

    Creating SQL Server Database using DBATools

    We can create a SQL Server database using New-DbaDatabase command in DBATools.

    我们可以使用DBATools中的New-DbaDatabase命令创建一个SQL Server数据库。

    New-DbaDatabase命令的语法 (The syntax for New-DbaDatabase command)

    We can get useful stuff for a command using Get-help *function* in DBATools. In the following image, you can see description and syntax for New-DbaDatabase command.

    使用DBATools中的Get-help * function *,我们可以获得命令的有用信息。 在下图中,您可以看到New-DbaDatabase命令的描述和语法。

    >Get-help New-DbaDatabase
    

    DBATools command help to get syntax

    Let’s have a quick recap of SQL database architecture. We have following SQL database files in SQL Server.

    让我们快速回顾一下SQL数据库体系结构。 我们在SQL Server中具有以下SQL数据库文件。

    • Primary data file (*.mdf)

      主数据文件(* .mdf)
    • Secondary data file (*.ndf)

      辅助数据文件(* .ndf)
    • Log file (*.ldf)

      日志文件(* .ldf)

    Each SQL database is having a Primary file group that contains the primary data file. We can create secondary file groups and have multiple secondary data files in it.

    每个SQL数据库都有一个包含主要数据文件的主要文件组。 我们可以创建辅助文件组,并在其中包含多个辅助数据文件。

    database architecture

    By default, SQL Server creates a copy of a model database if we do not specify any parameter for creating a database. Let’s create a SQL database using DBAtools.

    默认情况下,如果我们未指定任何用于创建数据库的参数,则SQL Server将创建模型数据库的副本。 让我们使用DBAtools创建一个SQL数据库。

    示例1:创建SQL Server数据库而不指定数据库名称 (Example 1: Create a SQL Server Database without specifying the database name)

    In the following DBATools command New-DbaDatabase, we specified the SQL instance name in which we want to create a new SQL database.

    在以下DBATools命令New-DbaDatabase中,我们指定了要在其中创建新SQL数据库SQL实例名称。

    > New-DbaDatabase -SqlInstance kashish\SQL2019CTP
    

    If we do not specify any database name, DBATools creates a new database with a random name. As we just created this database and did not have any backup, it shows last full, differential and log backup as default value.

    如果我们未指定任何数据库名称,则DBATools将创建一个具有随机名称的新数据库。 当我们刚创建此数据库并且没有任何备份时,它会将上次的完整备份,差异备份和日志备份显示为默认值。

    SQL Server Database using DBATools command

    As stated earlier, if we do not specify any parameter to create a SQL database, it creates a copy of a model database. Let’s compare the properties of the SQL database and the newly created database (random-808569434).

    如前所述,如果我们不指定任何参数来创建SQL数据库,它将创建模型数据库的副本。 让我们比较一下SQL数据库和新创建的数据库(random-808569434)的属性。

    Database comparison

    1. Both Model and random-808569434 database have similar size, space available and the number of users

      Model数据库和random-808569434数据库的大小,可用空间和用户数量都相似
    2. Collation of both Model and random-808569434 database is Latin1_General_CI_AS

      Model和random-808569434数据库的排序规则均为Latin1_General_CI_AS
    3. Database owner of Model database and random-808569434 is different. We executed DBATools command under default authentication (Windows authentication), and windows user (In this case, Kashish\Test) becomes a database owner

      Model数据库的数据库所有者与random-808569434不同。 我们在默认身份验证(Windows身份验证)下执行DBATools命令,并且Windows用户(在这种情况下为Kashish \ Test)成为数据库所有者

    SQL Server requires an exclusive lock on the model database to create a new SQL database. I executed this command while a model database properties window is open. SQL Server could not get exclusive lock and Crate database statement failed with the following error

    SQL Server需要在模型数据库上具有排他锁才能创建新SQL数据库。 打开模型数据库属性窗口时,我执行了此命令。 SQL Server无法获得排他锁,并且Crate数据库语句失败,并出现以下错误

    Error while creating SQL Server Database

    示例2:使用指定的数据库名称创建SQL Server数据库 (Example 2: Create SQL Server Database with specifying a database name)

    In the previous example, we did not specify any database name in DBATools command. Let’s specify a database name and execute the command.

    在前面的示例中,我们没有在DBATools命令中指定任何数据库名称。 让我们指定一个数据库名称并执行命令。

    > New-DbaDatabase -Name SQLTemp1 -SqlInstance .\SQL2019CTP
    

    SQL Server creates a new SQL database with the specified name.

    SQL Server使用指定的名称创建一个新SQL数据库。

    Specify Database name using DBATools

    We can create multiple databases as well by specifying database names separated by a comma.

    我们还可以通过指定用逗号分隔的数据库名称来创建多个数据库。

    > New-DbaDatabase -Name SQLTemp1, SQLTemp2  -SqlInstance kashish\SQL2019CTP
    

    Database SQLTemp1 already exists in SQL instance kashish\SQL2019CTP therefore we get a warning message for SQLTemp1 database. It creates SQLTemp2 database successfully.

    数据库SQLTemp1已存在于SQL实例kashish \ SQL2019CTP中,因此我们收到有关SQLTemp1数据库的警告消息。 它成功创建SQLTemp2数据库。

    Specify database name

    Let’s drop databases SQLTemp1 and SQLTemp2 and rerun the DBATools command.

    让我们删除数据库SQLTemp1SQLTemp2,然后重新运行DBATools命令。

    SQL Create Database

    We can get a result of a command in a grid view to have a better visual experience. We need to specify by | Out-GridView parameter with DBATools command.

    我们可以在网格视图中获得命令的结果,以获得更好的视觉体验。 我们需要指定| 使用DBATools命令的Out-GridView参数。

    > New-DbaDatabase -Name SQLTemp1,SQLTemp2  -SqlInstance kashish\SQL2019CTP | Out-GridView
    

    Execute the command, and it opens a new window for output as per the following image.

    执行命令,然后根据下图打开一个新窗口以输出。

    In the following image, we see the output in a grid format.

    在下图中,我们以网格格式看到输出。

    output in gird format

    示例3:使用辅助文件创建一个SQL Server数据库 (Example 3: Create a SQL Server database with Secondary files )

    Suppose we want to create a SQL database with the following requirements.

    假设我们要创建一个具有以下要求SQL数据库。

    • Data growth of primary data file (*.mdf) should be 40 MB

      主数据文件(* .mdf)的数据增长应为40 MB
    • We should have a secondary filegroup with a secondary data file having an original size of 30 MB and auto growth 30 MB

      我们应该有一个辅助文件组,其辅助数据文件的原始大小为30 MB,自动增长为30 MB
    • Log file growth should be 30 MB

      日志文件增长应为30 MB

    Execute following DBATools PowerShell command. In this command, we specified the following parameters.

    执行以下DBATools PowerShell命令。 在此命令中,我们指定了以下参数。

    1. -PrimaryFileGrowth parameter -PrimaryFileGrowth参数自动增长主数据文件
    2. -SecondaryFileGrowth parameter -SecondaryFileGrowth参数辅助数据文件自动增长
    3. -SecondaryFileSize parameter -SecondaryFileSize参数的辅助数据文件的初始大小
    4. -Loggrowth parameter -Loggrowth参数自动增长日志文件
    > New-DbaDatabase -Name SQLTemp1  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30
    

    filegrowth while creating SQL Server Database

    Let’s open database properties in SSMS and verify the conditions we specified.

    让我们在SSMS中打开数据库属性,并验证我们指定的条件。

    Auto growth validation

    You can notice that we did not specify the secondary file group name as well as the secondary data file name in the command. DBATools automatically assigns the filegroup and data file name for us. It derives the name from the database name. For example, in this example, it creates secondary filegroup SQLTemp1_MainData because database name is SQLTemp1.

    您可能会注意到,我们在命令中没有指定辅助文件组名以及辅助数据文件名。 DBATools自动为我们分配文件组和数据文件名。 它从数据库名称派生名称。 例如,在此示例中,由于数据库名称为SQLTemp1,因此它将创建辅助文件组SQLTemp1_MainData。

    verify secondary file group

    By default, it creates one secondary data file if we specify any parameter related to secondary file such as SecondaryFileGrowth.

    默认情况下,如果我们指定与辅助文件相关的任何参数(例如SecondaryFileGrowth) ,它将创建一个辅助数据文件

    We might want to create multiple secondary data files as well. We can specify the number of secondary files using -SecondaryFileCount parameter. In the following example, we want to create three secondary data files.

    我们可能还想创建多个辅助数据文件。 我们可以使用-SecondaryFileCount参数指定辅助文件的数量。 在下面的示例中,我们要创建三个辅助数据文件。

    > New-DbaDatabase -Name SQLTemp1  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -SecondaryFileCount 3
    

    specify Number of secondary data file

    Now go to database properties in SSMS. We can see four secondary database files in this database. In the query, we specified only three secondary files. It creates one additional secondary data file due to specified secondary data file parameters.

    现在转到SSMS中的数据库属性。 我们可以在该数据库中看到四个辅助数据库文件。 在查询中,我们仅指定了三个辅助文件。 由于指定了辅助数据文件参数,它会创建一个附加的辅助数据文件。

    Verify secondary data files

    示例4:使用DBATools在SQL Server数据库中将默认文件组指定为“辅助” (Example 4: Specify default file group as Secondary in SQL Server Database using DBATools)

    By default, SQL Server configures primary filegroup as default filegroup. Usually, we specify default filegroup other than primary filegroup. We can do it using DBATools parameter -DefaultFileGroup.

    默认情况下,SQL Server将主文件组配置为默认文件组。 通常,我们指定除主文件组之外的默认文件组。 我们可以使用DBATools参数-DefaultFileGroup来实现。

    In the following screenshot, you can see a database created using DBATools without specifying the parameter -DefaultFileGroup.

    在以下屏幕截图中,您可以看到使用DBATools创建的数据库,而没有指定参数-DefaultFileGroup。

    Verify Default file group in SSMS

    Let’s create another database by specifying the parameter -DefaultFileGroup to configure secondary filegroup as default.

    让我们通过指定参数-DefaultFileGroup来创建另一个数据库,以将辅助文件组配置为默认文件。

    >New-DbaDatabase -Name SQLTemp11  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3  -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -DefaultFileGroup Secondary
    

    Verify Default file group in SSMS

    示例5:使用指定的排序规则创建一个SQL Server数据库 (Example 5: Create a SQL Server database with the specified collation)

    By default, DBATools command creates SQL database with a default collation. If we want to specify a specific collation, we can do it using -Collation parameter.

    默认情况下,DBATools命令使用默认排序规则创建SQL数据库。 如果要指定特定的排序规则,则可以使用-Collat​​ion参数。

    In this example, we want to create SQL Server Database with a different collation Latin1_General_CS_AI. Execute the following command, and in the output, it shows the database collation same as we specified.

    在此示例中,我们要创建具有不同排序规则Latin1_General_CS_AI的 SQL Server数据库 执行以下命令,然后在输出中显示与我们指定的数据库排序规则相同的排序规则

    >New-DbaDatabase -Name SQLTemp11  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3  -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -DefaultFileGroup Secondary -Collation Latin1_General_CS_AI
    

    Specify a collation in SQL Create Database

    Let’s verify it from database properties as well. You can see database collation in database properties under the general page.

    让我们也从数据库属性中进行验证。 您可以在常规页面下的数据库属性中看到数据库排序规则。

    verify database collation

    示例6:使用特定的数据库所有者创建一个SQL Server数据库 (Example 6: Create a SQL Server database with a specific database owner)

    Once we create a database, SQL Server sets database owner as a connected user security context. For example, in my case, we connected to SQL Server using windows authentication (ID – Kashish\Test). If we do any specify any database owner, SQL Server sets Windows user Kashish\Test as owner.

    创建数据库后,SQL Server会将数据库所有者设置为连接的用户安全上下文。 例如,以我为例,我们使用Windows身份验证(ID – Kashish \ Test)连接到SQL Server。 如果我们指定任何数据库所有者,SQL Server会将Windows用户Kashish \ Test设置为所有者。

    Suppose we want to create all SQL Server database having owner SA. In DBATools, we can specify database owner using -owner parameter.

    假设我们要创建所有具有所有者SASQL Server数据库。 在DBATools中,我们可以使用-owner参数指定数据库所有者。

    Execute the following command to create a database with owner SA.

    执行以下命令以创建拥有者SA的数据库

    >New-DbaDatabase -Name SQLTemp11  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3  -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -DefaultFileGroup Secondary -Collation Latin1_General_CS_AI -owner sa
    

    In the output, it still showed the database owner Kashish\Test that is my windows user. It should set an owner as SA.

    在输出中,它仍然显示数据库所有者Kashish \ Test是我的Windows用户。 它应该将所有者设置为SA。

    SQL Create database with a specific database owner

    If we verify it in the database properties, it shows the correct database owner as specified in DBATools command.

    如果我们在数据库属性中进行验证,它将显示DBATools命令中指定的正确数据库所有者。

    Verify database owner

    示例7:创建具有最大主文件和辅助文件大小SQL Server数据库 (Example 7: Create a SQL Server database with a maximum primary and secondary file size)

    We might require setting maximum size for a primary and secondary data file in SQL Server to load a limited amount of data. It might be useful to control excessive growth of a database to avoid any disk related issues.

    我们可能需要为SQL Server中的主数据文件和辅助数据文件设置最大大小,以加载有限数量的数据。 控制数据库的过度增长以避免任何与磁盘相关的问题可能很有用。

    Note: You should not set maximum file size in production until we exactly know the requirements.

    注意:在我们完全了解要求之前,您不应在生产环境中设置最大文件大小。

    We can use –PrimaryFileMaxSize and –SecondaryFileMaxSize parameters to set the maximum size of primary and secondary files respectively.

    我们可以使用– PrimaryFileMaxSize和– SecondaryFileMaxSize参数分别设置主文件和辅助文件的最大大小。

    In the following example, we set the maximum size for both primary and secondary files to 100 MB.

    在以下示例中,我们将主文件和辅助文件的最大大小设置为100 MB。

    >New-DbaDatabase -Name SQLTemp11  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3  -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -PrimaryFileMaxSize 100 -SecondaryFileMaxSize 100
    

    Specify  maximum primary and secondary file size in SQL Server Database

    In the following screenshot, we can see that auto growth is set to 100 MB for all data files.

    在以下屏幕截图中,我们可以看到所有数据文件的自动增长均设置为100 MB。

    Verfiy maximum file size in SQL Create Database

    示例8:创建带有确认消息SQL Server数据库 (Example 8: Create a SQL Server database with a confirmation message)

    It is an excellent practice to get a confirmation prompt before we do any activity in the database. In DBATools, we can use -Confirm parameter to display a confirmation prompt. It prevents us from executing any command accidentally.

    在数据库中进行任何活动之前,获得确认提示是一种极好的实践。 在DBATools中,我们可以使用-Confirm参数来显示确认提示。 它可以防止我们意外执行任何命令。

    In the following example, we can see that it gives us a message – Are you sure, you want to perform this actions?

    在以下示例中,我们可以看到它向我们提供了一条消息– 您确定要执行此操作吗?

    We need to provide input Y or A to execute the DBATools command.

    我们需要提供输入Y或A来执行DBATools命令。

    >New-DbaDatabase -Name SQLTemp11  -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3  -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -PrimaryFileMaxSize 100 -SecondaryFileMaxSize 100 -confirm
    

    Confirmation message

    结论 (Conclusion)

    In this article, we explored creating SQL Server Database using PowerShell module DBATools. We will continue exploring useful command in DBATools in my future articles. If you had comments or questions, feel free to leave them in the comments below.

    在本文中,我们探讨了使用PowerShell模块DBATools创建SQL Server数据库。 在以后的文章中,我们将继续探索DBATools中有用的命令。 如果您有任何意见或问题,请随时将其留在下面的评论中。

    目录 (Table of contents)

    DBATools PowerShell Module for SQL Server
    PowerShell SQL Server Validation Utility – DBAChecks
    SQL Database Backups using PowerShell Module – DBATools
    IDENTITY columns threshold using PowerShell SQL Server DBATools
    DBATools PowerShell SQL Server Database Backups commands
    SQL Restore Database using DBATools
    Validate backups with SQL restore database operations using DBATools
    Fix Orphan users in SQL Server using DBATools PowerShell
    Creating a SQL Server Database using DBATools
    Get-DbaHelpIndex command in DBATools
    适用于SQL Server的DBATools PowerShell模块
    PowerShell SQL Server验证实用程序– DBAChecks
    使用PowerShell模块SQL数据库备份– DBATools
    使用PowerShell SQL Server DBATools的IDENTITY列阈值
    DBATools PowerShell SQL Server数据库备份命令
    使用DBAToolsSQL Restore Database
    使用DBATools通过SQL恢复数据库操作验证备份
    使用DBATools PowerShell修复SQL Server中的孤立用户
    使用DBATools创建SQL Server数据库
    DBATools中的Get-DbaHelpIndex命令

    翻译自: https://www.sqlshack.com/creating-a-sql-server-database-using-dbatools/

    展开全文
  • dbatools 存放日常工作所用到的运维工具 dba_tools.zip 存放MySQL DBA常用软件如sysbench pt-tools等工具一键安装包 check_mysql.py MySQL数据库巡检脚本 compare_tools 最初做这个是由于我们内部从MariaDB迁移到...
  • This article gives an overview to generate scripts for SQL Server objects with Windows PowerShell tool DBATools. 本文概述了如何使用Windows PowerShell工具DBATools为SQL Server对象生成脚本。 ...

    This article gives an overview to generate scripts for SQL Server objects with Windows PowerShell tool DBATools.

    本文概述了如何使用Windows PowerShell工具DBATools为SQL Server对象生成脚本。

    Database administrators or developers require generating scripts for SQL Server objects. We might need scripts to store a copy of object script before object change, create specific objects into other database environments such as development, UAT or non-prod environment. It is an excellent practice to keep a copy of the object before making a change to it. We can easily refer to the old script and roll back if required. Usually, we use SSMS Generate Scripts wizard to get these scripts.

    数据库管理员或开发人员需要为SQL Server对象生成脚本。 在对象更改之前,我们可能需要脚本来存储对象脚本的副本,在其他数据库环境(例如开发,UAT或非生产环境)中创建特定的对象。 在更改对象之前保留对象的副本是一种极好的实践。 我们可以轻松地参考旧脚本并在需要时回滚。 通常,我们使用SSMS生成脚本向导来获取这些脚本。

    在SSMS中生成脚本向导 (Generate Script Wizard in SSMS)

    Let’s have a quick review of Generate Scripts Wizard in SSMS.

    让我们快速回顾一下SSMS中的“生成脚本向导”。

    Right click on a database and go to Tasks and Generate Scripts.

    右键单击数据库,然后转到“任务和生成脚本”。

    Generate Script Wizard in SSMS

    It gives the option to script the entire database or specific database object.

    它提供了对整个数据库或特定数据库对象编写脚本的选项。

    Choose Objects

    Select specific database objects to script out and click on Next. We might select multiple objects as well to script out together.

    选择要编写脚本的特定数据库对象,然后单击“下一步”。 我们也可以选择多个对象来一起编写脚本。

    Select the database object to script

    In the next page, it gives us the scripting options. We get the following options.

    在下一页中,它提供了脚本选项。 我们有以下选择。

    Set Scripting options
    1. Single file per object to generate all scripts in different files 每个对象一个文件”来生成不同文件中的所有脚本
    2. Save to clipboard: we can save the generated script to the clipboard using this option

      保存到剪贴板:我们可以使用此选项将生成的脚本保存到剪贴板
    3. Save to New query window: It generates the script and opens it in a new query window of SSMS

      保存到新查询窗口:它将生成脚本并在SSMS的新查询窗口中将其打开

    Click on Advanced to set advanced scripting options. On this page, you can set various options to generate scripts. A few relevant options are as follows.

    单击高级以设置高级脚本选项。 在此页面上,您可以设置各种选项来生成脚本。 一些相关的选项如下。

    • Script for the server version

      服务器版本的脚本
    • Script primary and foreign keys

      脚本主键和外键
    • Script change tracking

      脚本更改跟踪
    • Script primary, unique keys

      脚本主唯一键
    • Types of data to script – Schema only, Data Only and Schema with Data

      要编写脚本的数据类型–仅架构,仅数据和具有数据的架构

    Advanced scripting options

    On the next page, we can review the configuration and finish to generate object scripts.

    在下一页上,我们可以查看配置并完成生成对象脚本。

    Review your selections to generate script

    We need to repeat the same process depending upon the requirements of objects script. We might need to set options different for few objects. We need to follow this wizard for a specific object in this case. It might be a time-consuming process to do it.

    我们需要根据对象脚本的要求重复相同的过程。 我们可能需要为几个对象设置不同的选项。 在这种情况下,我们需要针对特定​​对象遵循此向导。 这样做可能会很耗时。

    This approach also works on instance level only. We need to do this task only for each SQL Server instance. We cannot use this task with multiple instances altogether.

    此方法也仅适用于实例级别。 我们只需要对每个SQL Server实例执行此任务。 我们不能将这个任务与多个实例一起使用。

    In this article, I will use the AdventureWorks2017 database and HumanResources.Employee table. We can see that this table contains Primary, foreign keys, clustered, non-clustered index and triggers.

    在本文中,我将使用AdventureWorks2017数据库和HumanResources.Employee表。 我们可以看到该表包含主键,外键,聚集的,非聚集的索引和触发器。

    Sample table

    In this case, we can use PowerShell open-source module DBATools to do this task for us.

    在这种情况下,我们可以使用PowerShell开源模块DBATools为我们完成此任务。

    DBATools生成对象脚本 (DBATools to generate object scripts)

    In my previous articles on DBAtools, we explored a few essential commands to do tasks in SQL Server. We need to use a combination of commands to generate object scripts using DBATools.

    在我先前关于DBAtools的文章中,我们探讨了一些在SQL Server中执行任务的基本命令。 我们需要使用命令的组合来使用DBATools生成对象脚本。

    • Get-DbaTable

      获取数据库表

    We use Get-Help command in DBATools to get search commands containing the keyword.

    我们在DBATools中使用Get-Help命令来获取包含关键字的搜索命令。

    > Get-Help *table*
    

    Get-DbaTable DBATools command

    You can find the synopsis and syntax of the Get-DbaTable command in DBATools.

    您可以在DBATools中找到Get-DbaTable命令的摘要和语法。

    Get-DbaTable DBATools command

    Let’s run this command to get information about the HumanResources.Employee table.

    让我们运行此命令以获取有关HumanResources.Employee表的信息。

    In the following query, we use the following parameters.

    在以下查询中,我们使用以下参数。

    • SqlInstance: We specify the SQL instance using this parameter

      SqlInstance:我们使用此参数指定SQL实例
    • Database: We can specify the database name in this parameter

      数据库:我们可以在此参数中指定数据库名称
    • Table: Specify table for which we want to generate a script

      表格:指定要为其生成脚本的表格
    > Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee
    

    In the output, we can see that we get a piece of information about the index and data space, row count along with the table properties information such as FILETABLE, memory optimized, partition table, change tracking.

    在输出中,我们可以看到关于索引和数据空间,行数以及表属性信息(例如FILETABLE,内存优化,分区表,更改跟踪)的信息。

    table information using the Get-DbaTable

    We require generating scripts for the object. We need to use Get-DbaDbTable with the Export-DbaScript command to generate a script for the object.

    我们需要为对象生成脚本。 我们需要将Get-DbaDbTable与Export-DbaScript命令一起使用 为对象生成脚本。

    DBATools command Export-DbaScript allows exports of SQL Server objects from SQL Management Objects (SMO).

    DBATools命令Export-DbaScript允许从SQL管理对象(SMO) 导出 SQL Server对象。

    Let’s check the synopsis and syntax of Export-DbaScript with the following command.

    让我们使用以下命令检查Export-DbaScript的简介和语法。

    > Get-help Export-DbaScript
    

    Export-DbaScript DBATools command

    Let’s execute DBATools commands Get-DbaDbTable and Export-DbaScript to generate a script for the object.

    让我们执行DBATools命令Get-DbaDbTableExport-DbaScript来为对象生成脚本。

    > Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru 
    

    In this script, we use -Passthru parameter to display script in the window itself.

    在此脚本中,我们使用-Passthru参数在窗口本身中显示脚本。

    It generates the object script; however, we did not get scripts for keys, constraints, indexes. This script might not be useful for us because it does not replicate the source objects and gives only basis object creation script.

    它生成对象脚本; 但是,我们没有获得有关键,约束,索引的脚本。 该脚本可能对我们没有用,因为它不会复制源对象,并且仅提供基础对象创建脚本。

    Generate script using DBATools

    If we execute the above command without -Passthru parameter, it saves the script in the current user context. You can go to the directory and open the script in SSMS to go through it.

    如果我们在不带-Passthru参数的情况下执行上述命令,则会将脚本保存在当前用户上下文中。 您可以转到目录并在SSMS中打开脚本以进行浏览。

    Passthru parameter to display script

    In the SSMS Generate Script Wizard, we set the scripting options under the Advanced section. In the DBATools also, we can set the scripting options using the new command New-DbaScriptingOption.

    在SSMS生成脚本向导中,我们在“ 高级”部分下设置脚本选项。 同样在DBATools中,我们可以使用新命令New-DbaScriptingOption设置脚本选项

    Let’s explore this command to set scripting options and generate the desired script.

    让我们探索该命令来设置脚本选项并生成所需的脚本。

    New-DbaScriptingOption命令DBATools (New-DbaScriptingOption command DBATools)

    In the following screenshot, we can check the details about the New-DbaScriptingOption with the following the command

    在以下屏幕截图中,我们可以使用以下命令检查有关New-DbaScriptingOption的详细信息

    >Get-help New-DbaScriptingOption -examples 
    

    New-DbaScriptingOption command DBATools

    We can check the available scripting options using the Get-Member command. Execute the following command to get a list of available properties along with their definitions.

    我们可以使用Get-Member命令检查可用的脚本选项。 执行以下命令以获取可用属性及其定义的列表。

    > $options = New-DbaScriptingOption
    >$options | Get-Member
    

    Get members details of all available properties

    Get members details of all available properties

    We can check the value of individual property as well. For example, let’s check the value of the property DriClustered.

    我们也可以检查单个财产的价值。 例如,让我们检查属性DriClustered的值。

    > $options = New-DbaScriptingOption
    > $options.DriClustered
    

    We get the return value False for the DriClustered parameter. It is the reason that the generated script using DBATools does not contain the clustered index information.

    我们获得DriClustered参数的返回值False 。 这是使用DBATools生成的脚本不包含聚集索引信息的原因。

    set $options.DriClustered to true using DBATools

    Similarly, we can check value for other properties.

    同样,我们可以检查其他属性的值。

    set $options.DriClustered to true using DBATools

    We can change the value of required properties to TRUE and use $options object along with the parameter –ScriptingOptionsObject to generate the script with these objects.

    我们可以将必需属性的值更改为TRUE,并使用$ options对象以及参数ScriptingOptionsObject来生成包含这些对象的脚本。

    使用DBATools在对象创建脚本中添加约束 (Add constraints in object creation script using DBATools)

    Suppose we want to add all constraints in the object script. Execute the following script to change the value to TRUE for DriAllConstraints and generate the script.

    假设我们要在对象脚本中添加所有约束。 执行以下脚本,将DriAllConstraints的值更改为TRUE并生成脚本。

    > $options = New-DbaScriptingOption
    > $options.DriAllConstraints =$true
    > Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options
    

    We can see constraints as well in the script for the specified object.

    我们还可以在脚本中看到指定对象的约束。

    Add constraints in object creation script

    使用DBATools在对象创建脚本中添加非聚集索引 (Add Non clustered indexes in object creation script using DBATools)

    Let’s do the following things for this example.

    让我们为这个示例做以下事情。

    • DriAllConstraints property to false DriAllConstraints属性更改为false
    • NonClusteredIndexes property to true NonClusteredIndexes属性更改为true
    > $options = New-DbaScriptingOptionPS 
    > $options.DriAllConstraints =$false
    > $options.NonClusteredIndexes  =$true
    >  Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options
    

    Add Non clustered indexes in object creation script

    使用DBATools在对象创建脚本中添加外键 (Add Foreign key in object creation script using DBATools)

    Let’s try with a few other interesting options. Suppose we want foreign keys in the object scripts. We need to enable DriForeignKeys parameter and execute the script as follows

    让我们尝试其他一些有趣的选项。 假设我们要在对象脚本中使用外键。 我们需要启用DriForeignKeys参数并执行脚本,如下所示

    > $options.DriForeignKeys = $true
    > Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options
    

    In the output, we can see foreign key constraints along with the object creating script.

    在输出中,我们可以看到外键约束以及对象创建脚本。

    Add Foreign key in object creation script using DBATools

    使用DBATools在对象创建脚本中添加如果不存在 (Add If Not Exists in object creation script using DBATools)

    It is a good practice to check whether the object exists or not before we create an object. We might have another object with a similar name. We use SQL Exists operator to test the existence of an object in the SQL Server database.

    在创建对象之前,先检查对象是否存在是一个好习惯。 我们可能还有另一个名称相似的对象。 我们使用SQL Exists运算符来测试SQL Server数据库中对象的存在。

    Our script should include Not Exists operator, and we should create an object if it does not exists. We need to enable parameter IncludeIfNotExists to the true and generated script will contain the IF EXISTS clause.

    我们的脚本应包括Not Exists运算符,并且如果不存在则应创建一个对象。 我们需要启用参数IncludeIfNotExists为true,并且生成的脚本将包含IF EXISTS子句。

    >$options.IncludeIfNotExists = $true
    > Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options
    

    Add If Not Exists in object creation

    指定目标版本以使用DBATools生成脚本 (Specify a target version to generate a script using DBATools)

    We might have a different version of the destination SQL Server for which we want to generate a script. For example, I want to generate a script for SQL Server Compatibility level 140, whereas the source compatibility level is 150.

    我们可能要为其生成脚本的目标SQL Server具有不同版本。 例如,我想为SQL Server兼容性级别140生成脚本,而源兼容性级别为150。

    We want to set the TargetServerVersion parameter for the SQL Server compatibility level we want to generate the script.

    我们想要为要生成脚本SQL Server兼容性级别设置TargetServerVersion参数。

    >$options.TargetServerVersion = "Version140"
    >Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options
    

    Specify a target version to generate a script

    使用DBATools一起为多个对象生成脚本 (Generate scripts for multiple objects together using DBATools)

    In previous examples, we generated a script for an object using DBATools. We might want to generate scripts for multiple objects. We can select multiple objects in the Generate Scripts wizard of SSMS. In the DBATools also we can do it using variables.

    在前面的示例中,我们使用DBATools为对象生成了脚本。 我们可能想为多个对象生成脚本。 我们可以在SSMS的“生成脚本”向导中选择多个对象。 在DBATools中,我们也可以使用变量来实现。

    In the following query, we defined a $Tablename variable, and it includes two table names. We use Foreach-object loop to go through each table and generate the required script. We can consider Foreach-object loop similar to a loop in SQL Server.

    在以下查询中,我们定义了$ Tablename变量,它包含两个表名。 我们使用Foreach-object循环遍历每个表并生成所需的脚本。 我们可以考虑将Foreach对象循环类似于SQL Server中的循环。

    > $TableName = "HumanResources.Employee", 'Person.Person';
    > $options = New-DbaScriptingOption
    > $options.DriForeignKeys = $true
    > $TableName | Foreach-Object
    >> Get-DbaDbTable -ServerInstance Kashish\SQL2019CTP -Database AdventureWorks2017 $SourceDB -Table $_ | Export-DbaScript -ScriptingOptionsObject $options -Passthru;
    >>}
    

    In the following screenshot, we can see it generated scripts for both the objects.

    在下面的屏幕截图中,我们可以看到它为两个对象生成了脚本。

    Generate scripts for multiple objects together using DBATools

    结论 (Conclusion)

    In this article, we explored to generate a script using DBATools Windows PowerShell commands. We can use DBATools to automate these scripts and run as per our requirements. I would suggest reviewing them as per your environment. If you have any comments or questions, feel free to leave them in the comments below.

    在本文中,我们探索了使用DBATools Windows PowerShell命令生成脚本的方法。 我们可以使用DBATools来自动化这些脚本并按照我们的要求运行。 我建议根据您的环境对其进行审查。 如果您有任何意见或疑问,请随时将其留在下面的评论中。

    目录 (Table of contents)

    DBATools PowerShell Module for SQL Server
    PowerShell SQL Server Validation Utility – DBAChecks
    SQL Database Backups using PowerShell Module – DBATools
    IDENTITY columns threshold using PowerShell SQL Server DBATools
    DBATools PowerShell SQL Server Database Backups commands
    SQL Restore Database using DBATools
    Validate backups with SQL restore database operations using DBATools
    Fix Orphan users in SQL Server using DBATools PowerShell
    Creating a SQL Server Database using DBATools
    Get SQL Database details using DBATools
    Get-DbaHelpIndex command in DBATools
    Script SQL Server objects using DBATools
    适用于SQL Server的DBATools PowerShell模块
    PowerShell SQL Server验证实用程序– DBAChecks
    使用PowerShell模块SQL数据库备份– DBATools
    使用PowerShell SQL Server DBATools的IDENTITY列阈值
    DBATools PowerShell SQL Server数据库备份命令
    使用DBAToolsSQL Restore Database
    使用DBATools通过SQL恢复数据库操作验证备份
    使用DBATools PowerShell修复SQL Server中的孤立用户
    使用DBATools创建SQL Server数据库
    使用DBATools获取SQL数据库详细信息
    DBATools中的Get-DbaHelpIndex命令
    使用DBATools编写SQL Server对象脚本

    翻译自: https://www.sqlshack.com/script-sql-server-objects-using-dbatools/

    展开全文
  • This article will provide an overview and introduction to DBAtools, a powerful open source library of automation scripts. 本文将概述和介绍DBAtoolsDBAtools是一个强大的自动化脚本开源库。 It is ...

    This article will provide an overview and introduction to DBAtools, a powerful open source library of automation scripts.

    本文将概述和介绍DBAtools,DBAtools是一个强大的自动化脚本开源库。

    It is an essential task for DBA to automate routine activities. DBAs should always be exploring opportunities for automation in their environment. It gives you enough room to focus on other critical tasks. We can use the combination of t-SQL, SSIS and SSRS to fetch required data and present in various format automatically. We can use PowerShell scripts to automate things without much complexity. SQL Server provides two PowerShell modules SQLPS and SQLServer. We can use SQLServer PowerShell module to use new cmdlets supporting various SQL Server features and latest features.

    DBA自动化例行活动是一项基本任务。 DBA应该始终在其环境中探索自动化机会。 它为您提供了足够的空间来专注于其他关键任务。 我们可以结合使用t-SQL,SSIS和SSRS来获取所需的数据并自动以各种格式显示。 我们可以使用PowerShell脚本来自动化事情,而无需太多复杂性。 SQL Server提供了两个PowerShell模块SQLPS和SQLServer。 我们可以使用SQLServer PowerShell模块来使用支持各种SQL Server功能和最新功能的新cmdlet。

    In this article, we will discuss automation of DBA tasks with the open-source and community driven PowerShell module DBATools.

    在本文中,我们将讨论使用开源和社区驱动的PowerShell模块DBATools自动化DBA任务。

    PowerShell integrates deeply and seamlessly with SQL Server and OS and helps us perform various tasks programmatically with a single language. DBATools is a useful repository for the PowerShell commands for SQL Server tasks, which contains more than 500 commands to do tasks such as backup/restore, monitoring, administration tasks with these simple to use commands.

    PowerShell与SQL Server和OS进行了无缝无缝集成,可帮助我们使用一种语言以编程方式执行各种任务。 DBATools是用于SQL Server任务的PowerShell命令的有用存储库,其中包含500多个命令,可使用这些简单易用的命令来执行诸如备份/还原,监视,管理任务之类的任务。

    DBATools supports almost all features, editions in SQL Servers such as followings.

    DBATools支持SQL Server中的几乎所有功能,例如以下版本。

    • SQL Server versions SQL Server 2000 to SQL Server 2017

      SQL Server版本SQL Server 2000到SQL Server 2017
    • Clustered and AG instances

      集群实例和AG实例
    • All SQL Server editions

      所有SQL Server版本
    • Windows and SQL authentication

      Windows和SQL身份验证

    You can visit the URL for the documentation.

    您可以访问文档的URL

    DBATools documentation

    安装 (Installation)

    We can install DBATools using various methods. If you have an active internet connection, open the Windows PowerShell with administrative rights. You will receive the following error message without administrative rights.

    我们可以使用多种方法安装DBATools。 如果您有活动的Internet连接,请使用管理权限打开Windows PowerShell。 您将收到以下错误消息,没有管理权限。

    DBATools installation

    Right click on Windows PowerShell and Run as administrator.

    右键单击Windows PowerShell,然后以管理员身份运行

    Windows PowerShell with administrative rights

    Run the following command to install DBATools module. In PowerShell.

    运行以下命令以安装DBATools模块。 在PowerShell中。

    >Install-Module -Name DBATools
    

    DBATools automatically downloads the dependencies and installs them. We need to give permission by entering Y to import the NuGet provider as shown here.

    DBATools自动下载并安装依赖项。 我们需要输入Y来授予许可,以导入NuGet提供程序,如下所示。

    DBATools installation

    We also need to accept the module from an untrusted repository. Enter Y to accept and go ahead with the installation.

    我们还需要从不受信任的存储库接受模块。 输入Y以接受 并继续安装。

    DBATools installation

    We get the warning in the following screenshot because DBATools is already installed in my system. In my environment, I have version 0.8.693 however latest version is 0.9.781.

    由于我的系统中已经安装了DBATools,因此我们在以下屏幕截图中得到警告。 在我的环境中,我的版本为0.8.693,但最新版本为0.9.781。

    DBATools installation and upgrade

    Run the following command to upgrade the version.

    运行以下命令以升级版本。

    >Install-Module -Name DBATools -Force
    

    It downloads the required package and installs it in PowerShell module.

    它下载所需的软件包并将其安装在PowerShell模块中。

    DBATools installation and upgrade

    Once the DBATools PowerShell Module is installed, run the following command to enable remote execution of PowerShell scripts.

    安装DBATools PowerShell模块后,运行以下命令以启用PowerShell脚本的远程执行。

    > set-executionpolicy remotesigned
    

    enable remote execution of PowerShell scripts

    Azure Data Studio中的PowerShell模块 (PowerShell Module in Azure Data Studio )

    Azure Data Studio is a cross-platform integrated tool for SQL Server. It also has an in-built terminal for PowerShell, SSH. We can use this to run the SQL queries as well as PowerShell Command. We will use Azure Data Studio to run the commands using DBATools PowerShell Module.

    Azure Data Studio是用于SQL Server的跨平台集成工具。 它还具有用于PowerShell,SSH的内置终端。 我们可以使用它来运行SQL查询以及PowerShell命令。 我们将使用Azure Data Studio通过DBATools PowerShell模块运行命令。

    Launch Azure Data Studio. Go to View -> Command Palette and Terminal: Create new Integrated Terminal.

    启动Azure Data Studio。 转到查看 -> 命令面板终端:创建新的集成终端

    PowerShell Module in Azure Data Studio

    In the PowerShell terminal, run the following command to get a list of commands containing word Login.

    在PowerShell终端中,运行以下命令以获取包含单词Login的命令列表。

    >Get-Command -Name *login*
    

    Get-help command in DBATools

    Similarly, in the following image, you can see the filtered results for keyword *Copy-SQLServe*

    同样,在下图中,您可以查看关键字* Copy-SQLServe *的过滤结果。

    Showing filtered results for keyword *Copy-SQLServe*

    In this next section, we will look on a few commands in DBATools PowerShell module.

    在下一部分中,我们将研究DBATools PowerShell模块中的一些命令。

    Test-DbaDatabaseOwner (Test-DbaDatabaseOwner)

    :我们可以检查数据库所有者的数据库。

    We can get information about this command using the following query.

    我们可以使用以下查询获取有关此命令的信息。

    > get-help Test-DbaDatabaseOwner
    

    It gives information about this command along with brief information, description, and related links.

    它提供有关此命令的信息以及简要信息,描述和相关链接。

    Let us run the command on SQL named instance SQL2019CTP.

    让我们在名为实例SQL2019CTPSQL上运行命令。

    > Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP
    

    Get-help command ifor Test-DbaDatabaseOwner DBATools

    It gives information about each database in the instance. We did not specify any login name; therefore, it checks for SA by default.

    它提供有关实例中每个数据库的信息。 我们没有指定任何登录名; 因此,默认情况下会检查SA。

    >> Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP
    

    Use cases of command Test-DbaDatabaseOwner in DBATools

    In the output, we get the row for each property in the database. If we have many databases, it is difficult to read details. We can format the result in a tabular format using the following query. It represents the output in an excellent tabular representation.

    在输出中,我们获得数据库中每个属性的行。 如果我们有许多数据库,则很难阅读详细信息。 我们可以使用以下查询将结果格式化为表格格式。 它以出色的表格形式表示输出。

    In this output, we can see that OwnerMatch is False for highlighted databases as their owner is not SA. You can check this in column TargetOwner.

    在此输出中,我们可以看到突出显示的数据库的OwnerMatchFalse ,因为它们的所有者不是SA。 您可以在TargetOwner列中进行检查。

    >> Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP | Format-Table
    

    Use cases of command Test-DbaDatabaseOwner in DBATools

    Let us change database owner for SQLShackDemo_ADR to Rajendra using following command.

    让我们使用以下命令将SQLShackDemo_ADR的数据库所有者更改Rajendra

    sp_changedbowner 'rajendra'
    

    Let us rerun the command and check for the login ‘rajendra’. In the output, we can see OwnerMatch is True for database SQLShackDemo_ADR.

    让我们重新运行命令并检查登录名“ rajendra”。 在输出中,我们可以看到OwnerMatch对于数据库SQLShackDemo_ADRTrue

    >Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -TargetLogin 'rajendra' | Format-Table
    

    Checking the database owner name in the output

    We can use -SqlCredential parameter to login with alternate credentials in SQL Server instance. We can use both Windows and SQL authentication.

    我们可以使用-SqlCredential参数在SQL Server实例中使用备用凭据登录。 我们可以同时使用Windows和SQL身份验证。

    Suppose we want to login to localhost\SQL2019CTP using SQL authentication user rajendra and want to check database where targetlogin does not match ‘kashish\Test’.

    假设我们想登录到本地主机\ SQL2019CTP使用SQL身份验证的用户拉金德拉并希望检查数据库,在那里targetlogin不匹配“kashish \测试”。

    Run the below command in terminal.

    在终端中运行以下命令。

    >Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -SqlCredential rajendra -TargetLogin 'kashish\Test' | Format-Table
    

    It opens a pop-up window to provide a password for the user specified in -SqlCredential.

    它会打开一个弹出窗口,为-SqlCredential中指定的用户提供密码。

    Use cases of command Test-DbaDatabaseOwner in DBATools

    We login to the instance using rajendra and then checks for database owner that does not match targetowner.

    我们使用rajendra登录到实例,然后检查与targetowner不匹配的数据库所有者

    Use cases of command Test-DbaDatabaseOwner in DBATools

    We can further customize the output in a grid view using parameter Out-GridView. In a Grid view, we get an interactive result window. In this interactive result window, we can filter out the results as well.

    我们可以使用参数Out-GridView在网格视图中进一步自定义输出。 在网格视图中,我们得到一个交互式结果窗口。 在此交互式结果窗口中,我们也可以过滤出结果。

    Run the following code to check databases with target login ‘Kashish\Test’ in a grid output window.

    运行以下代码以在网格输出窗口中以目标登录名“ Kashish \ Test”检查数据库。

    >Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP -SqlCredential rajendra -TargetLogin 'kashish\Test' | Out-GridView
    

    Use cases of command Test-DbaDatabaseOwner in DBATools

    In the Add Criteria, you get a list of all columns present in the output. Suppose we want to filter based on the database name. Select Database from the drop-down and mention the database name for which we want to filter the results. It is an interactive filtering of results. We get the filtered result as soon as we start typing on the filter box.

    添加条件中 ,您将获得输出中存在的所有列的列表。 假设我们要基于数据库名称进行过滤。 从下拉列表中选择数据库 ,并提及我们要为其过滤结果的数据库名称。 它是结果的交互式过滤。 一旦开始在过滤器框中键入内容,我们就会得到过滤的结果。

    In the following image, you can see the filtered results for the database name contains SQLShackDemo.

    在下图中,您可以看到包含SQLShackDemo数据库名称的过滤结果

    Use cases of command Test-DbaDatabaseOwner in DBATools - Grid view

    We can filter the result set for a particular database using –Database parameter. In the following code, we filtered out the result for SQLShackDemo database.

    我们可以使用–Database参数过滤特定数据库的结果集。 在以下代码中,我们过滤掉了SQLShackDemo数据库的结果。

    >Test-DbaDbOwner -SqlInstance localhost\SQL2019CTP  -TargetLogin 'kashish\Test' -Database SQLShackDemo
    

    We get only result for a particular database in this case.

    在这种情况下,我们只能得到特定数据库的结果。

    Use cases of command Test-DbaDatabaseOwner in DBATools

    Get-DbaSpConfigure (Get-DbaSpConfigure)

    DBA need to configure various parameters in SQL Server such as maximum memory, minimum memory, dedicated administration connection, allow remote access etc. In SQL Server, we d do most of the configurations using the sp_configure command. DBATools contains PowerShell command Get-DbaSpConfigure to configure these setting easily and effectively.

    DBA需要在SQL Server中配置各种参数,例如最大内存,最小内存,专用管理连接,允许远程访问等。在SQL Server中,我们将使用sp_configure命令进行大多数配置。 DBATools包含PowerShell命令Get-DbaSpConfigure,可轻松有效地配置这些设置。

    First, let us get the information about Get-DbaSpConfigure using the get-help command. It lists out the synopsis, description, syntax for this DBATool command.

    首先,让我们使用get-help命令获取有关Get-DbaSpConfigure的信息。 它列出了此DBATool命令的简介,描述和语法。

    > get-help Get-DbaSpConfigure
    

    Use cases of command Get-DbaSpConfigure in DBATools

    The Get-DbaSpConfigure command returns all server level configuration information in SQL Server. In the following query, we specified the SQL instance name to get the all supported configuration using this command. We want the result in tabular format, therefore, specified Format-Table.

    Get-DbaSpConfigure命令返回SQL Server中的所有服务器级别的配置信息。 在以下查询中,我们指定了SQL实例名称,以使用此命令获取所有受支持的配置。 我们希望结果采用表格格式,因此,请指定Format-Table。

    >Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP | Format-Table
    

    We can see it return the default value, run value and description for each configuration.

    我们可以看到它返回每个配置的默认值,运行值和描述。

    Use cases of command Get-DbaSpConfigure in DBATools

    It is quite a long list to go through. You can check the few configurations output in the following screenshot as well.

    这是一个很长的清单。 您也可以在以下屏幕截图中查看一些配置输出。

    Use cases of command Get-DbaSpConfigure in DBATools

    We do not need information about all system configurations most of the time. Suppose we want to check whether default backup compression is enabled or not in our SQL instance. We need to pass particular configuration in –Name parameter. Execute following DBATool command to get detailed information about this configuration.

    大多数时候,我们不需要有关所有系统配置的信息。 假设我们要检查SQL实例中是否启用了默认备份压缩。 我们需要在–Name参数中传递特定的配置。 执行以下DBATool命令以获取有关此配置的详细信息。

    >Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'DefaultBackupCompression'
    

    In the output, you can see we get excellent information such as MinValue, MaxValue, ConfiguredValue, and RunningValue. It also gives a column to show whether in the specified instance, we are running with the default value for this parameter or not.

    在输出中,您可以看到我们获得了极好的信息,例如MinValue,MaxValue,ConfiguredValue和RunningValue。 它还提供了一个列来显示在指定实例中我们是否正在使用此参数的默认值运行。

    Use cases of command Get-DbaSpConfigure in DBATools

    Similarly, following DBATool command list the configuration details for Cost threshold of Parallelism.

    类似地,以下DBATool命令列出了并行度成本阈值的配置详细信息

    Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'CostThresholdForParallelism'
    

    Use cases of command Get-DbaSpConfigure in DBATools

    In the following query, we are checking whether the dedicated administrator connection is enabled on SQL instance or not.

    在以下查询中,我们正在检查是否在SQL实例上启用了专用管理员连接。

    >Get-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'RemoteDacConnectionsEnabled'
    

    Use cases of command Get-DbaSpConfigure in DBATools

    Once we get the information of any particular parameter, we can use DBATool command Set-DbaSpConfigure to change the configuration value. Suppose we want to enable the dedicated administrator connection in SQL instance. Previously, we checked that this configuration is not enabled for specified SQL instance. We can enable it using the following code by specifying configuration value 1 for ‘RemoteDacConnectionsEnabled.’

    一旦获得任何特定参数的信息,就可以使用DBATool命令Set-DbaSpConfigure更改配置值。 假设我们要在SQL实例中启用专用管理员连接。 以前,我们检查了是否为指定SQL实例启用了此配置。 通过为“ RemoteDacConnectionsEnabled”指定配置值1,我们可以使用以下代码启用它。

    >Set-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'RemoteDacConnectionsEnabled' -Value 1
    

    Use cases of command Get-DbaSpConfigure in DBATools

    Similarly, in the following code, we are changing the Cost threshold of parallelism value to 100.

    同样,在下面的代码中,我们将并行度Cost阈值更改为100。

    Set-DbaSpConfigure -SqlInstance localhost\SQL2019CTP -Name 'CostThresholdForParallelism' -Value 100
    

    In the output, we get the Previous and New value for the configuration setting.

    在输出中,我们获得配置设置的上一个和下一个值。

    Use cases of command Get-DbaSpConfigure in DBATools

    Let us verify this change using SSMS. Connect to SQL instance. Open Server properties and go to Advanced.

    让我们使用SSMS验证此更改。 连接到SQL实例。 打开服务器属性,然后转到“ 高级”

    In the following screenshot, we can see the Cost Threshold for Parallelism value is 100. It is the value we changed using DBATools configuration.

    在以下屏幕截图中,我们可以看到“并行度的成本阈值”值为100。这是我们使用DBATools配置更改的值。

    Verify configuration change from DBATools with SSMS

    结论 (Conclusion)

    In this article, we explored the use of PowerShell module DBATools to perform various DBA tasks. It contains many such important commands. I will continue exploring useful command and share in future articles.

    在本文中,我们探讨了使用PowerShell模块DBATools执行各种DBA任务。 它包含许多这样的重要命令。 我将继续探索有用的命令并在以后的文章中分享。

    目录 (Table of contents)

    DBATools PowerShell Module for SQL Server
    PowerShell SQL Server Validation Utility – DBAChecks
    SQL Database Backups using PowerShell Module – DBATools
    IDENTITY columns threshold using PowerShell SQL Server DBATools
    DBATools PowerShell SQL Server Database Backups commands
    SQL Restore Database using DBATools
    Validate backups with SQL restore database operations using DBATools
    Fix Orphan users in SQL Server using DBATools PowerShell
    Creating a SQL Server Database using DBATools
    Get SQL Database details using DBATools
    Get-DbaHelpIndex command in DBATools
    适用于SQL Server的DBATools PowerShell模块
    PowerShell SQL Server验证实用程序– DBAChecks
    使用PowerShell模块SQL数据库备份– DBATools
    使用PowerShell SQL Server DBATools的IDENTITY列阈值
    DBATools PowerShell SQL Server数据库备份命令
    使用DBAToolsSQL Restore Database
    使用DBATools通过SQL恢复数据库操作验证备份
    使用DBATools PowerShell修复SQL Server中的孤立用户
    使用DBATools创建SQL Server数据库
    使用DBATools获取SQL数据库详细信息
    DBATools中的Get-DbaHelpIndex命令

    翻译自: https://www.sqlshack.com/dbatools-powershell-module-for-sql-server/

    展开全文
  • This article will be first article of series for SQL database backup and restoration using DBAtools, a powerful open source library of PowerShell automation scripts. 本文将是使用DBAtools(一个强大的...

    This article will be first article of series for SQL database backup and restoration using DBAtools, a powerful open source library of PowerShell automation scripts.

    本文将是使用DBAtools(一个强大的PowerShell自动化脚本的开源库)进行SQL数据库备份和还原的系列文章的第一篇。

    Every organization should prepare a Disaster Recovery plan to avoid a business loss against any unexpected downtime. It can be in the form of power failure, data centre issues, cyber-attacks, natural disaster etc. We must do a regular disaster drill to be prepared any such incidents. The DBA plays a vital role in these drills.

    每个组织都应准备灾难恢复计划,以避免因任何意外停机而造成业务损失。 可能是电源故障,数据中心问题,网络攻击,自然灾害等形式。我们必须定期进行灾难演习,以准备任何此类事件。 DBA在这些演练中起着至关重要的作用。

    We used to take regular database backups for production servers to restore later in case of any loss of data. You should do regular database restoration drills to verify that your backup policy is valid and able to recover from any incidents. We need to restore a database backup on the instance on the same version of SQL Server.

    我们过去经常为生产服务器进行数据库备份,以备日后在数据丢失的情况下还原。 您应该进行常规的数据库还原演练,以验证您的备份策略有效并且能够从任何事件中恢复。 我们需要在相同版本SQL Server上的实例上还原数据库备份。

    If you are maintaining a large number of production instances, it becomes tedious work to test backups on a specified frequency. We also need to perform additional task of consistency check using DBCC CHECKDB command after database restoration. It ensures that there is no allocation or consistency error in the restore database. As a summary, we need to perform two tasks in database restoration drills:

    如果要维护大量生产实例,那么以指定频率测试备份将变得很繁琐。 数据库还原后,我们还需要使用DBCC CHECKDB命令执行其他一致性检查任务。 它确保还原数据库中没有分配或一致性错误。 作为总结,我们需要在数据库还原练习中执行两项任务:

    1. Restore database regularly from existing backups regularly

      定期从现有备份中还原数据库
    2. Database Consistency check on the newly restored database

      对新还原的数据库进行数据库一致性检查

    In SQL Server, you can write t-SQL code or create an SSIS package to perform the required steps. You need to be good in programming or SSIS package development to do so.

    在SQL Server中,您可以编写t-SQL代码或创建SSIS包以执行所需的步骤。 您需要精通编程或SSIS包开发。

    As an alternative, we can use DBATools to maintain database backups, restoration and verifications for disaster recovery purpose. In this article, we will discuss database backups using PowerShell SQL Server module DBATools.

    或者,我们可以使用DBATools维护数据库备份,还原和验证,以进行灾难恢复。 在本文中,我们将讨论使用PowerShell SQL Server模块DBATools进行数据库备份。

    • Note: We are using Azure Data Studio to run DBATools commands. You can also use Windows PowerShell for this.
    • 注意: 我们正在使用Azure Data Studio运行DBATools命令。 您也可以使用Windows PowerShell。

    使用DBAToolsSQL数据库备份 (SQL Database backups using DBATools)

    We can get all commands related to keyword Backup using Get-help.

    我们可以使用Get-help获取与关键字Backup相关的所有命令。

    >Get-Help *Backup*
    

    Backup SQL database - Database backups using DBATools

    We will verify last database backups using command Get-DbaLastBackup. In this command, we used Out-GridView to get results in grid view.

    我们将使用命令Get-DbaLastBackup验证最后的数据库备份 在此命令中,我们使用Out-GridView在网格视图中获取结果。

    >get-help Get-DbaLastBackup
    

    In below screenshot, we can see the syntax, description for this command.

    在下面的屏幕截图中,我们可以看到此命令的语法,说明。

    Backup SQL database - last database backups using command Get-DbaLastBackup.

    Let us run this command in my instance Kashish\SQL2019CTP. In the output, we can see that currently, I do not have any SQL backups for my database instance. It gives timestamp of each database backup in corresponding column such as LastFullBackup, lastDiffbackup, LastLogbackup. It also gives the details of the number of days since last full, differential and log backup.

    让我们在实例Kashish \ SQL2019CTP中运行此命令。 在输出中,我们可以看到当前我的数据库实例没有任何SQL备份。 它在相应的列(例如LastFullBackup,lastDiffbackup,LastLogbackup)中提供每个数据库备份的时间戳。 它还提供自上次完整备份,差异备份和日志备份以来的天数的详细信息。

    Backup SQL database - last database backups using command Get-DbaLastBackup.

    使用DBATools进行数据库备份 (Take a database backup using DBATools)

    We can perform database backups using Backup-DBADatabase command using DBATools in PowerShell SQL Server. Check the syntax of Backup-DBADatabase using below command.

    我们可以在PowerShell SQL Server中使用DBATools使用Backup-DBADatabase命令执行数据库备份。 使用以下命令检查Backup-DBADatabase的语法。

    >get-help Backup-DBADatabase
    

    Backup SQL database - Take database backup using DBATools

    We can take full SQL database backups, transaction log backups, and database file backups. We need to provide the following parameters to take database backup.

    我们可以进行完整SQL数据库备份,事务日志备份和数据库文件备份。 我们需要提供以下参数来进行数据库备份。

    • The server name in -parameter

      -parameter中的服务器名称
    • Database name to the -Database parameter

      -Database参数的数据库名称
    • Type of database backup using -Type parameter. If we do not specify any backup type, it takes Full backup

      使用-Type参数的数据库备份类型。 如果我们不指定任何备份类型,则需要完全备份
    • We can specify a backup directory using -BackupDirectory parameter. If we do not specify any backup directory, it takes backup in the default directory

      我们可以使用-BackupDirectory参数指定备份目录。 如果我们未指定任何备份目录,它将在默认目录中进行备份




      1. Full backup: Databasename_yyyymmddhhss.bak

        完全备份:Databasename_yyyymmddhhss.bak
      2. Log backup: Databasename_ yyyymmddhhss.trn

        日志备份:Databasename_ yyyymmddhhss.trn

    Let us perform a database backup using Backup-DBADatabase by only specifying an instance name.

    让我们仅通过指定实例名称来使用Backup-DBADatabase执行数据库备份。

    >Backup-DBADatabase -ServerInstance Kashish\SQL2019CTP
    

    Backup SQL database - Take database backup using DBATools

    We did not specify any database in the Backup-DBADatabase command in PowerShell SQL Server. It takes backups of all databases in this case.

    我们未在PowerShell SQL Server的Backup-DBADatabase命令中指定任何数据库。 在这种情况下,它需要备份所有数据库。

    We can verify the database backup using Get-DbaLastBackup command. We can see here that entry for last full backup and status as well.

    我们可以使用Get-DbaLastBackup命令验证数据库备份。 我们在这里也可以看到该条目的最新完整备份和状态。

    >Get-DbaLastBackup -SqlServer Kashish\SQL2019CTP | Out-GridView
    

    If the database recovery model is full and log backups are not running, you can see in the status ‘No log backup in the last hour’.

    如果数据库恢复模型已满并且日志备份未在运行,则可以在状态“最近一小时没有日志备份”中看到。

    Backup SQL database - Take database backup using DBATools

    In the default backup directory, database backup file is present. We can see the backup file in the format of databasename_yyyymmddhhss.bak as per default full backup format.

    在默认备份目录中,存在数据库备份文件。 根据默认的完整备份格式,我们可以看到格式为databasename_yyyymmddhhss.bak的备份文件。

    Backup SQL database - Take database backup using DBATools

    使用DBATools进行差异数据库备份 (Differential database backup using DBATools)

    Suppose we want to take a differential backup for SQLShackDemo database only. We can specify the database name using -database parameter. We also need to specify a backup type as Differential.

    假设我们只想对SQLShackDemo数据库进行差异备份。 我们可以使用-database参数指定数据库名称。 我们还需要将备份类型指定为“差异”。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo
    

    It takes differential backup for the specified database and returns the details such as type, totalsize, start time, end time and duration for this backup.

    它为指定的数据库执行差异备份,并返回此备份的详细信息,例如类型,总计大小,开始时间,结束时间和持续时间。

    Backup SQL database - Differential backup using DBATools

    Now, we want to retake differential SQL database backup for user databases SQLShackDemo and SQLShackDemo_ADR in the directory C:\TEMP\Backup. We can specify multiple databases name in -database parameter separated by a comma.

    现在,我们要为目录C:\ TEMP \ Backup中的用户数据库SQLShackDemo和SQLShackDemo_ADR重新获取差异SQL数据库备份。 我们可以在-database参数中用逗号分隔来指定多个数据库名称。

    In the following query, we specified the backup directory in the BackupDirectory parameter.

    在以下查询中,我们在BackupDirectory参数中指定了备份目录。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo,SQLShackDemo_ADR -BackupDirectory C:\TEMP\Backup\
    

    You can see the differential database backup for both the databases in the specified location.

    您可以在指定位置看到两个数据库的差异数据库备份。

    使用DBATools的自定义SQL数据库备份文件格式 (Custom SQL database backup file format using DBATools)

    You might notice that backup file format is the same for both full and differential backup as database_YYYYMMDDHMM. We want to give a customized name for the backup file. We need to do following changes in command for backup using DBATools.

    您可能会注意到,完整备份和差异备份的备份文件格式都与database_YYYYMMDDHMM相同。 我们要为备份文件指定一个自定义名称。 我们需要对命令进行以下更改,以便使用DBATools进行备份。

    • BackupFileName: Specify a format for the database backup file. I want to include database name, backup type (Full, Differential, and Log) along with a timestamp of backup execution. For example, I will define the backup file name as dbname-backuptype-timestamp.bak BackupFileName:指定数据库备份文件的格式。 我想包括数据库名称,备份类型(完整,差异和日志)以及备份执行的时间戳。 例如,我将备份文件名定义为dbname-backuptype-timestamp.bak
    • ReplaceInName: We need to specify this switch in backup command to replace the strings in backupfilename with actual values ReplaceInName:我们需要在backup命令中指定此开关,以将backupfilename中的字符串替换为实际值

    In this example, we are specifying BackupFileName as dbname-backuptype-timestamp.bak. Once we set ReplaceInName, command works as follows

    在此示例中,我们将BackupFileName指定为dbname-backuptype-timestamp.bak。 设置ReplaceInName之后,命令的工作方式如下

    • dbname – it replaces dbname with the actual database name

      dbname –用实际的数据库名称替换dbname
    • timestamp – It specifies timestamp in BackupFileName

      时间戳–在BackupFileName中指定时间戳
    • backuptype – We get backup type in the BackupFileName

      backuptype –我们在BackupFileName中获取备份类型

    Run the following command in PowerShell SQL Server.

    在PowerShell SQL Server中运行以下命令。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo  -BackupDirectory C:\TEMP\Backup -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName
    

    Now look at the database backup directory and notice the backup file name.

    现在查看数据库备份目录,并注意备份文件名。

    We might also want to add SQL instance name in the backup file name; therefore, you can run the following command with instancename parameter in BackupFileName.

    我们可能还想在备份文件名中添加SQL实例名。 因此,您可以在BackupFileName中使用instancename参数运行以下命令。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo  -BackupDirectory C:\TEMP\Backup - BackupFileName instancename-dbname-backuptype-timestamp.bak -ReplaceInName
    

    We can see SQL instance name in a backup file as well.

    我们也可以在备份文件中看到SQL实例名称。

    Similarly, add a servername in a SQL database backup file name to specify server name as well.

    同样,在SQL数据库备份文件名中添加服务器名以也指定服务器名。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Differential -Database SQLShackDemo  -BackupDirectory C:\TEMP\Backup -BackupFileName servername-instancename-dbname-backuptype-timestamp.bak -ReplaceInName
    

    使用DBATools将SQL数据库备份到单独的文件夹中 (SQL Database backups into a separate folder using DBATools)

    Suppose we want to create a separate folder for each database and all of its SQL database backups should go under it. It allows us to look for all database backups in a specified directory easily. If we have a large number of databases, it is not possible to manually create a folder and specify in backup command. DBATools solves these issues with -CreateFolder string. It automatically creates a folder for each database in a specified backup directory and takes backup of a particular database.

    假设我们要为每个数据库创建一个单独的文件夹,并且所有SQL数据库备份都应位于该文件夹下。 它使我们能够轻松地在指定目录中查找所有数据库备份。 如果我们有大量数据库,则无法手动创建文件夹并在backup命令中指定。 DBATools使用-CreateFolder字符串解决了这些问题。 它会自动为指定备份目录中的每个数据库创建一个文件夹,并备份特定数据库。

    Run the following command to take all databases backup in SQL instance in a separate folder for each database.

    运行以下命令以将SQL实例中的所有数据库备份到每个数据库的单独文件夹中。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP  -BackupDirectory C:\TEMP\Backup -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName -CreateFolder
    

    Backup SQL database - Database backs into a separate folder using DBATools

    Now look at the SQL database backup directory, and you can see a separate folder for each database. The SQL Database backup is also placed in a particular database folder.

    现在查看SQL数据库备份目录,您可以看到每个数据库的单独文件夹。 SQL数据库备份也放置在特定的数据库文件夹中。

    Backup SQL database - Database backs into a separate folder using DBATools

    If the folder already exists, it does not create a separate folder or overwrites it. It ignores step to create a directory and takes a backup in the existing directory only.

    如果该文件夹已经存在,则不会创建单独的文件夹或将其覆盖。 它忽略创建目录的步骤,仅在现有目录中进行备份。

    Backup SQL database - Database backs into a separate folder using DBATools

    SQL Server supports backup compression. It is good practice to take compressed backup. We might have backup compression enabled at the instance level. We do not want to check compression configuration at instance level each time. We should specify compression in backup command as well to ensure the backup is compressed.

    SQL Server支持备份压缩。 最好执行压缩备份。 我们可能在实例级别启用了备份压缩。 我们不想每次都在实例级别检查压缩配置。 我们还应该在backup命令中指定压缩,以确保备份被压缩。

    使用DBATools压缩备份 (Compressed backup using DBATools)

    We can use -CompressBackup string to take compressed backup using DBATools command.

    我们可以使用-Co​​mpressBackup字符串使用DBATools命令进行压缩备份。

    For this demo, I have taken a SQL database backup of SQLShackDemo with and without compression to show the difference.

    在此演示中,我已进行了SQLShackDemoSQL数据库备份(带压缩和不带压缩)以显示差异。

    • SQL database backup size with compression: 94.7 MB

      带压缩SQL数据库备份大小:94.7 MB
    • SQL database backup size without compression: 187 MB

      不压缩SQL数据库备份大小:187 MB

    使用DBATools的仅复制SQL数据库备份 (Copy-Only SQL database backup using DBATools)

    We define a backup policy to take regular database backups. Sometimes we get the requirement for an ad-hoc backup. This ad-hoc backup might affect the backup LSN chain and we might need to reinitiate backup chain. SQL Server provides a solution in terms of copy-only backup. By default, DBATools takes a normal full backup of a database. We can specify CopyOnly string in backup command to take copy-only backup. It does not impact the LSN of database backups.

    我们定义了备份策略以进行常规数据库备份。 有时我们会收到临时备份的要求。 此临时备份可能会影响备份LSN链,我们可能需要重新启动备份链。 SQL Server提供了仅复制备份方面的解决方案。 默认情况下,DBATools将对数据库进行常规的完整备份。 我们可以在backup命令中指定CopyOnly字符串以执行仅复制备份。 它不会影响数据库备份的LSN。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP  -BackupDirectory C:\TEMP\Backup -Database SQLShackDemo -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName  -CompressBackup -CopyOnly
    

    Backup SQL database - Copy-Only  backup using DBATools

    使用DBATools进行SQL数据库备份验证和确认 (SQL database backup verification and validation using DBATools)

    We want to perform database backup validation to avoid any corruption while writing backup in the media. It ensures that we have a compatible and verified backup file. SQL Server provides following backup validations.

    我们希望执行数据库备份验证,以避免在介质中写入备份时发生任何损坏。 它确保我们具有兼容且经过验证的备份文件。 SQL Server提供以下备份验证。

    • Perform CheckSum before writing to media

      在写入媒体之前执行CheckSum
    • Verify backup when finished

      完成后验证备份

    We can specify Checksum and Verify string to do these validations. We should add these strings to have a consistent backup and avoid any issues during database restoration.

    我们可以指定Checksum和Verify字符串来进行这些验证。 我们应该添加这些字符串以具有一致的备份,并避免在数据库还原期间出现任何问题。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP  -BackupDirectory C:\TEMP\Backup -Database SQLShackDemo -BackupFileName dbname-backuptype-timestamp.bak -ReplaceInName  -CompressBackup -CopyOnly -CheckSum –Verify
    

    Backup SQL database - Backup verification and validation using DBATools

    使用DBATools备份事务日志 (Transaction log backup using DBATools)

    We can specify -Type Log in backup command to take a transaction log backup.

    我们可以指定-Type Log in backup命令进行事务日志备份。

    >Backup-DBADatabase -SqlInstance  Kashish\SQL2019CTP -Type Log -Database msdb -BackupDirectory C:\TEMP\Backup\
    

    Backup SQL database - Transaction log backup using DBATools

    结论 (Conclusion)

    In this article, we explored about database backups using PowerShell SQL Server module DBA-Tools. We can take backups multiple configurations and format using DBATools. We will cover database restoration with DBATools in my next article.

    在本文中,我们探讨了使用PowerShell SQL Server模块DBA-Tools进行数据库备份的方法。 我们可以使用DBATools备份多种配置和格式。 我的下一篇文章将介绍使用DBATools进行数据库还原。

    目录 (Table of contents)

    DBATools PowerShell Module for SQL Server
    PowerShell SQL Server Validation Utility – DBAChecks
    SQL Database Backups using PowerShell Module – DBATools
    IDENTITY columns threshold using PowerShell SQL Server DBATools
    DBATools PowerShell SQL Server Database Backups commands
    SQL Restore Database using DBATools
    Validate backups with SQL restore database operations using DBATools
    Fix Orphan users in SQL Server using DBATools PowerShell
    Creating a SQL Server Database using DBATools
    Get SQL Database details using DBATools
    Get-DbaHelpIndex command in DBATools
    适用于SQL Server的DBATools PowerShell模块
    PowerShell SQL Server验证实用程序– DBAChecks
    使用PowerShell模块SQL数据库备份– DBATools
    使用PowerShell SQL Server DBATools的IDENTITY列阈值
    DBATools PowerShell SQL Server数据库备份命令
    使用DBAToolsSQL Restore Database
    使用DBATools通过SQL恢复数据库操作验证备份
    使用DBATools PowerShell修复SQL Server中的孤立用户
    使用DBATools创建SQL Server数据库
    使用DBATools获取SQL数据库详细信息
    DBATools中的Get-DbaHelpIndex命令

    翻译自: https://www.sqlshack.com/sql-database-backups-using-powershell-module-dbatools/

    展开全文
  • In my earlier PowerShell SQL Server article, SQL Database Backups using PowerShell Module – DBATools, we explored the importance of a disaster recovery solution for an organization. Microsoft offe...
  • In this article, we will review PowerShell SQL Server module DBATools to identify IDENTITY columns about to reach the threshold. 在本文中,我们将回顾PowerShell SQL Server模块DBATools,以识别即将达到...
  • dbatools是PowerShell模块,您可能会认为它类似于命令行SQL Server Management Studio。 该项目最初只是Start-SqlMigration.ps1 ,但现在已经发展成为包含的集合,可帮助自动执行SQL Server任务并鼓励最佳实践。 ...
  • This article gives an overview of Orphan users and fixing them using DBATools PowerShell. 本文概述了Orphan用户,并使用DBATools PowerShell对其进行了修复。 孤儿用户概述 (Overview of Orphan Users...
  • PLSQL Developer 11.0.6.1776 中文最新官网注册版含注册机DBATools
  • DBATools is an open-source PowerShell that contains a collection of useful commands. In this series of articles on DBATools (see TOC at the bottom) we performed installation of it. We also...
  • DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件,主要功能是提供许多DBA数据库管理的功能,使ORACLE工程师不仅可以使用PL/SQL Developer进行开发设计,同时也可以进行数据库管理及监控,大大提高...
  • In the series of articles on DBATools, (see TOC at the bottom) we are exploring useful DBATools command to perform administrative SQL tasks. In this article, we will explore useful co...
  • In this article, we will explore database backup validation by with SQL restore database operations using DBATools. 在本文中,我们将探讨通过使用DBATools进行SQL恢复数据库操作来进行数据库备份验证。 ...
  • If you haven’t heard of dbatools, it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you...
  • 此工具的主要功能有:绿色,已破解,中文操作文档,附加dbatools插件(可用于扩展数据库表空间等,此功能经常使用,完全可保证),是学习oracle进行提高的必备工具,希望对学习oracle数据的朋友有所帮助。
  • PLSQLDeveloper,oracle数据库开发工具,不多说了,内有注册机。不让上传大于20M的,汉化包在本空间另一个资源里。...dbatools可以修改表空间大小,生成word版的数据字典,很小很强大。详见其内说明。
  • 最新plsql工具,包含:PL.SQL.Developer.v8.0.0.1480.Keygen.rar; DBATools V1.1.0 Beta1 2010-01-28.rar;chinese.exe;plsqldev804.exe
  • PLSQL Developer 8.04.1514 简体中文版(含注册机,DBATools插件)

空空如也

空空如也

1 2 3 4 5 ... 10
收藏数 196
精华内容 78
关键字:

dbatools