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<INSTANCENAME>-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
