Export SQL Server config using dbatools

Exports SQL Server ALL database restore scripts, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases, system triggers and backup devices from one SQL Server to another.

Export-DbaInstance consolidates most of the export scripts in dbatools into one command.

This is useful when you’re looking to Export entire instances. It less flexible than using the underlying functions. Think of it as an easy button. Unless an -Exclude is specified, it exports:

All database ‘restore from backup’ scripts. Note: if a database does not have a backup the ‘restore from backup’ script won’t be generated. All logins. All database mail objects. All credentials. All objects within the Job Server (SQL Agent). All linked servers. All groups and servers within Central Management Server. All SQL Server configuration objects (everything in sp_configure). All user objects in system databases. All system triggers. All system backup devices. All Audits. All Endpoints. All Extended Events. All Policy Management objects. All Resource Governor objects. All Server Audit Specifications. All Custom Errors (User Defined Messages). All Server Roles. All Availability Groups. All OLEDB Providers.

The exported files are written to a folder with a naming convention of “machinename$instance-yyyyMMddHHmmss”.

Export-DbaInstance -SqlInstance SQLSERVER01 -Path \\SQLSERVER01\Backups\ -NoPrefix 

Redshift – Script to generate statements to recreate users and groups

--Script to generate statements to re-create users in redshift
select 'create user '+ usename +' password ''Hidden1!'' ;' from pg_user
order by usename asc;

--Script to generate statements to re-create groups 
select 'create group '+ groname +';' from pg_group
order by groname asc;

--Script to generate statements to add users to various groups
SELECT 'alter group '+ groname + ' add user ' + usename + ';'
FROM "admin".v_get_users_in_group
order by groname , usename;

Difference between users, groups, and roles on Postgres and Redshift

Postgres

We’ll start here, because Postgres generally sets the standard for relational databases.

In Postgres 9, users and groups were replaced with roles (Postgres have been very kind, and have maintained user and group as aliases for role for backwards compatibility). The difference between users and roles? Simply put, users can now be considered as roles with the ability to login.

  • Membership: Any role can be a member of another role, so you can create a hierarchical structure with as many tiers as you’d like.
  • Ownership: Any role can own relations.
  • Privileges: Any role can have privileges granted to it.
  • Inheritance: Up to you! Roles can be configured such that any members automatically inherit the privileges of the role, or can be configured to force a member to explicitly change their role in order to use those privileges.

Redshift

Redshift forked from Postgres somewhere around version 8. As such, roles look more like an old-school Postgres database rather than the role-based world of Postgres 9 – on Redshift, users and groups are separate entities.

  • Membership: Only a user can be a member of groups, i.e. a group cannot be a member of other groups, so you can only have a two-tiered structure.
  • Ownership: Users, rather than groups, own relations.
  • Privileges: Both users and groups can have privileges granted to them.
  • Inheritance: Users automatically inherit all privileges of any groups they are a member of.

Show Actual Maximum Column Lengths In Redshift

#!/bin/bash
#
# Many third-party data sources do lazy character column definitions, making them varchar(65535), which is stoopid.
# This little script will read in the DDL for a table, parse out the columns defined like this and then hork out a
# SQL script that discovers the actual max length of the actual data for more efficient DDL.  Suggested use is to
# run this script and output it to a xxxx.sql file, then \i that file in Redshift while in the correct schema.
#
# Assumes tablename will be passed on the command line and a file, tablename.sql will exist in this dir.

TABLENAME=${1}

if [ "${TABLENAME}" == "" ]
then
	echo -e "\nUSAGE: $0 redshiftTableName\n\nExpects redshiftTableName.sql to exist in this directory.\n\n"
	exit 1
fi

COLNAMES=`grep 65535 ${TABLENAME}.sql | cut -f2 -d","|cut -f1 -d" "`

for COL in ${COLNAMES}
do
 echo "SELECT MAX(LEN(${COL})) AS max_${COL}  FROM ${TABLENAME};"
done
%d bloggers like this: