Schedule Database Backup using Windows Task Scheduler

You may have seen my last past about to Auto synchronize Data from Production to Development using Script Daily.

On top of that, we also had requirement to schedule database backup one database is synchronized.

We also used windows task scheduler using batch file to do this task because we had SQL Server Express edition installed on our Server.

Batch File

Now, we will see how to create batch file which will execute SQL Server BACKUP DATABASE command to take backup of database from server to local file system.

We have also created query so it will create backup in below format.
Database_YYYYMMDD_hhmmss
(YYYY = Year, MM = Month, DD = Day, hh = hour, mm = minute, ss = second).

Query:
SQLCMD -S [Server Name] -U [User Name] -P [Password] -Q "Declare @vDisk nvarchar(500); Set @vDisk = N'V:\Asif Ghanchi\Asif_'+ replace(replace(replace(convert(varchar,getdate(),120),'-',''),' ','_'),':','') + '.bak'; BACKUP DATABASE [Asif] TO DISK = @vDisk WITH NOFORMAT, NOINIT, NAME = N'Asif-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
You can get more details about SQLCMD from below link.
http://technet.microsoft.com/en-us/library/ms162773.aspx

Below are details of above parameters.

1. [Server Name] : You can get server name from you SQL Server Management Studio (SSMS) using below query

Query:
Select @@servername

2. [User Name] (Optional): If you want to connect SQL Server using SQL Authentication then you can define your user name here. Else it will connect using Windows authentication.
3. [Password] (Optional) : You can define SQL Server User Name Password here.

Scheduler
Now, we will see how to create windows "Task Scheduler" which will execute above batch file daily.

To do that, we need to follow below steps.
1. Go to Start -> Control Panel -> Administrative Tools -> Task Scheduler
2. Click on "Create Basic Task".

3. Enter Name and Description of Task and Click Next.

4. Choose appropriate schedule type and Click Next. I have choose "Daily".

5. Choose appropriate start time and recurring based on Schedule Type selected in last step. I have choose 11 AM for every day.

6. Choose action which you want to perform and Click Next. We have choose "Start a program" because we want to execute batch file for this task.

7. Browse batch file location from where you want to execute batch file for this task and Click Next.

8. Review Screen and Click Finish.

9. Below is task which is created in Windows Task Scheduler.

Hope it will also be useful for your requirement also.

Schedule Data Backup from Production to Development

OR
Auto synchronize Data from Production to Development using Script Daily
OR
Refresh data from Production to Development Server.

We mostly seen a situation where we want to synchronize data from Production to Development or QA Server on demand request, daily or weekly basis.

We have different tools to achieve it.

1. Red Gate Data Synchronization
2. Data Synchronize using SSDT

I come to a situation where my client doesn’t have a license software for SQL Server so we have installed express edition which doesn’t have SSDT installed with it.

In such situation, I thought to create dynamic procedure sc.cp_data_synch which will synchronize data from Production to Development Server based on table definition in Development Server.

Below are parameters for the procedure sc.cp_data_synch.

1. @p_SourceServerName
   1. Server Name of Production Server from where we want to synchronize data.
2. @p_SourceUserName
   1. User Name of Production Server.
3. @p_SourcePassword
   1. Password of Production Server
4. @p_SourceDatabaseName
   1. Database Name of Production Server

Below is sample execution plan.

exec [sc].[cp_data_synch]
@p_SourceServerName = 'Source Server Name',
@p_SourceUserName = 'Source User Name',
@p_SourcePassword = 'Source Password',
@p_SourceDatabaseName = 'Source Database Name'

Batch File

Now, we will see how to create batch file which will execute above script.

Below is command which is used to execute above script.

SQLCMD -S [Server Name] -U [User Name] -P [Password] -Q "EXEC Asif.dbo.cp_data_synch"

You can get more details about SQLCMD from below link.
http://technet.microsoft.com/en-us/library/ms162773.aspx
 Below are details of above parameters.

1. [Server Name] : You can get server name from you SQL Server Management Studio (SSMS) using below query

Query:
Select @@servername
2. [User Name] (Optional): If you want to connect SQL Server using SQL Authentication then you can define your user name here. Else it will connect using Windows authentication.

3. [Password] (Optional) : You can define SQL Server User Name Password here.

Scheduler

Now, we will see how to create windows "Task Scheduler" which will execute above batch file daily.

To do that, we need to follow below steps.

1. Go to Start -> Control Panel -> Administrative Tools -> Task Scheduler

2. Click on "Create Basic Task".

3. Enter Name and Description of Task and Click Next.

4. Choose appropriate schedule type and Click Next. I have choose "Daily".

5. Choose appropriate start time and recurring based on Schedule Type selected in last step. I have choose 9 AM for every day.

6. Choose action which you want to perform and Click Next. We have choose "Start a program" because we want to execute batch file for this task.

7. Browse batch file location from where you want to execute batch file for this task.

8. Below is task which is created in Windows Task Scheduler.

Below are scripts to download:

1. sc.cp_data_synch.sql

Release multiple Oracle scripts to Production Server using batch file

I am writing this blog for all DBA team who are doing this task manually for their Production Deployment repeatedly.

To solve this problem, I have created a batch file which will read all SQL files from folder excluding child folder and use SQLPLUS to execute scripts file to specified Data Source.

How to run batch file ?

1. Copy batch file “ReleaseOracleScripts.bat” to location from where you want to release SQL files.

2. Click on “ReleaseOracleScripts.bat” to execute batch file.

3. Batch file will ask Host Name, Port, Service Name, User Name and Password where you want to deploy your scripts.

4. Batch file will ask you to click any key before executing each script.

5. Once click any key, It will execute this script to Target Server and display output.

6. Once all scripts are deployed, It will ask to click any key to close window.

Below is the batch file to download.

1. ReleaseOracleScripts.bat

Release multiple SQL Server scripts to Production Server using batch file

I am writing this blog for one on my colleague Kallidile, Sujith who wants to do this task for his Production Deployment repeatedly. This is also dedicated to all DBA team who are also doing same.

To solve this problem, I have created a batch file which will read all SQL files from folder excluding child folder and use SQLCMD to execute scripts file to specified Data Source.

How to run batch file ?

1. Copy batch file “ReleaseSQLServerScripts.bat” to location from where you want to release SQL files.

2. Click on “ReleaseSQLServerScripts.bat” to execute batch file.

3. Batch file will ask Server Name where you want to deploy your scripts.

4. Batch file will ask you to click any key before executing each script.

5. Once click any key, It will execute this script to Target Server and display output.

6. Once all scripts are deployed, It will ask to click any key to close window.

Below is the batch file to download.

1. ReleaseSQLServerScripts.bat

Note:

1. We are using window authentication for deployment.

Unzip all *.zip files from folder including child folders

I am writing this blog for one on my colleague Ashish Gupta who wants to do this task for his Production Deployment repeatedly.

We had requirement in our company to unzip all files from folder including child folders to specified location. It was not only single file but we had lots of zip files and each was having lots file in there.

To solve this problem, we have created batch file which will read all zip files from folder including child folder and use winzip32.exe command to extract all files to specified location.

We also found that winzip32.exe command will open folder where files are extracted once unzip operation is completed. We had also provided solution for this here.

How to disable “Show unzipped file” option ?

1. Open C:\Program Files (x86)\WinZip\WINZIP32.EXE.

2. Click right bottom error at “Unzip Options”.

3. Uncheck “Show unzipped file”.

How to run batch file ?

1. Copy batch file “ExtractZipFiles.bat” to location from where you want to extract all zip files.

2. Click on “ExtractZipFiles.bat” to execute batch file.

3. Batch file will ask location where you want to extract all files and click enter. I have used “C:\GhanchiAsif” to extract my all files.

4. Batch will extract all files and extract to “C:\GhanchiAsif”.

Below is the batch file to download.

1. ExtractZipFiles.bat