Database Settings and Backup File Size

Note Settings Full Backup File Size Original Database Database Size: 1823 MB Recovery model: Full 738 MB After change recovery model to Simple Database Size: 1823 MB Recovery model: Simple 738 MB After shrink log file Database Size: 739 MB Recovery model: Simple 738 MB

Backup Database From Script

Full Database Backup Output 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 92256 pages for database ‘MyDB’, file ‘MyDB’ on file 1. 100 percent processed. Processed 3 pages for database ‘MyDB’, file ‘MyDB_log’ on file... » read more

In-Place Upgrade from SQL Server 2014 to 2016

How to Upgrade SQL 2014 to SQL 2016 Mount the SQL 2016 ISO or media on your current SQL 2014 server. Select “Upgrade from a previous version of SQL Server” SSRS 2016 Part of SQL Server 2016 install. Select “Reporting Services – Native” from Instance Features https://social.technet.microsoft.com/wiki/contents/articles/50902.sql-server-2016-install-and-configure-ssrs.aspx https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/upgrade-and-migrate-reporting-services?view=sql-server-ver15 Notes: Backup all databases in case the... » read more

Always On and TDE Example

Enabling TDE in AlwaysOn setup Create MS SQL master key on primary server and replica server. Password can be the same or different. Create MS SQL certificate on primary server and copy over to replica server. On Replica, restore the certificate. Use script to enable TDE for each database. Make sure the database is fully... » read more

Import Data from Excel

Objective: Import the content of the excel to a table in the production database. First, import the content of the excel file to a staging database, massage the data, and then export the data as a data script. Steps 1. Create new excel file and copy the content to the new excel file. Original excel... » read more

Enable Failure Email for SQL Agent Jobs

How to Enable Failure Emails for SQL Agent Jobs Expand SQL Server Agent. Expand Jobs. Right click on a Job > Properties. Go to the Notifications tab. Click to enable Email. Select the correct Operator. Choose “When the job fails” Hit OK. Note: Make sure the SQL Server Mail Account and Profile has been setup correctly.

Linked server was unable to begin a distributed transaction.

Error: The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “xxxx” was unable to begin a distributed transaction. Fix: The default settings were not configured correctly to allow communication with the other servers.  These configuration settings are found under the Component Services snapin which can be launched by typing dcomcnfg.msc at a run/command... » read more