:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Jan 22 / 2018
  • 0
Generate restore script for last full backup of multiple databases
Backup and Restore, dbDigger, T-SQL Scripts

Generate restore script for last full backup of multiple databases

In a previous post i shared various commands and scripts used during databases migration process. Restore script there is not able to generate scripts for multiple databases in a single execution. Following script will generate restore script of multiple databases for their last full backup.

Following are the aspects to consider while using the script

  • Modify the select statement of cursor to get your required databases selected.
  • Modify the WITH parameters in script like REPLACE, KEEP_CDC, RECOVERY and STATS as per your requirement.
  • Any DB without FULL backup will not generate error but will be skipped in result.
  • Once result is retrieved you can review and change the file paths for files before starting the restore on destination server.
  • Setting the result to text will help to select all the scripts in one go.

Set SSMS result toText format

Following is the script to generate restore statements of multiple databases for their last full backups.

 

  • Oct 02 / 2017
  • 0
T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Exec vs Sp_ExecuteSQL for dynamic SQL execution

Exec and sp_executeSQL both are used for executing the dynamic sql statements. There are some differences between the both

EXEC

Exec is available since early versions before the sp_executesql. It has no direct support of using or storing the local variables. Also using Exec makes the setup vulnerable to sql injection attacks as parameters appear as part of code.

SP_ExecuteSQL

sp_executesql is improved way to execute dynamic sql statements. It provides mechanism to use and store values in local variables. This mechanism is not vulnerable to sql injection as variables do not appear as part of code but as an operand. Also it enables the sql engine to use the cached execution plan of the statement like a stored procedure.

Conclusion

sp_executesql has upper hand as compared to exec method of executing dynamic sql. Keeping in view the flexibility, efficiency and security of sp_executesql we should opt it for executing dynamic SQL.

  • Aug 31 / 2017
  • 0
T-SQL Scripts, Table Partitioning

Verify next month partitions in all databases

I have databases with partitioned tables in some databases. I occasionally had to verify that next month partition is there in each database. It required to check in each database one by one. To improve the process i created a script that parses the databases with partitioned tables one by one and checks either partition for next month data is there or not.

Specifications

My created script is specific to scenario and following are the specifications

  1. All partitioned tables in a DB are created on a single partition scheme. Hence there is only one partition scheme and partition function in a DB.
  2. Partitions are created with one month span. i.e. Each partition has one month data.
  3. Partitions are created with right boundary (less than)
  4.  Script checks either partition for next months data is added or not

Script

Output

Output will be in following format.

Verify next month partitions in all databases

 

  • Aug 25 / 2017
  • 0
Columnstore Indexes, T-SQL Scripts, T-SQL Tips and Tricks

Row count for all tables with clustered columnstore index in a database

Consider a scenario that you require to get number of rows for tables in a database with clustered column store index (CCI). It would require to access three tables to get the required info. Following script will get the row count for all tables with CCI

  • Mar 09 / 2017
  • 0
Database Migrations, dbDigger, T-SQL Scripts

Code snippets for database migration task

Following are some code snippets that i used during a database migration task from one server to another. Backup restore method is used in this migration task

DB list on source server

Get the database list on source server that are required to migrate

Note DB properties

Note the important database properties on source server so that we may verify these on destination server after migration is complete.

Size of DBs

Get size of all databases that are to be migrated. It would help to estimate the disk capacity on destination and also the time estimate for backup and restore process.

Get which DBs are in Avaiability Group

Set DBs to READ ONLY and Restricted mode on source server

Before backing up the databases we will do following operations for each DB. As here i have databases in AG so i also included command to set DB out of AG so that i may put it to READ ONLY mode next.

Backup Databases

We will take backup of all databases through following script. Go through the option used for backup and also put a valid path. Backup file name will be generated automatically.

 

Get restore command for each DB one by one

Put DB name in first line of script one by one and get the restore commands for last full backup of that specific database

 

Set databases to READ WRITE mode on destination server

 

Fix and verify the orphan users

 

Update the DB properties

Following scripts will generate commands to fix the specific database property.

 

 

  • Oct 01 / 2014
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, SQL Server IO, T-SQL Scripts

Get number of reads and writes for each database on SQL Server

We can get the number of read and write operations for each database on our server. This information id being fetched by a DMV so analysis data depends upon up time of server.

Pages:1234567...12
Consult us to explore the Databases. Contact us