Backup Database with Custom Script
Creating a custom database backup script to handle your database backup. usp_bu_user_dbs FULL Backup
Differential Backup
How do the differential backups work from a restore perspective? Depending on your backup schedule and the time when the failure occurred would dictate the detailed steps that would need to be taken. If you need to have your current SQL Server databases back up and running, then the full backup would be restored followed... » read more
Reducing Size Of SQL Backup
Reducing Size of SQL Backup Is your database in “Simple” recovery mode? If so, it’ll produce a lot less transaction log entries, and the backup will be smaller. Recommended for development – but not for production. If it’s in “FULL” recovery mode – do you do regular transaction log backups? That should limit the growth... » read more
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
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