Tag: dbatools

Migrate an entire SQL instance from one server to another using dbatools

The following powershell module allows you to migrate an entire SQL instance from one server to another. Best part is that it is not complex and takes a single line command to perform the migration.

Start-DbaMigration -Verbose -Source "SQLSERVER01" -Destination "SQLSERVER02" -BackupRestore -SharedPath "\\NetworkShare\SQLBackups\Migration" -Exclude Credentials,
LinkedServers,
SpConfigure,
CentralManagementServer,
DatabaseMail,
SysDbUserObjects,
SystemTriggers,
BackupDevices,
Audits,
Endpoints,
ExtendedEvents,
PolicyManagement,
ResourceGovernor,
ServerAuditSpecifications,
CustomErrors,
DataCollector,
StartupProcedures

Copy Linked Server connection from one server to another

Have you ever had to create a linked server connection on a server when you build a replacement to existing one? Here is a great one-line command to copy linked server connection from one server to another. Best part I like is that you don’t need to know the password. DBAtools will do that for you.

Copy-DbaLinkedServer -Source SQLSERVER01 -Destination SQLSERVER02 -LinkedServer SALESFORCE -Force

Copies over two SQL Server Linked Servers (SALESFORCE) from sqlserver01 to sqlserver02. With switch -Force, if the credential already exists on the destination, it will be dropped. If you want to skip the drop and create, exclude the -Force switch.

dbatools – Run the sql query against all instances in the group on the CMS

I love dbatools. Lately, I have been using it regularly on my job. One thing that I do from time to time is check on various parameters like uptime, version, edition etc on various SQL servers. I would love to be able to check the uptime or latest cumulative update version on a group of servers registered under Central management server. Below is a command I found through dbatools module:

Get-DbaRegServer -SqlInstance [SERVERNAME] -Group Production | Invoke-DbaQuery -Query 'SELECT @@version'

The above one-line statement runs the query SELECT @@version across all the servers registered under group name Production on Central management server.

How do you clone SQL permissions from one account to another using dbatools

When a new hire or existing user needs access to a database or multiple databases. Easiest way is to figure out if there is any existing user whose permissions can be cloned and applied to the new user.

Instructions

The following step works if DBAUtility database is already setup and you are running SQL agent job to capture a snapshot of all user permissions across the entire SQL instance.

USE DBAUtility
EXEC perms.clonePermissions @UserName = N'Domain\user1',      -- nvarchar(256)
                            @NewUser = N'Domain\user2',       -- nvarchar(256)
                            @logintype = 'U',      -- char(1)
                            @CreateLogins = 1, -- bit
                            @ExecuteScript = 1-- bit

How to run native SQL backups across multiple servers and multiple databases

Follow the below process to run SQL backups across one or more SQL servers, one or more databases using a simple powershell command. This uses the dbatools, open source powershell modules. You would use this in a scenario when Commvault server is down.

Instructions

Run the backups using the following powershell script:

$servers = 'SQLSERVER01','SQLSERVER02'
$filepath = '\\NetworkPath\Backup\servername\dbname'
$dbnamelist = 'DB01','DB02'
foreach ($server in $servers)
{
Backup-DbaDatabase -SqlInstance $server -database $dbnamelist -BackupDirectory $filepath -Type Full -CompressBackup -ReplaceInName -BuildPath
}

In the example above, the script would backup databases named ‘DB01′ and ‘DB02’ on both servers listed in the $servers variable. In order to backup all the databases, remove the -database parameter.

Creating Directories Dynamically

In the previous examples we just wrote the backups to the root of a file share. But sometimes you’ll want to organize or separate your backups based off of server name or database name or backup type. This is something else that dbatools makes very simple to do dynamically. This is done by using the –ReplaceInName parameter and then specifying keywords in your FilePath and Path strings.

-ReplaceInName
If this switch is set, the following list of strings will be replaced in the FilePath and Path strings:
instancename – will be replaced with the instance Name
servername – will be replaced with the server name
dbname – will be replaced with the database name
timestamp – will be replaced with the timestamp (either the default, or the format provided)
backuptype – will be replaced with Full, Log or Differential as appropriate

The backup jobs have already been created for certain critical servers which will enable us to switch to an alternative way to keep running backups in case Commvault server goes down.

Run the below script to enable the backup jobs across multiple servers:

# Run the following to enable native SQL backups to a network path
#Backup Location = '\\NetworkPath\Backup\servername\dbname'
$servers = 'SQLSERVER01','SQLSERVER02','SQLSERVER03'
foreach($server in $servers)
{
Set-DbaAgentJob -SqlInstance $server -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'DatabaseBackup - USER_DATABASES - DIFF', 'DatabaseBackup - USER_DATABASES - FULL' -Enabled
}

# Run the following to enable transaction log backups for selected servers
Set-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'DatabaseBackup - SYSTEM_DATABASES - LOG' -Enabled

 Run the below script to disable the backup jobs across multiple servers:

# Run the following to disable native SQL backups 
$servers = 'SQLSERVER01','SQLSERVER02','SQLSERVER03'
foreach($server in $servers)
{
Set-DbaAgentJob -SqlInstance $server -Job 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'DatabaseBackup - USER_DATABASES - DIFF', 'DatabaseBackup - USER_DATABASES - FULL' -Disabled
}
# Run the following to disable transaction log backups on selected servers
Set-DbaAgentJob -SqlInstance SQLSERVER01 -Job 'DatabaseBackup - SYSTEM_DATABASES - LOG' -Disabled