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.
Following is the script to generate restore statements of multiple databases for their last full backups.
Exec and sp_executeSQL both are used for executing the dynamic sql statements. There are some differences between the both
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 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.
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.
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.
My created script is specific to scenario and following are the specifications
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.
Partitions are created with one month span. i.e. Each partition has one month data.
Partitions are created with right boundary (less than)
Script checks either partition for next months data is added or not
SET NOCOUNT ON
Print'Report for partitioning status on '+@@servername
--Create tempDB tohold DB names with partitioned tables
CREATE TABLE#DBs (DBName VARCHAR(50))
--Insert DB names with partitioned tables
INSERT INTO#DBs (DBName)
'use ?; if exists (select top 1 * from ?.[sys].[partitions] where partition_number >1) select db_name()'
--Declarecursor forchecking DBs one by one
--Prepare sql forlast partition check
if (SELECT convert(bigint, max(r.value) )
FROM [sys].[partition_range_values] r inner join [sys].[partition_functions] f
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