Batch Script: SQL Server Database Backup, Refresh, sanitize, run script and upload csv to sftp

One of the customers requested the following:

Backup a production database and restored it on a dev server. Sanitize the refreshed database and run a script and upload results as a csv file to sftp site. I know this can be done with SSIS, but I thought a batch job would be simpler to manage and troubleshoot.

First, create a directory for this job and add the following files to it. I will call mine D:\NirvanaInc. We are going to use the following tools to get this task done.

• SQLCMD - To run backup/restore commands, sanitize scripts etc
• PSFTP - To upload files to an SFTP site
• sendemail - To send notification emails

Now that we are ready, lets start building the scripts we will use. The idea is to create one batch file which will call and run various scripts that do backup, restore, cleanup etc. this approach will make it incredibly easier to maintain and make changes to the job in future, if needed.

backup.sql

This is simple backup script:

USE master;

DECLARE @startdate VARCHAR(100);

SET @startdate = 'backup start: ' + convert(VARCHAR(32), getdate(), 121);

PRINT @startdate;

BACKUP DATABASE [NirvanaProd] TO DISK = N'R:\Backups\NirvanaProd.bak'
WITH COPY_ONLY
 ,NOFORMAT
 ,INIT
 ,NAME = N'NirvanaProd-Full Database Backup'
 ,SKIP
 ,NOREWIND
 ,NOUNLOAD
 ,COMPRESSION
 ,STATS = 10
GO

DECLARE @backupSetId AS INT

SELECT @backupSetId = position
FROM msdb.dbo.backupset
WHERE database_name = N'NirvanaProd.bak'
 AND backup_set_id = (
  SELECT max(backup_set_id)
  FROM msdb..backupset
  WHERE database_name = N'NirvanaProd.bak'
  )

IF @backupSetId IS NULL
BEGIN
 RAISERROR (
   N'Verify failed. Backup information for database ''NirvanaProd'' not found.'
   ,16
   ,1
   )
END

RESTORE VERIFYONLY
FROM DISK = N'R:\Backups\NirvanaProd.bak'
WITH FILE = @backupSetId
 ,NOUNLOAD
 ,NOREWIND
GO

DECLARE @enddate VARCHAR(100);

SET @enddate = 'backup end: ' + convert(VARCHAR(32), getdate(), 121);

PRINT @enddate;

Restore.sql

-- restore backup on target server, SQL:
USE master;

-- wait for start time
DECLARE @now DATETIME
 ,@targettime DATETIME;

-- targettime is set to 02:00:00 AM, If the backup is done sooner, it will wait till 2 AM to kick off restore
SET @targettime = substring(CONVERT(VARCHAR(39), GETDATE(), 121), 1, 10) + ' 02:00:00';
SET @now = GETDATE();

IF (@targettime > @now)
BEGIN
 WAITFOR TIME @targettime;
END;

DECLARE @startdate VARCHAR(100);
SET @startdate = 'db restore start: ' + convert(VARCHAR(32), getdate(), 121);
PRINT @startdate;

IF EXISTS (
  SELECT *
  FROM sys.databases
  WHERE NAME = 'NirvanaDev'
  )
BEGIN
 ALTER DATABASE [NirvanaDev] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END

RESTORE DATABASE [NirvanaDev]
FROM DISK = N'R:\Remotebackups\Nirvanaprod.bak'
WITH FILE = 1
 ,MOVE N'FinancialSoftwareSystems_Data' TO N'D:\SQLData\NirvanaDev.mdf'
 ,MOVE N'FinancialSoftwareSystems_log' TO N'D:\SQLData\nirvanaDev.ldf'
 ,NOUNLOAD
 ,REPLACE
 ,STATS = 10
GO

ALTER DATABASE [NirvanaDev] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

DECLARE @enddate VARCHAR(100);
SET @enddate = 'db users restore end: ' + convert(VARCHAR(32), getdate(), 121);
PRINT @enddate;

I will leave out sanitize and grant permissions script because they are specific to your environment and you should build your scripts accordingly. Next up is running the a report script on target database and uploading the output as CSV to SFTP site. I chose PSFTP because of its ease of use.

run_psftp.bat

echo lcd D:\NirvanaInc > psqftp.bat
echu lpwd >> psftp.bat
echo put D:\NirvanaInc\sql_results.csv >> psftp.bat
echo quit >> psftp.bat

c:\sysutil\psftp mysftpuser@sftp.nirvanainc.com -pw myFancyPwd -b psftp.bat  -v

By now, you should have the following files on your D:\NirvanaInc folder:
backup.sql
restore.sql
sanitize.sql
grant_script.sql
run.sql
run_psftp.bat


Finally, the main batch file:

@echo off
goto :startbatch

Description
=============================================================
This batch file is used for 
1. Taking a backup of NirvanaProd database
2. Copying backup file to a Dev db server
3. Restore database on Dev Environment
4. Sanitizing Database
5. Creating user and granting permission
6. Run a script
7. Upload results to sftp
===============================================================

:startbatch

REM set variablesa

set source_server=10.10.20.21
set target_server=10.10.20.22
set source_db=NirvanaProd
set target_db=NirvanaDev

set home_dir=D:\NirvanaInc\NirvanaProd
set backup_script=%home_dir%\backup.sql
set backup_out=%home_dir%\backup_out.txt
set copy_out=%home_dir%\copy_out.txt
set restore_script=%home_dir%\restore.sql
set restore_out=%home_dir%\restore_out.txt
set sanitize_script=%home_dir%\sanitize.sql
set sanitize_out=%home_dir%\sanitize_out.txt
set grant_script=%home_dir%\grant_script.sql
set grant_out=%home_dir%\grant_out.txt
set run_script=%home_dir%\run.sql
set run_script_out=%home_dir%\sql_results.csv

set serverlogin=mylogin
set serverpass=fancypassword123


REM backup your production database
sqlcmd -S tcp:%source_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %backup_script% -o %backup_out%


REM copy backup from source server to target server, log is in %copy_out%
echo copy of backup file started at: %date% %time% > %copy_out%
copy /y \\%source_server%\R$\Backups\%source_db%.bak \\%target_server%\R$\RemoteBackups 1>>%copy_out% 2>&1
echo copying of the backup file completed at: %date% %time% >> %copy_out%

REM Restore backup on the target server
sqlcmd -S tcp:%target_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %restore_script% -o %restore_out%

REM Sanitize database and create user, grant permissions
sqlcmd -S tcp:%target_server%,1433 -d %target_db% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %sanitize_script% -o %sanitize_out%
sqlcmd -S tcp:%target_server%,1433 -d %target_db% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %grant_script% -o %grant_out%

REM Run sql script
sqlcmd -S tcp:%target_server%,1433 -d %target_db% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %run_script% -o %run_script_out%

REM Upload run_sql.csv results to SFTP
call %home_dir%/run_psftp.bat 

REM send emails on completion
C:\sysutil\sendemail -t alldba@nirvanainc.com -cc ImeMyself@nirvanainc.com -u "%target_db% refresh" -m "%source_db% backup from server %source_server% is restored on %target_server% as db %target_db% . The script ran successfully and the output was uploaded to SFTP" -s 192.178.6.221:25 -f "Gandalph@NirvanaInc.com" -a %backup_out% -a %restore_out% -a %sanitize_out% -a %grant_out% 

have fun! :)