Creating a simple batch file to backup files or folders is a cool way to move, copy, and backup any file you like.
A batch file is a text file saved as a .bat extension and when doubled clicked on it will execute the dos commands contained in that file.
A batch file is a text file saved as a .bat extension and when doubled clicked on it will execute the dos commands contained in that file.
In this article I will be using the xcopy command and the robocopy command to create a batch file to copy a file or folder. If you have Windows XP you will use xcopy for your batch file and if you have Windows Vista you can use the new command, robocopy.
Then check, if event log backup files created successfully. Step 4 – Configure Script in Scheduler. Finally, configure this script in windows task schedulers to run it automatically on a regular interval. A daily backup is much sufficient for normal uses systems.
- Jun 08, 2018 In Windows Server 2003 to Backup. The backup job in a batch file by using. Customizing Windows Server Backup Schedule. Server 2008 that replaces the venerable NT Backup from Windows Server 2003. Of my batch file. I have a file that I'm try to move via a batch file on a timed schedule. Move File not working in Task Scheduler.
- (I considered tacking this on the end of the thread from July about NTBackup and batch files, but it is different enough to not have to reference anything in that thread.) I am running Backup Exec for Windows Servers on Windows Server 2003 SP-2; our email program is FirstClass.
How to create a batch file
- Open Notepad by going to the start menu>all programs>accessoires>then choose Notepad.
- Type your commands into the notepad file.
- Then go to the file menu at the top and choose Save As.
- Now save the file as Yourfilename.bat
Some examples on what to write in a batch file to copy or backup files
xcopy “C:Documents and SettingsYour usernameMy DocumentsMy Pictures” “H:backup of pictures” /e /y
This command will backup my pictures on C: drive and paste it to H: drive in a folder called backup of pictures. If you put the destination as just H: than all the files will be all over the place. You must specify a folder. I usually make a new folder first. Of course you can also get the batch file to make the new folder for you but this is another article altogether.
/e means to copy all directories and sub directories
/y means to copy with prompting to overwrite the already exsisting files
See more xcopy commands.
-->This article introduces how to use a Transact-SQL script and Windows Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis.
Original product version: SQL Server
Original KB number: 2019698
Original KB number: 2019698
Summary
SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.
Currently SQL Server Express users can back up their databases by using one of the following methods:
Use SQL Server Management Studio or Azure Data Studio. For more information on how to use these tools to Back up a database review the following links:
- Use a Transact-SQL script that uses the BACKUP DATABASE family of commands. For more information, see BACKUP (Transact-SQL).
This article describes how to use a Transact-SQL script together with Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis.
Note
This applies to only SQL Server express editions and not to SQL Server Express LocalDB.
More information
You have to follow these four steps to back up your SQL Server databases by using Windows Task Scheduler:
Step A: Create stored procedure to Back up your databases.
Connect to your SQL express instance and create sp_BackupDatabases stored procedure in your master database using the script at the following location:
Step B: Download SQLCMD tool (if applicable).
The
sqlcmd
utility lets you enter Transact-SQL statements, system procedures, and script files. In SQL Server 2014 and lower versions, the utility is shipped as part of the product. Starting with SQL Server 2016, sqlcmd
utility is offered as a separate download. For more information, review sqlcmd Utility.Step C: Create batch file using text editor.
In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the following examples into that file, depending on your scenario:
- All the scenarios below use
D:SQLBackups
as a place holder. The script needs to be adjusted to the right drive and Backup folder location in your environment. - If you are using SQL authentication, ensure that access to the folder is restricted to authorized users as the passwords are stored in clear text.
Note
The folder for the
SQLCMD
executable is generally in the Path variables for the server after SQL Server is installed or after you install it as stand-alone tool. But if the Path variable does not list this folder, you can either add its location to the Path variable or specify the complete path to the utility.Example1: Full backups ofalldatabases in the local named instance of SQLEXPRESS by using Windows Authentication.
Example2: Differential backups ofalldatabases in the local named instance of SQLEXPRESS by using a SQLLogin and its password.
Note
The SQLLogin should have at least the Backup Operator role in SQL Server.
Example 3: Log backups ofalldatabases in local named instance of SQLEXPRESS by using Windows Authentication
Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication
Similarly, you can make a differential Backup of USERDB by pasting in 'D' for the **@backupType** parameter and a log Backup of USERDB by pasting in 'L' for the **@backupType** parameter.
Step D: Schedule a job by using Windows Task Scheduler to execute the batch file that you created in step B. To do this, follow these steps:
- On the computer that is running SQL Server Express, click Start, then in the text box type task Scheduler.
- Under Best match, click Task Scheduler to launch it.
- In Task Scheduler, right-click on Task Schedule Library and click on Create Basic task….
- Enter the name for the new task (for example: SQLBackup) and click Next.
- Select Daily for the Task Trigger and click Next.
- Set the recurrence to one day and click Next.
- Select Start a program as the action and click Next.
- Click Browse, click the batch file that you created in Step C, and then click Open.
- Check the box Open the Properties dialog for this task when I click Finish.
- In the General tab,
- Review the Security options and ensure the following for the user account running the task (listed under When running the task, user the following user account:)The account should have at least Read and Execute permissions to launch sqlcmd utility. Additionally,
- If using Windows authentication in the batch file, ensure the owner of the task permissions to do SQL Backups.
- If using SQL authentication in the batch file, the SQL user should have the necessary permissions to do SQL Backups.
- Adjust other settings according to your requirements.
Tip
Backup Batch File Example
As a test, run the batch file from Step C from a command prompt that is started with the same user account that owns the task.
Be aware of the following when you use the procedure that is documented in this article:
Schedule Batch File
- The Task Scheduler service must be running at the time that the job is scheduled to run. We recommend that you set the startup type for this service as Automatic. This makes sure that the service will be running even on a restart.
- There should be lots of space on the drive to which the backups are being written. We recommend that you clean the old files in the Backup folder regularly to make sure that you do not run out of disk space. The script does not contain the logic to clean up old files.