EugeneChiang.com

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

Native 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

RedGate SQL Search

https://www.red-gate.com/products/sql-development/sql-search/ Find SQL fast in SQL Server Management Studio and Visual Studio Find fragments of SQL in tables, views, stored procedures, functions, jobs, and more Quickly navigate to objects wherever they happen to be on a server Search across multiple object types and multiple databases Find all references to an object Search with booleans and... » 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

Drop and Recreate database from Always On High Availability From Script

If you need to re-create the database, you have to remove the database from the Always On High Availability Group first before you can drop the database. To recreate the Always On Availability, create a backup of the database on the primary server and then restore the backup to the replica server. Drop Database from... » 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

Copy Table using SELECT INTO

Example: Use the following to create a copy of the original table data in the database.

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

Email SQL Query Result

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.