Month: March 2021

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

Get a list of active SSRS subscriptions

Recently, I was asked by developers to get a list of all the active SSRS subscriptions. I found this script on msdn but improvized it to include LastRuntime and LastRunStatus.

USE ReportServer
go
SELECT   c.[Name] ReportName,           
s.ScheduleID JobName,           
ss.[Description] SubscriptionDescription,           
ss.DeliveryExtension SubscriptionType,
ss.LastRunTime,
ss.LastStatus,
c.[Path] ReportFolderPath,           
row_number() over(order by s.ScheduleID) as rn             
into
#Temp  
FROM     
ReportSchedule rs           
INNER JOIN Schedule s ON rs.ScheduleID = s.ScheduleID           
INNER JOIN Subscriptions ss ON rs.SubscriptionID = ss.SubscriptionID           
INNER JOIN [Catalog] c ON rs.ReportID = c.ItemID AND ss.Report_OID = c.ItemID   

SELECT * from #temp
DROP TABLE #Temp

Extract SQL table data into an excel spreadsheet

If you ever need to extract data from a SQL table into a spreadsheet. Here is a method to do it using BCP command line utility

bcp StackOverflow.dbo.Users out C:\Temp\UsersExtract.csv -SSQLSERVER01 -c -t"," -T

-S Servername switch (SQLSERVER01 is the servername)

out File path (csv file is created at C:\Temp folder)

StackOverflow is the database name

dbo.Users is the SQL table

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.

Backup/Restore a SQL TDE certificate

Database backups are critical to any organization. Similarly, we need to ensure the TDE certificates are backed up in case of a DR scenario. TDE cert is present on any database that has encryption at rest enabled on it.

Below script takes backup of the master key, TDE cert and Private key attached to the cert:

BACKUP MASTER KEY
TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_MK'
ENCRYPTION BY PASSWORD = 'StrongPassword';
GO

USE [master];
GO
BACKUP CERTIFICATE Livongo_Ascend_Cert TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY
(
    FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
    ENCRYPTION BY PASSWORD = 'StrongPassword'
);
GO

Below is the script to use in a DR scenario or when you need to restore an encrypted backup to a new server:

/* This creates the service master key*/ 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'
GO

/* This creates the TDE cert from backup */
Use [master]
GO
CREATE CERTIFICATE TDE_databasename
FROM FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY (
FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
DECRYPTION BY PASSWORD='StrongPassword')
GO

/* This creates the encryption key using the cert we created in the above step*/
Use [databasename]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_databasename
GO

/* This step enables the encrption on the database*/
ALTER DATABASE [databasename]
SET ENCRYPTION ON
GO

Use the following script to view all the encrypted DBs and their Cert name, encryption key used.

SELECT db_name(database_id) AS [Database Name],
    dek.key_length as [Key Length],
    case encryption_state when '0' then 'No database encryption key present, no encryption'
    when '1' then 'Not Encrypted'
    when '2' then 'Encryption in Progress'
    when '3' then 'Encrypted'
    when '4' then 'Key Change in Progress'
    when '5' then 'Decryption in Progress'
    when '6' then 'Protection Change in Progress'
    end as [Encryption Status] ,
    key_algorithm as [Key Algorithm],
    Name as [Cert Name],
    pvt_key_encryption_type_desc as [Pvt Key Desc],
    [subject] as [Subject],
    [expiry_date] as [Expiry Date],
    [start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint
%d bloggers like this: