How to Backup MYSQL Databases

REM Export all databases names to file
mysql -h your-server-ip -u user-name -ppassword information_schema --skip-column-names --execute="SHOW DATABASES;" > databases.tmp
echo "Start!"
REM Look over database names

REM Outter loop to get table names

for /F %%A in (databases.tmp) do (
   echo "Exporting table Names for %%A"
   mysql -h your-server-ip -u user-name -ppassword %%A --skip-column-names --execute="SHOW TABLES;" >"%%A_tables.tmp"

   mkdir %%A


#### nested loop to create table name files per table
   for /F %%B in (%%A_tables.tmp) do (
   echo "Exporting data for %%B of %%A"
   mysqldump -h your-server-ip -u user-name -ppassword %%A %%B> "%%A\%%A_%%B.sql"
   )
)

del *.tmp

 

How to Backup Microsoft SQL Server (MSSQL) Databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
 
-- 2. Setting the backup path

SET @path = 'X:\MSSQLDB\Backup\'  

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- ignore system databases

--3. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

  -- 4. Defining the filename
    SET @fileName = @path + @name + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName 

    FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

 

How to Create Multiple SQL Server Instances on Same Server

When you install SQL Server, the installation creates an instance (DEFAULT OR SQLEXPRESS) of SQL Server. In my case I have created instanace named DEVSQL.

Some times we do need to create multiple instances based on requirements. To create more instances, start the setup again. The following screen appears when setup starts for the SQL Server 2012.

Click “New SQL Server stand-alone installation or add features to an existing installation”, the following screen appears:

Installation Start

As you can see DEVSQL instance already exists, it was created by the first installation, now select “New installation or add shared features”. Then click Next and accept the License Agreement.

Then select features you want to install for the new instance.

After that the installation offer you to specify the name of your new instance (MYTEST was chosen in this example):

Click Next to create the new instance and perform the remaining setup steps.

Now you are done!

 

How to install Koha – A Library Software on Ubuntu

Step 1:
Execute the script by giving the following command:

#!/bin/bash

#export HTTP_PROXY_HOST=10.3.100.207
#export HTTP_PROXY_PORT=8080

wget -O- http://debian.koha-community.org/koha/gpg.asc | sudo apt-key add –
echo deb http://debian.koha-community.org/koha stable main | sudo tee /etc/apt/sources.list.d/koha.list

sudo apt-get update
sudo apt-get install libgd-perl
sudo apt-get upgrade
sudo apt-get install koha-common

Koha installation gives error related with apache2-mpm-itk issue.
The initial configuration makes necessary enable / disable of the modules and does the initial configurations.

Step 2:
Execute the script by giving the following command:
############### Initial Configuration ####################

sudo a2dismod mpm_event
sudo a2enmod mpm_prefork
sudo service apache2 restart
sudo apt-get install -f

Step 3:
Koha Server configuration
In this step, we need to edit network information like domain name and port numbers.

sudo gedit /etc/koha/koha-sites.conf

Here we have to change port number of Koha staff client to 8080.
Find following line in the file and make changes.

INTRAPORT=”8080″

Step 4:
Adding ports

We have assigned 8080 port for Koha staff client and 80 for OPAC.
Open following file and add new port.

sudo gedit /etc/apache2/ports.conf

Copy paste following line below Listen 80

Listen 8080

Restart Apache,

sudo service apache2 restart

Step 5:
Apache module enable is done her executing the script

sudo a2enmod rewrite
sudo a2enmod cgi
sudo service apache2 restart

Step 6:
Install MySQL server and create the koha database. Here we have named the database as Koha2018.
Keep the default settings for MySQL server and when it asks for password give it as Koha2018. When the process asks to change the root password press ‘n’ then keep on pressing ‘enter’ for default values.

sudo apt-get install mysql-server
sudo koha-create –create-db Koha2018
sudo mysql_secure_installation

Step 7:
Most of the versions of Koha (3.x, 16.05 to 17.11) are not compatible with this default MYSQL configuration.
To bypasss the problem you can edit the configuration file (certainly /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf) and force the SQL modes Koha supports:

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After save the changes and restart mysql.

Step 8:
Finally enable the koha site by executing the script
sudo a2dissite 000-default
sudo a2enmod deflate
sudo a2ensite Koha2018
sudo service apache2 restart

Step 9:
KOHA Web Installation URL – Open the Koha staff using the link:
http://localhost:8080
This will show the login user name and the pass word

For credentials execute the script:

echo “Admin user name : koha_osslm”;
sudo xmlstarlet sel -t -v ‘yazgfs/config/pass’ /etc/koha/sites/Koha2018/koha-conf.xml
echo ” “;

Enjoy:
Browse to http://localhost:8080 – for admin login
Browse to http://localhost:80 – for OPAC login

Get last and current quarter in javascript

function getQuarter(id) {
var d = new Date();
var quarter = Math.floor((d.getMonth() / 3));

switch (id) {
case "Current":
var firstDate = new Date(d.getFullYear(), quarter * 3, 1);
var endDate = new Date(firstDate.getFullYear(), firstDate.getMonth() + 3, 0);
break;
case "Previous":
var firstDate = new Date(d.getFullYear(), quarter * 3 - 3, 1);
var endDate = new Date(firstDate.getFullYear(), firstDate.getMonth() + 3, 0);
break;
}
}

 

How to get list of all always encrypted columns in SQL Server

We do have “Always encrypted” feature in SQL 2016 and later versions, from this feature we can encrypt the column data instead of encrypting whole database.  Sensitive data like credit card numbers, SSN.
We need to prepare a list of encrypted columns at some where to track for future.
Without tracking anywhere in the documents we can run the simple SQL query which returns all the columns in respective tables and encryption type.

SELECT
t.name AS TableName,
c.name AS ColumnName,
k.name AS KeyName,
c.encryption_type_desc,
c.encryption_algorithm_name
FROM sys.columns c
INNER JOIN sys.column_encryption_keys k ON c.column_encryption_key_id = k.column_encryption_key_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE encryption_type IS NOT NULL

 

 

SQL Server – List all the Constraints by Table or by Column Name

List all Constraints of the Database:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
OR
SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

List Constraints  on table column:

 Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name]
From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj]
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
WHERE Tab.[Name] ='Employee' AND Col.[Name]='DOB'
order by Col.[Name]