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
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.
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
Install homebrew and run the following commands
#As of 2020, Homebrew does not allow to use shallow copy, you need to fetch from git using unshallow tag
sudo git -C "$(brew --repo homebrew/core)" fetch --unshallow
brew tap mongodb/brew
#Run this to install mongodb 4.2 or whatever is the latest version
brew install mongodb-community@4.2
#Run this to start Mongodb
brew services start mongodb-community
Run this check if mongodb service is running
ps aux | grep -v grep | grep mongod
#run this to connect to Mongodb
$mongo
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.
Database Blog 