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".
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.
Hope it will also be useful for your requirement also.
No comments:
Post a Comment