Category: Uncategorized

Backup of MSSQL Database in Express Edition

Backup of databases in SQL_EXPRESS Edition

As we all know there is no inbuild functionality for backup in like available in standard and Enterprise Edition. Please find some different methods for Backup

Method-1
     1>  Create one stored procedure using below query  that use to take backup
[Stored Procedure Name =sp_BackupDatabases]
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: Backup Databases for SQLExpress
— Parameter1: databaseName
— Parameter2: backupType F=full, D=differential, L=log
— Parameter3: backup file location
— =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
AS
       SET NOCOUNT ON;
          
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
          
             — Pick out only databases which are online in case ALL databases are chosen to be backed up
             — If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
          
            — Filter out databases which do not need to backed up
            IF @backupType=’F’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’D’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’L’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
          
            — Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                 
                      
            — Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
      WHILE @Loop IS NOT NULL
      BEGIN
— Database Names have to be in [dbname] format since some have – or _ in their name
      SET @DBNAME = ‘[‘+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+’]’
— Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),’/’,”) + ‘_’ +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”) 
— Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = ‘F’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_FULL_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘D’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_DIFF_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘L’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_LOG_’+ @dateTime+ ‘.TRN’
— Provide the backup a name for storing in the media
      IF @backupType = ‘F’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ full backup for ‘+ @dateTime
      IF @backupType = ‘D’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ differential backup for ‘+ @dateTime
      IF @backupType = ‘L’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ log backup for ‘+ @dateTime
— Generate the dynamic SQL command to be executed
       IF @backupType = ‘F’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
                  END
       IF @backupType = ‘D’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH DIFFERENTIAL, INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
       IF @backupType = ‘L’
                  BEGIN
               SET @sqlCommand = ‘BACKUP LOG ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
— Execute the generated SQL command
       EXEC(@sqlCommand)
— Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 END
          2>  Open command prompt and run below command for take backup in one folder
C:\>sqlcmd -S .\SQL2012 -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackup\’, @backupType=’D'”
Method -2
1>  Create Folder in D:\SQL_Backup\scripts
2>  Create the TSQL script
DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
–month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
   SET @monthSTR= ‘0’ + CAST(MONTH(GETDATE()) AS CHAR(2))
–day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
   SET @daySTR=’0′ + CAST(DAY(GETDATE()) AS CHAR(2))
–hour variable
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
ELSE
   SET @hourStr= ‘0’ + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
–minute variable
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
ELSE
   SET @minStr= ‘0’ + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
–name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr
–=================================================================
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN (‘TEMPDB’)
WHILE @IDENT IS NOT NULL
BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK = ”D:\SQLBackup\’+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’
   EXEC (@SQL)
   `>@IDENT AND NAME NOT IN (‘TEMPDB’)
END
Save above file as BackupDB.sql in D:\SQL_Backup\scripts folder
Run Below command in command prompt :
sqlcmd -S.\SQL2012 -E -i”D:\scripts\SqlBakup.sql”
Note:  -S sql instance Name
3>  Create VB Script file for remove database older than 3 days save as D:\SQL_Backup\scripts\deleteBAK.vbs
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘location of the database backup files
sFolder = “E:\SQL_Backup\”

Set folder = fso.GetFolder(sFolder)
Set files = folder.Files

‘used for writing to textfile – generate report on database backups deleted
Const ForAppending = 8

‘you need to create a folder named “scripts” for ease of file management &
‘a file inside it named “LOG.txt” for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & “\scripts\LOG.txt”, ForAppending)

objFile.Write “================================================================” & VBCRLF & VBCRLF
objFile.Write ”                     DATABASE BACKUP FILE REPORT                ” & VBCRLF
objFile.Write ”                     DATE:  ” &    FormatDateTime(Now(),1)   & “” & VBCRLF
objFile.Write ”                     TIME:  ” &    FormatDateTime(Now(),3)   & “” & VBCRLF & VBCRLF
objFile.Write “================================================================” & VBCRLF

‘iterate thru each of the files in the database backup folder
For Each itemFiles In files
   ‘retrieve complete path of file for the DeleteFile method and to extract
        ‘file extension using the GetExtensionName method
   a=sFolder & itemFiles.Name

   ‘retrieve file extension
   b = fso.GetExtensionName(a)
       ‘check if the file extension is BAK
       If uCase(b)=”BAK” Then

           ‘check if the database backups are older than 3 days
           If DateDiff(“d”,itemFiles.DateCreated,Now()) >= 3 Then

               ‘Delete any old BACKUP files to cleanup folder
               fso.DeleteFile a
               objFile.WriteLine “BACKUP FILE DELETED: ” & a
           End If
       End If
Next

objFile.WriteLine “================================================================” & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing 

4>   Create the batch file that will call the TSQL script and the VBScript file
D:\SQL_Backup\scripts\databaseBackup.cmd
REM Run TSQL Script to backup databases
sqlcmd -S-E -i”E:\SQL_Backup\scripts\backupDB.sql”
REM Run database backup cleanup script
E:\SQL_Backup\scripts\deleteBAK.vbs

5>  Create a task in Windows Task Scheduler
Create a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel -> Scheduled Tasks or under Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks.
Since we are using Windows authentication to run the TSQL script, use a Windows account that is a member of the db_backupoperator role of all the databases
  • Launch “Scheduled Tasks”
  • Click on Add Scheduled Task
  • Browse to the “E:\SQL_Backup\scripts” folder and select databaseBackup.cmd
  • Pick the frequency and time for the backups to run
  • Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases
Method-3
1>  Create T-Sql Script for backup as follow:
———————————————————DB_Name : Offline_DB——————————————————————————
DECLARE @pathName NVARCHAR(512)
SET @pathName = ‘D:\SQLBackup\Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’Offline_DB’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : MPOS_Offline_DB————————————————————————-
SET @pathName = ‘D:\SQLBackup\MPOS_Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [MPOS_Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012TempDB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012TempDB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
2>  Save as BackupDB.sql and save it on D:\Scripts
3>  To Run BackupDB.sql file run below command to check whether it was working or not
Open cmd – Aa a Administrator
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
Note: database backup path define in .sql script
4>  If you want to compress backup then create batch file in D:\scripts folder also install 7Zip application. Copy 7z.exe in D:\scripts
5>  Create cmd or bat file as follow:
@echo Take Backup of databases
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
@echo Compress old databses
D:\scripts\7z.exe a -tzip D:\SQLBackup\MPOS_Offline_DB_%date%.zip  D:\SQLBackup\MPOS_Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\Offline_DB_%date%.zip  D:\SQLBackup\Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012_%date%.zip  D:\SQLBackup\ReportServer$SQL2012_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012TempDB_%date%.zip  D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak
@echo Remove Backupfiles
del D:\SQLBackup\MPOS_Offline_DB_*.bak
del D:\SQLBackup\Offline_DB_*.bak
del D:\SQLBackup\ReportServer$SQL2012_*.bak

del D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak

Backup of MSSQL Database in Express Edition

Backup of databases in SQL_EXPRESS Edition

As we all know there is no inbuild functionality for backup in like available in standard and Enterprise Edition. Please find some different methods for Backup

Method-1
     1>  Create one stored procedure using below query  that use to take backup
[Stored Procedure Name =sp_BackupDatabases]
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: Backup Databases for SQLExpress
— Parameter1: databaseName
— Parameter2: backupType F=full, D=differential, L=log
— Parameter3: backup file location
— =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases] 
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
AS
       SET NOCOUNT ON;
          
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
          
             — Pick out only databases which are online in case ALL databases are chosen to be backed up
             — If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
          
            — Filter out databases which do not need to backed up
            IF @backupType=’F’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’D’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE IF @backupType=’L’
                  BEGIN
                  DELETE @DBs where DBNAME IN (‘tempdb’,’Northwind’,’pubs’,’master’,’AdventureWorks’)
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
          
            — Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                 
                      
            — Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
      WHILE @Loop IS NOT NULL
      BEGIN
— Database Names have to be in [dbname] format since some have – or _ in their name
      SET @DBNAME = ‘[‘+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+’]’
— Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),’/’,”) + ‘_’ +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”) 
— Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = ‘F’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_FULL_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘D’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_DIFF_’+ @dateTime+ ‘.BAK’
      ELSE IF @backupType = ‘L’
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, ‘[‘,”),’]’,”)+ ‘_LOG_’+ @dateTime+ ‘.TRN’
— Provide the backup a name for storing in the media
      IF @backupType = ‘F’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ full backup for ‘+ @dateTime
      IF @backupType = ‘D’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ differential backup for ‘+ @dateTime
      IF @backupType = ‘L’
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[‘,”),’]’,”) +’ log backup for ‘+ @dateTime
— Generate the dynamic SQL command to be executed
       IF @backupType = ‘F’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’
                  END
       IF @backupType = ‘D’
                  BEGIN
               SET @sqlCommand = ‘BACKUP DATABASE ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH DIFFERENTIAL, INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
       IF @backupType = ‘L’
                  BEGIN
               SET @sqlCommand = ‘BACKUP LOG ‘ +@DBNAME+  ‘ TO DISK = ”’+@BackupFile+ ”’ WITH INIT, NAME= ”’ +@BackupName+”’, NOSKIP, NOFORMAT’       
                  END
— Execute the generated SQL command
       EXEC(@sqlCommand)
— Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 END
          2>  Open command prompt and run below command for take backup in one folder
C:\>sqlcmd -S .\SQL2012 -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackup\’, @backupType=’D'”
Method -2
1>  Create Folder in D:\SQL_Backup\scripts
2>  Create the TSQL script
DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
–month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
   SET @monthSTR= ‘0’ + CAST(MONTH(GETDATE()) AS CHAR(2))
–day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
   SET @daySTR=’0′ + CAST(DAY(GETDATE()) AS CHAR(2))
–hour variable
IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
ELSE
   SET @hourStr= ‘0’ + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))
–minute variable
IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
ELSE
   SET @minStr= ‘0’ + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))
–name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr
–=================================================================
DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN (‘TEMPDB’)
WHILE @IDENT IS NOT NULL
BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = ‘BACKUP DATABASE ‘+@DBNAME+’ TO DISK = ”D:\SQLBackup\’+@DBNAME+’_db_’ + @dateString +’.BAK” WITH INIT’
   EXEC (@SQL)
   `>@IDENT AND NAME NOT IN (‘TEMPDB’)
END
Save above file as BackupDB.sql in D:\SQL_Backup\scripts folder
Run Below command in command prompt :
sqlcmd -S.\SQL2012 -E -i”D:\scripts\SqlBakup.sql”
Note:  -S sql instance Name
3>  Create VB Script file for remove database older than 3 days save as D:\SQL_Backup\scripts\deleteBAK.vbs
On Error Resume Next
Dim fso, folder, files, sFolder, sFolderTarget
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘location of the database backup files
sFolder = “E:\SQL_Backup\”

Set folder = fso.GetFolder(sFolder)
Set files = folder.Files

‘used for writing to textfile – generate report on database backups deleted
Const ForAppending = 8

‘you need to create a folder named “scripts” for ease of file management &
‘a file inside it named “LOG.txt” for delete activity logging
Set objFile = fso.OpenTextFile(sFolder & “\scripts\LOG.txt”, ForAppending)

objFile.Write “================================================================” & VBCRLF & VBCRLF
objFile.Write ”                     DATABASE BACKUP FILE REPORT                ” & VBCRLF
objFile.Write ”                     DATE:  ” &    FormatDateTime(Now(),1)   & “” & VBCRLF
objFile.Write ”                     TIME:  ” &    FormatDateTime(Now(),3)   & “” & VBCRLF & VBCRLF
objFile.Write “================================================================” & VBCRLF

‘iterate thru each of the files in the database backup folder
For Each itemFiles In files
   ‘retrieve complete path of file for the DeleteFile method and to extract
        ‘file extension using the GetExtensionName method
   a=sFolder & itemFiles.Name

   ‘retrieve file extension
   b = fso.GetExtensionName(a)
       ‘check if the file extension is BAK
       If uCase(b)=”BAK” Then

           ‘check if the database backups are older than 3 days
           If DateDiff(“d”,itemFiles.DateCreated,Now()) >= 3 Then

               ‘Delete any old BACKUP files to cleanup folder
               fso.DeleteFile a
               objFile.WriteLine “BACKUP FILE DELETED: ” & a
           End If
       End If
Next

objFile.WriteLine “================================================================” & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing 

4>   Create the batch file that will call the TSQL script and the VBScript file
D:\SQL_Backup\scripts\databaseBackup.cmd
REM Run TSQL Script to backup databases
sqlcmd -S-E -i”E:\SQL_Backup\scripts\backupDB.sql”
REM Run database backup cleanup script
E:\SQL_Backup\scripts\deleteBAK.vbs

5>  Create a task in Windows Task Scheduler
Create a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel -> Scheduled Tasks or under Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks.
Since we are using Windows authentication to run the TSQL script, use a Windows account that is a member of the db_backupoperator role of all the databases
  • Launch “Scheduled Tasks”
  • Click on Add Scheduled Task
  • Browse to the “E:\SQL_Backup\scripts” folder and select databaseBackup.cmd
  • Pick the frequency and time for the backups to run
  • Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases
Method-3
1>  Create T-Sql Script for backup as follow:
———————————————————DB_Name : Offline_DB——————————————————————————
DECLARE @pathName NVARCHAR(512)
SET @pathName = ‘D:\SQLBackup\Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’Offline_DB’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : MPOS_Offline_DB————————————————————————-
SET @pathName = ‘D:\SQLBackup\MPOS_Offline_DB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [MPOS_Offline_DB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
———————————————————DB_Name : ReportServer$SQL2012——————————————————————–
SET @pathName = ‘D:\SQLBackup\ReportServer$SQL2012TempDB_’ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [ReportServer$SQL2012TempDB] TO  DISK = @pathName WITH NOFORMAT, NOINIT,  NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
2>  Save as BackupDB.sql and save it on D:\Scripts
3>  To Run BackupDB.sql file run below command to check whether it was working or not
Open cmd – Aa a Administrator
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
Note: database backup path define in .sql script
4>  If you want to compress backup then create batch file in D:\scripts folder also install 7Zip application. Copy 7z.exe in D:\scripts
5>  Create cmd or bat file as follow:
@echo Take Backup of databases
sqlcmd -S .\SQL2012 -E -i”D:\scripts\SQLbackup_Newsql.sql”
@echo Compress old databses
D:\scripts\7z.exe a -tzip D:\SQLBackup\MPOS_Offline_DB_%date%.zip  D:\SQLBackup\MPOS_Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\Offline_DB_%date%.zip  D:\SQLBackup\Offline_DB_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012_%date%.zip  D:\SQLBackup\ReportServer$SQL2012_*.bak
D:\scripts\7z.exe a -tzip D:\SQLBackup\ReportServer$SQL2012TempDB_%date%.zip  D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak
@echo Remove Backupfiles
del D:\SQLBackup\MPOS_Offline_DB_*.bak
del D:\SQLBackup\Offline_DB_*.bak
del D:\SQLBackup\ReportServer$SQL2012_*.bak

del D:\SQLBackup\ReportServer$SQL2012TempDB_*.bak

MySql Basic Commands

Mysql Commands

1>    Login MySQL Console

# [mysql dir]mysql  -u username -p

2>    Create a database on the sql server.

mysql> create database [databasename];

3>    List all databases on the sql server.

mysql> show databases;

4>    Switch to a database.

mysql> use [db name];

5>    To see all the tables in the db.

mysql> show tables;

6>    To see database’s field formats.

mysql> describe [table name];

7>    To delete a db.

mysql> drop database [database name];

8>    To delete a table.

mysql> drop table [table name];

9>    Show all data in a table.

mysql> SELECT * FROM [table name];

10>Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11>Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

12>Show all records containing the name “Bob” AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;

13>Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;

14>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;

15>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;

16>Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

17>Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

18>Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

19>Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

20>Sum column.

mysql> SELECT SUM(*) FROM [table name];

21>Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

22>Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update password.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

23>Change a user’s password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

24>Change a user’s password from MySQL prompt. Login as root. Set the password. Update password.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

25>Set a root password if there is on root password.

# mysqladmin -u root password newpassword

26>Update a root password.

# mysqladmin -u root -p oldpassword newpassword

27>Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

28>Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privileges. Update privileges.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

29>To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

30>Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

31>Update database permissions/privilages.

mysql> flush privileges;

32>Delete a column.

mysql> alter table [table name] drop column [column name];

33>Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

34>Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

35>Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

36>Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

37>Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

38>Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

39>Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

40>Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

41>Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

42>Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

43>Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

44>Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

45>Reset the admin password of any tables :

#mysql -uDATABASENAME -pPASSWORD
mysql> use DATABASENAME;

mysql>UPDATE `TABLENAME` SET `PASSWORD-FILED-NAME` = MD5( 'admin' ) WHERE `USER-FIELD-NAME`.`user_name` = "admin" ;

Error Log and Slow Query Log in MySQL

Error log and Slow query log in MySQL
      
      1>    Error Log:
   ·         For Enable logs need to change in my.cnf file as follow:
      root@server# vi  /etc/mysql/my.cnf
                Or
      root@server# vi  /etc/my.cnf
   ·         Define error log with log-error in both [mysqld_safe] and [mysqld] section in my.cnf file
          [mysqld_safe]
          log-error=/var/log/mysql/error.log
          [mysqld]
          log-error=/var/log/mysql/error.log
   ·         Save file and Restart mysql service
      2>    Slow Query Log:
    ·     First thing need to check whether “Slow Query Log” is enable or not. For that access MySql and try  to execute following command.
         root@server# mysql –u root –p
         password:
         mysql> show variables like ‘%slow%’;
+———————+———————————+
| Variable_name       | Value                           |
+———————+———————————+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+———————+———————————+
·   The command result shows slow query log is currently disabled in the server.  You have add the following entries in the “/etc/mysql/my.cnf” file in-order to enable “slow query log”.  Place the entries below the section “mysqld”
[mysqld]
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
·         Check again whether the “slow query log” is enabled.
   mysql> show variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+——————+——-+

MySql Basic Commands

Mysql Commands

1>    Login MySQL Console

# [mysql dir]mysql  -u username -p

2>    Create a database on the sql server.

mysql> create database [databasename];

3>    List all databases on the sql server.

mysql> show databases;

4>    Switch to a database.

mysql> use [db name];

5>    To see all the tables in the db.

mysql> show tables;

6>    To see database’s field formats.

mysql> describe [table name];

7>    To delete a db.

mysql> drop database [database name];

8>    To delete a table.

mysql> drop table [table name];

9>    Show all data in a table.

mysql> SELECT * FROM [table name];

10>Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11>Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

12>Show all records containing the name “Bob” AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′;

13>Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by phone_number;

14>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′;

15>Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′ limit 1,5;

16>Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;

17>Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

18>Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

19>Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

20>Sum column.

mysql> SELECT SUM(*) FROM [table name];

21>Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

22>Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update password.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));
mysql> flush privileges;

23>Change a user’s password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

24>Change a user’s password from MySQL prompt. Login as root. Set the password. Update password.

# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

25>Set a root password if there is on root password.

# mysqladmin -u root password newpassword

26>Update a root password.

# mysqladmin -u root -p oldpassword newpassword

27>Allow the user “bob” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by ‘passwd’;
mysql> flush privileges;

28>Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privileges. Update privileges.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
mysql> flush privileges;
or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

29>To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

30>Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = ‘whatever’;

31>Update database permissions/privilages.

mysql> flush privileges;

32>Delete a column.

mysql> alter table [table name] drop column [column name];

33>Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

34>Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

35>Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

36>Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

37>Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

38>Load a CSV file into a table.

mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

39>Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

40>Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

41>Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

42>Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

43>Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

44>Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default ‘bato’);

45>Reset the admin password of any tables :

#mysql -uDATABASENAME -pPASSWORD
mysql> use DATABASENAME;

mysql>UPDATE `TABLENAME` SET `PASSWORD-FILED-NAME` = MD5( 'admin' ) WHERE `USER-FIELD-NAME`.`user_name` = "admin" ;

Error Log and Slow Query Log in MySQL

Error log and Slow query log in MySQL
      
      1>    Error Log:
   ·         For Enable logs need to change in my.cnf file as follow:
      root@server# vi  /etc/mysql/my.cnf
                Or
      root@server# vi  /etc/my.cnf
   ·         Define error log with log-error in both [mysqld_safe] and [mysqld] section in my.cnf file
          [mysqld_safe]
          log-error=/var/log/mysql/error.log
          [mysqld]
          log-error=/var/log/mysql/error.log
   ·         Save file and Restart mysql service
      2>    Slow Query Log:
    ·     First thing need to check whether “Slow Query Log” is enable or not. For that access MySql and try  to execute following command.
         root@server# mysql –u root –p
         password:
         mysql> show variables like ‘%slow%’;
+———————+———————————+
| Variable_name       | Value                           |
+———————+———————————+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+———————+———————————+
·   The command result shows slow query log is currently disabled in the server.  You have add the following entries in the “/etc/mysql/my.cnf” file in-order to enable “slow query log”.  Place the entries below the section “mysqld”
[mysqld]
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
·         Check again whether the “slow query log” is enabled.
   mysql> show variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+——————+——-+

Backup and Restore in MySQL

Backup and Restore MySQL Database
·        Mysql include mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT intosql-statements of the source database. To restore the database, execute the *.sql file on destination database.
Backup and Restore command line syntax
·        backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
·        restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
   1>    Backup a single database:
          # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
   2>    Backup multiple database:
               # mysqldump -u root -p[root_password] [database_name] [database_name_2] > dumpfilename.sql
   3>    Verify the dumpfilename.sql dump file contains both the database backup
         # grep -i “Current database:” /tmp/dumpfilename.sql
           Current Database: `database_name`
           Current Database: `database_name_2`
   4>    Backup all the databases:
         # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql
   5>    Backup a specific table:
         # mysqldump -u root -ptmppassword database1 accounts_contacts /tmp/database1_accounts_contacts.sql
    6>    Different mysqldump group options:
–opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
–compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks
How to Restore MySQL database
    1>    Restore a database: When you are restoring the dumpfilename.sql on a remote database, make         sure to create the database before you can perform the restore.
# mysql -u root –ptmppassword
# mysql> create database database_name;
# mysql -u root -ptmppassword database_name < /tmp/dumpfilename.sql
    2>    Backup a local database and restore to remote server using single command: if you want to         create read only database in remote computer. For that you need to create database in remote               computer and then run below command.
[local-server]# mysqldump -u root -ptmppassword database_name  | mysql  \   -u root -ptmppassword –host=remote-server -C database_name1

Backup and Restore in MySQL

Backup and Restore MySQL Database
·        Mysql include mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT intosql-statements of the source database. To restore the database, execute the *.sql file on destination database.
Backup and Restore command line syntax
·        backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
·        restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
   1>    Backup a single database:
          # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
   2>    Backup multiple database:
               # mysqldump -u root -p[root_password] [database_name] [database_name_2] > dumpfilename.sql
   3>    Verify the dumpfilename.sql dump file contains both the database backup
         # grep -i “Current database:” /tmp/dumpfilename.sql
           Current Database: `database_name`
           Current Database: `database_name_2`
   4>    Backup all the databases:
         # mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql
   5>    Backup a specific table:
         # mysqldump -u root -ptmppassword database1 accounts_contacts /tmp/database1_accounts_contacts.sql
    6>    Different mysqldump group options:
–opt is a group option, which is same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. opt is enabled by default, disable with –skip-opt.
–compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks
How to Restore MySQL database
    1>    Restore a database: When you are restoring the dumpfilename.sql on a remote database, make         sure to create the database before you can perform the restore.
# mysql -u root –ptmppassword
# mysql> create database database_name;
# mysql -u root -ptmppassword database_name < /tmp/dumpfilename.sql
    2>    Backup a local database and restore to remote server using single command: if you want to         create read only database in remote computer. For that you need to create database in remote               computer and then run below command.
[local-server]# mysqldump -u root -ptmppassword database_name  | mysql  \   -u root -ptmppassword –host=remote-server -C database_name1

MySQL Replication

How to Set Up Master Slave Replication in MySQL

This tutorial will use the following IP addresses:
12.34.56.789- Master Database
12.23.34.456- Slave Database

Step One—Configure the Master Database

/etc/mysql/my.cnf
Once inside that file, we need to make a few changes.
The first step is to find the section that looks like this, binding the server to the local host:
bind-address            = 127.0.0.1
Replace the standard IP address with the IP address of server.
bind-address            = 12.34.56.789
Make sure this line is uncommented.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
Restart MySQL.
sudo service mysql restart
The next steps will take place in the MySQL shell, itself.
Open up the MySQL shell.
mysql -u root -p
We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

Follow up with:
FLUSH PRIVILEGES;
The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.
In your current tab switch to “newdatabase”.
USE newdatabase;
Following that, lock the database to prevent any new changes:
FLUSH TABLES WITH READ LOCK;
Then type in:
SHOW MASTER STATUS;
You will see a table that should look something like this:
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.
If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.
Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).
mysqldump -u root -p --opt newdatabase > newdatabase.sql
Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;
Now you are all done with the configuration of the the master database.

Step Two—Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.
Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):
CREATE DATABASE newdatabase;
EXIT;
Import the database that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sql
Now we need to configure the slave configuration in the same way as we did the master:
sudo nano /etc/mysql/my.cnf
We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.
server-id               = 2
Following that, make sure that your have the following three criteria appropriately filled out:
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase
You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.
Restart MySQL once again:
sudo service mysql restart
The next step is to enable the replication from within the MySQL shell.
Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:
1. It designates the current server as the slave of our master server.
2. It provides the server the correct login credentials
3. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.
With that—you have configured a master and slave server.
 Activate the slave server:
START SLAVE;
You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.
SHOW SLAVE STATUS\G
If there is an issue in connecting, you can try starting slave with a command to skip over it:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Database Optimization

OPTIMIZE LARGE DATABASE

For large table and large db we need to change below parameters mostly to optimize it. It is not fixed for each server so you need to set it according to your server requirements.

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
key_buffer_size = 128M
innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_additional_mem_pool_size = 20M
innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_file_size = 64M
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.
table_cache = 2048
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
thread_cache_size = 8
query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
query_cache_size= 16M