Search and Restore .bak File [BULK RESTORE], SQL Server

You can say this process as BULK Restore of .bak file.

Using the below script we can search and restore bak files; Just we need to follow some pattern.

Make sure the .bak file names start with DB name and some pattern.

Below script will search the bak file from disk/provided path and restore the file.

Follow the below steps to prepare the environment.

1. Create a Table [dbo].[dbNames] in [master] database.

CREATE TABLE [dbo].[dbNames](  [ID] [int] IDENTITY(1,1) NOT NULL,  [DBNAME] [nvarchar](max) NULL,  [DBFILENAME] [nvarchar](max) NULL,  [DBFILELOCATION] [nvarchar](max) NULL,  [Comment] [nvarchar](500) NULL,  CONSTRAINT [PK_dbNames] PRIMARY KEY CLUSTERED ([ID] ASC)  )

2. Insert Database names which need to restore and leave [Comment] column blank

INSERT INTO [dbo].[dbNames] ([DBNAME], [DBFILENAME], [DBFILELOCATION]) values ('Test_Database','FileName','FileLocation') Example SQL select d.name,m.name,m.physical_name from sys.databases d JOIN sys.master_files m ON d.database_id = m.database_id where d.name not in ( 'master','tempdb','model','msdb','DBAdmin')

3. Make sure all .bak files name start with the Database name and some static words ie. "_Backup_2019"

Test_Database_Backup_20190131_01.bak
Test_Database2_Backup_20190131_01.bak

4. Now Adjust the below Script and Run. wow ... all databases are ready to use!!

declare @dbName varchar(MAX) ='' declare @fileName varchar(MAX) declare dbNamecursor cursor for select distinct DBNAME from dbNames Where Comment is null DECLARE @DataPath nvarchar(500), @DBFilePath nvarchar(500), @DBLogPath nvarchar(500) SET @DataPath = '<BAK file location>' SET @DBFilePath = '<DB file (.mdf) location>\' SET @DBLogPath = '<DB LOG file (.ldf) location>\' DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT,IS_FILE bit) INSERT INTO @DirTree(subdirectory, depth,IS_FILE) EXEC master.sys.xp_dirtree @DataPath,0,1 declare @createSQL varchar(MAX) ='' declare @createMOVESQL varchar(MAX) ='' OPEN dbNamecursor FETCH NEXT FROM dbNamecursor INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN set @fileName = null select @fileName=a.subdirectory from @DirTree a where a.subdirectory like @dbName+'_Backup_FULL_20190924%.bak' SET @createSQL = '' SET @createMOVESQL='' if @fileName is not null begin print char(10)+'@dbName = '+@dbName+' --> @fileName = '+@fileName set @createSQL = 'restore database ['+@dbName+ '] from disk = '''+@DataPath+'\'+@fileName+''' ' select @createMOVESQL = @createMOVESQL + ' , MOVE '''+s.DBFILENAME+''' TO '''+case when s.DBFILELOCATION like '%ldf' then @DBLogPath+DBNAME+'_log' else @DBFilePath+DBNAME end+'_'+ cast ( ROW_NUMBER() over (order by s.DBFILENAME) as varchar) +right(s.DBFILELOCATION,4)+''' ' from dbNames s where  DBNAME=@dbName set @createSQL= @createSQL+ ' WITH '+right(@createMOVESQL,len(@createMOVESQL)-2) print @createSQL exec(@createSQL) update dbNames set Comment='Done' where DBNAME=@dbName end else begin print char(10)+'@dbName = '+@dbName+' --> @fileName = NOT FOUND' update dbNames set Comment='FILE NOT FOUND' where DBNAME=@dbName end FETCH NEXT FROM dbNamecursor INTO @dbName END CLOSE dbNamecursor; DEALLOCATE dbNamecursor;

Control and Loop Statements in R

Control and Loop Statements in R.

There are 2 types of Control/Conditional statements and 3 types of Loop available in R. see below

See Also: Introduction to R/ Basic functions of R

Conditional Statements
1. if ... else
2. ifelse() Function

Loop Statements
1. for loop
2. while loop
3. repeat loop

Related Statements/Topics
1. break
2. next
3. logical expression

Conditional Statements
1. if...else Like other programming languages, the if...else statement is the same in R

NOTE: else if/ else keyword should be placed in the next of closing } of the earlier block.

if(logic_expression) {
stmt.
} else if (logic_expression) {
stmt.
} else {
stmt
}

Example 1
x <- 5
if(x>10) {
print("grater than 10")
}

Example 2
x <- 5
if(x>10) {
print("grater than 10")
} else {
print("less than 10")
}

Example 3
x <- 5
if(x>10) {
print("grater than 10")
} else if (x>5 & x<10) {
print("less than 10 BUT grater than 5")
} else {
print("less than 5")
}

2. ifelse() is a function to use with vector AND the return would be a vector also.

Example 1
x = 1:10
ifelse( x %% 2 == 0 , "even", "odd")
OUTPUT: [1] "odd"  "even" "odd"  "even" "odd"  "even" "odd"  "even" "odd"  "even"

Loop Statements
1. for loop: the basic is the same as other languages BUT the syntex is different

for ( val in vector/sequence )
{
    statement
}

Example 1
v = c(1,2,3,4,5)
for ( x in v )
{
    print (x)
}

Example 2: Fibonacci Series upto 10th number
f = c(1,1)
for (x in 1:8) {
f <- c(f, sum( tail(f,2) ) )
}
print(f)
OUTPUT: [1]   1   1   2   3   5   8  13  21  34  55  89 144

Example 3: Nested For Loop - a simple pyramid of numbers
for(x in 1:9){
p = NULL
for(y in 1:x){
p = c(p, y)
}
print(p)
}
OUTPUT:
[1] 1
[1] 1 2
[1] 1 2 3
[1] 1 2 3 4
[1] 1 2 3 4 5
[1] 1 2 3 4 5 6
[1] 1 2 3 4 5 6 7
[1] 1 2 3 4 5 6 7 8
[1] 1 2 3 4 5 6 7 8 9

2. while loop: basic is also same for this one.

while( logic_expression )
{
    statement
}

Example 1
x=1
y=10
while(x<y)
{
  print(x)
  x = x + 1
}

Example 2 : Create a vector with the sum of current value from the first value [ x=1⅀x=n ].
v=1:10
x=1
sumV = NULL
while ( x <= length(v) )
{
sumV = c(sumV , sum( head(v,x) ))
x = x + 1
}
print(v)
print(sumV)

OUTPUT:

v ==>        [1]  1  2  3  4  5  6  7  8  9 10

sumV ==> [1]  1  3  6 10 15 21 28 36 45 55

3. repeat loop: it just repeats the block until finding a break statement.

repeat {
statement
}

Example 1:
r = 0
count = 1
repeat {
r = c(r,count)
if(count == 5) { break }
count = count + 1
}
print(r)
OUTPUT: [1] 0 1 2 3 4 5

Related Statements/Topics
1. break: this is like other languages. just break a loop to continue and goto the end of the loop.
2. next: this is like continue of other languages. just continue the next iteration by skipping the following statements.
Example 1:
r=NULL
for (x in 1:20) {
if(x == 5) { next}
  if(x == 10) { break}
r = c(r,x)
count = count + 1
}
print(r)
OUTPUT:
In output 5 is missing and loop stopes at 10!
[1] 1 2 3 4 6 7 8 9

3. logical expression: this is also like other languages.
R supports the combination of logical operators..
x > 2 & y <5
x > 2 && y <5
x > 2 | y <5
x > 2 || y <5
for vector:
xV  & yV ==> will check all the elements and return a logical vector
xV  && yV ==> will check the first elements only and return a logical value
xV  || yV ==> will check the first elements only and return a logical value
all logical operators:
&, &&, | ,||, ! (NOT)

Basic Functions to Start R Programming

To start programming in R, we have to understand the below topics first ...

1. Variables/Objects with Data Types: like other programming languages, R also has Numeric (int & float), Caracter (String) and Logical (Boolean) data types. A variable can hold any of this data type data. An object can be any from below.

2. Vectors (one-dimensional array): can hold numeric, character or logical values. The elements in a vector all have the same data type.

3. Factors (Enum): Advanced level of Vactor with category information. Your data can be anything but the category should be limited. Like sex information of a survey, there are two category Male & Female, where the actual Vactor has all data.

4. Matrices (two-dimensional array): can hold numeric, character or logical values. The elements in a matrix all have the same data type.

5. Data frames (two-dimensional Objects): can hold numeric, character or logical values[Variables, Vectors/Factors & Matrices]. Within a column, all elements have the same data type, but different columns can be of a different data type.

6. Lists (Vactor of Objects): This is a Vactor of Objects. Where Objects can be any of Vectors, Factors, Matrices and Data Frames.

Examples:

1. Variable Assignment:

i <- 10
f <- 15.62
c <- "Azam"
l <- TRUE [R is Case Sencitive]

2. Vector (one-dimensional array):

#Define a Vector
idsV <- c(1,2,3,4,5) [Numaric Vector]
idsV <- 1:5 [Numaric Vector]
namesV <- c("Saiful","Azam") [Caracter Vector]
logicalVac <- c(TRUE, FALSE, FALSE, TRUE) [Logical Vector]

# Naming a Vector
namesV <- c("Saiful","Azam")
names(namesV) <- c("FName","LName")
namesV <- c(FName="Saiful",LName="Azam")

#Select elements from a Vector [R is 1-index based not 0-indexBased]
x <- c(9, 5, 6, 3, 7)
x[1,5,3]   # Output -- 9 7 6
x[2:4]  # Output -- 5 6 3
logicV <- x>5  # logicV = [TRUE, FALSE, TRUE, FALSE, TRUE]
x[logicV]  #Output -- 9,6,7
x[!logicV] #Output -- 5,3

#Add/ Delete item to an existing Vector
namesV <- c("Saiful","Azam")
namesV <- c(namesV, "Sohag")
x=1:15
x[! (x>3 & x<10)]     # 4-9 will be deleted
x[-(3:10)]      # 3-10 will be deleted
x[-(c(3,10))]  # 3 & 10 will be deleted

# Calculation with Vector
x + 5  # add 5 to all elements
x * 5  # Multiply 5 with all elements
sum(x)  # sum all elements
mean(x)  # average of all elements

# Order a Vector using a Col
v1 <- c(10,50,15,66,11,5,90)
ord <- order (v1 , decreasing = FALSE)
v1 [ ord]
v1[order (v1)]

3. Factors (Enum):

#Declare a Factor
sex_vector <- c("Male", "Female", "Female", "Male", "Male")
# Convert sex_vector to a factor
factor_sex_vector <- factor(sex_vector)
#output
[1] Male   Female Female Male   Male  
Levels: Female Male

#Define a Order within Categories in Factor
temperature_vector <- c("High", "Low", "High","Low", "Medium")
factor_temperature_vector <- factor(temperature_vector, order = TRUE, levels = c("Low", "Medium", "High"))
#output 
[1] High   Low    High   Low    Medium
Levels: Low < Medium < High

#Compare tow elements of a Factor
t1<- temperature_vector [1]
t2 <- temperature_vector [2]
t1<t2     #output -- FALSE

#Define Levels for elements
survey_vector <- c("M", "F", "F", "M", "M")
factor_survey_vector <- factor(survey_vector)
levels(factor_survey_vector) <- c("Female", "Male")
factor_survey_vector
#output
[1] Male   Female Female Male   Male  
Levels: Female Male

summary(factor_survey_vector)
#Output
Female   Male 
     2      3

4. Matrices (two-dimensional array):

#Create Matrix
mx<- matrix(1:9,nrow=3, byrow=TRUE)

v1<- 1:3
v2<- 4:6
v3<- 7:9
vc <- c(v1, v2 ,v3)
mx <- matrix(vc ,nrow=3, byrow=TRUE)

# Naming (Row, Col) a Matrix
rownames(mx) <- c("a","b","c")
colnames(mx) <- c("x","y","z")
mx <- matrix(vc , nrow = 3, byrow = TRUE, dimnames = list(c("a","b","c"),  c("x","y","z")))

# Add/ Delete Row & Col in matrix
vAdd <- 10:12
cbind(mx, d = vAdd)
rbind(mx, w = vAdd)

mx[ , -2 ]  # Delete 2nd col
mx[ -2, ]  # Delete 2nd row

# Select elements from Matrix
mx[ 1, ]   # 1st row
mx[ ,1 ]   # 1st col
mx[ 1:3, 3 ]  # rows 1 to 3 and 3rd col
mx[ 1, 1:3 ]  # 1st row and 1-3 cols
mx [ , "a"]   # get all row for col "a"

#Get Row/Col Sums/Means
rowSums(mx)
rowMeans(mx)
colSums(mx)
colMeans(mx)

# Calculation with Matrix
mx * 5  # Multiply all elements with 5
mx / 5   # multiply all elements with 5
mx %*% mx  # Actual Matrix multiplication

5. Data frames (two-dimensional Objects):

# Define Data-Frame
v1 <- 1:10
v2 <- 11:20
v3 <- 21:30

df <- data.frame(v1, v2, v3)
df <- data.frame(col1=v1, col2=v2, col3=v3)  # with diclared column name

# Add/ Delete Col into Data Frame
v4 <- 50:60
df <- data.frame (df , col4 = v4)   # add new column col4
df <- df [ , -3 ]
df [ , !(names(df) %in% c("col3")) ]
df [ , !(names(df) == "col3") ]

# Select elements from Data Frames
df[ 1, 2 ]
df [ 1:5, 2:6 ]
df [ , 1:10]
df [ 1:100, ]
df [1:5,"col1"]
df$col3# get all data of col name as vector
df [ df$col3 > 3, ]   # all rows where the logical vector is TRUE
subset(df , subset= col2>15)    #logicalCondition  like x > 2

# Order a Data Frame using a Col
positions <-  order (df$col2 , decreasing = FALSE)
df [ positions, ]

6. Lists (Vactor of Objects):

#Define a List
my_vector <- 1:10
my_matrix <- matrix(1:9, ncol = 3)
my_df <- mtcars[1:10,]
my_list <- list(my_vector,my_matrix, my_df)
my_list <- list(vec=my_vector, mat=my_matrix, df=my_df)

# Add/ Delete Object in List
my_list <-  c(my_list , year = 2019)   #Add
my_list [[2]] <- NULL   #DEL
my_list [["vec"]] <- NULL    #DEL

#Select elements from a List
my_list [[ 1 ]]
my_list [[ "vec" ]]
my_list [[ "vec" ]][2]
my_list$vec
my_list$vec[2]

Add additional partition to an existing partitioned Table/Index, SQL Server

To add an additional partition to an existing partitioned table, we have to follow below steps...

See Also - Create a Partition & use in Table/Index (Existing/Not)

NOTE: it is not mandatory to create new/separate file & file group to add an additional partition.

1. ALTER PARTITION SCHEME
2. ALTER PARTITION FUNCTION

If you think to use a new File Group with a new File, Create a File Group and File first. BUT this is Optional.

Create a File Group and a new File to use for the New Partition.

File Group:
ALTER DATABASE Partition_Test ADD FILEGROUP FG_FII_2019_01;

File to this FileGRoup:
ALTER DATABASE Partition_Test ADD FILE
(
    NAME = F_FII_2019_01,
    FILENAME = 'C:\MSSQL\Data\Partition_Test \F_FII_2019_01.ndf',
    SIZE = 1048576KB,
    FILEGROWTH = 1048576KB
) TO FILEGROUP FG_FII_2019_01;

Now let ALTER the partition ... [Asume that, our partition is based on Date/Period column]

1. ALTER PARTITION SCHEME: we have to mention the file group name which will store this partition data

ALTER PARTITION SCHEME PS_Partition_Test
NEXT USED [FG_FII_2019_01];

2. ALTER PARTITION FUNCTION: in the function definition, we have to put the range value which separates the data for this partition.

say, we will store 2019-January and later data in this partition: [asumming the actual definition was with RIGHT RANGE of Date]

ALTER PARTITION FUNCTION [PF_Partition_Test] ()
SPLIT RANGE (N'2019-01-01T00:00:00.000');


So, From now, all data with date 2019-01-01 and future will store the file group FG_FII_2019_01 and use the new partition range for the query.

Create Partition & use in Table/Index (Existing/Not), SQL Server

To use the benefit of partition, we have to create a partition scheme, partition function and then attached it to the Tabel/Index.

See also - Add additional partition to an existing partitioned Table/Index.

so follow the below example...

0. [Optional] Create FileGroup and File to use in Partition Scheme
1. Create a Partition Function.

2. Create a Partition Scheme.

3. Create a New Table using the Partition Scheme. OR

4. Existing Table: Create a Clustered Index using the Partition Scheme.

First of all, let's discuss our data/table for what we will create a Partition.

Table Definition: TestTable

ColumnName	Type
Name	nvarchar(500)
Designation	nvarchar(500)
JoiningDate	datetime

Now, we will create the partition for the Column JoiningDate

0. [Optional] Create FileGroup and File to use in Partition Scheme

File Group:
ALTER DATABASE Partition_Test ADD FILEGROUP FG_FII_2019_01;

File to this FileGRoup:
ALTER DATABASE Partition_Test ADD FILE
(
    NAME = F_FII_2019_01,
    FILENAME = 'C:\MSSQL\Data\Partition_Test \F_FII_2019_01.ndf',
    SIZE = 1048576KB,
    FILEGROWTH = 1048576KB
) TO FILEGROUP FG_FII_2019_01;

1. Create a Partition Function.

below partition function will separate data by three partitions. look at the last one, from 2019-03 and later all data will use the last range.

CREATE PARTITION FUNCTION [PF_TestTable](datetime)
AS RANGE RIGHT FOR VALUES
(
N'2019-01-01T00:00:00.000',
N'2019-02-01T00:00:00.000',
N'2019-03-01T00:00:00.000'
)

2. Create a Partition scheme.

NOTE: it is not mandatory to use different filegroup for a different range. If you have the different disk for I/O, then you can create three different files & filegroup on different disks. Whatever the case, you can use different filegroup for respective ranges or can use the same filegroup for all ranges.

CREATE PARTITION SCHEME [PS_TestTable] 

AS PARTITION [PF_TestTable] TO
(
[FG_FII_2019_01],
[FG_FII_2019_01],
[FG_FII_2019_01]
)

3. Create a New Table using the Partition Scheme

CREATE TABLE [TestTable] (Name nvarchar(500), Designation nvarchar(500), JoiningDate datetime)
ON [PS_TestTable] (JoiningDate)

4. Existing Table [TestTable]: Create a Clustered Index using the Partition Scheme.

NOTE: Delete the existing Clustered Index, if any.

CREATE CLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable]
(
[JoiningDate] ASC
) ON [PS_TestTable] (JoiningDate)

For more Information: Click Here.