Script Backup Sql Server Express
The examples below show screenshots from SQL Server Management Studio Express 2014, which is similar to earlier versions of SQL Server Express. Create the Backup Job. Run SQL Server Management Studio Express. In the tree view, expand Server Objects = New Backup Device. The Backup Device dialog opens. I’ve used this simple SQL Server backup script a number of times to backup sql server to a file or network drive. It’s ideal if you are running SQL Server Express for example which does not come equipped with maintenance plans. This script can be used in conjunction with SQLCMD to backup SQL Server from the command line.
We have already covered how to backup a SQL Server database from the command line, so what if you want to backup all your databases at once? You could create a batch script which runs the backup command for each database, but this script would have to be updated each time a database is added or removed. Additionally, the database backups will all be appended to one file which will grow by the size of the new backup each time it is run. Instead, in true “set it and forget it” fashion, we will create a batch script which will adapt to your SQL Server as new databases are added and removed.
To get right to the point, this is the backup script:
@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C
REM Build a list of databases to backup
SET DBList=%SystemDrive%SQLDBList.txt
SqlCmd -E -S MyServer -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)” > “%DBList%”
REM Backup each database, prepending the date to the filename
FOR /F “tokens=*” %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S MyServer -Q “BACKUP DATABASE [%%I] TO Disk=’D:Backup%NowDate%_%%I.bak'”
ECHO.
)
REM Clean up the temp file
IF EXIST “%DBList%” DEL /F /Q “%DBList%”
ENDLOCAL
Assuming the date is 1/13/2009 and you have 3 databases named ‘MyDB’, ‘AnotherDB’ and ‘DB Name with Spaces’, the script will produce 3 files in the backup location specified:
- 2009-01-13_AnotherDB.bak
- 2009-01-13_DB Name with Spaces.bak
- 2009-01-13_MyDB.bak
Customizing and Running the Batch Script
Of course, you will want to customize the script to your environment so here is what you need to do:
- If your machine’s locale is not set to the US, the command ‘Date /T’ may not return the date in the format “Tue 01/13/2009”. If this is case, the NowDate variable will not produce the desired format and should be adjusted. (1 place)
- Change ‘MyServer’ to be the name of your SQL Server (add the instance name if applicable). (2 places)
- The databases named ‘master’, ‘model’, ‘msdb’ and ‘tempdb’ are databases which ship with SQL Server. You can add additional database names to this list if you do not want them to be backed up. (1 place)
- Change the backup location from ‘D:Backup’ to the location where you want the database backup files stored.
Once you have customized the batch script, schedule it to run via Windows Task Scheduler as a user with Administrator rights and you are all set.
READ NEXT- › How to Speed Up Your PlayStation 4’s Downloads
- › How to Disable the Login Screen’s Background Blur on Windows 10
- › How to Use All Linux’s Search Commands
- › How to See All Your Saved Wi-Fi Passwords on Windows 10
- › How Do Bone Conduction Headphones Work?
- Automate Backup Database on SQL Server, Part 1: Create VB Script
Sql Server Express 2012 Backup Script
Last year, I wrote an article about backup and restore database on SQL Server with Microsoft SQL Server Management Studio. That article (How to backup and restore database on Microsoft SQL Server 2005) was simple and plain. But it will be inconvenience if you have to backup databases frequently. So I decide to write another article. This article, I show you how to backup databases automatically on scheduled time. Therefore, you don’t have to waste time to manually backup databases on SQL Server anymore. Let’s me explain what I’m going to do to automate the task that I’ve mentioned above. First, I’ll create a VB Script file that perform backup database on SQL Server. Then, I create a Scheduled Task to execute the script daily. That’s it, the script will be executed according to the scheduled time without any user interaction.
Create VB Script
- In the example below, I’m going to create a VB Script that backup a database Northwind on SQL Server 2005 (INSTANCE01). Then, I’ll create a Scheduled Task to execute the script at 1:00 AM daily. Sounds easy, isn’t it? Let’s see it in action.
- On SQL Server 2005 server, open Notepad and type the following code:
Code Explanation:
- Line 3: Specify server name. “.” means the local computer.
- Line 5-6 and 24: Connect to SQL Server with Windows Authentication mode (Using current user credential). If you don’t want to specify username and password in the script, uncomment these line and comment the line 25 instead.
- Line 8-11: Variables Declaration
- Line 13-17: Assign values to variables.
- Line 13: Username for connect to SQL Server
- Line 14: Password of the username
- Line 15: The SQL Server. For SQL Server Express Edition, the value should be “.SQLEXPRESS”.
- Line 16: The Database name. In this example, it is Northwind.
- Line 17: Define location where you want to keep the backup file.
- Line 19-20: Create Objects to perform backup.
- Line 22-23: SQL Connection attributes.
- Line 24: Connect to SQL Server with Windows Authentication Mode (Doesn’t need username and password). See Line 5-6 and 24 for more detail.
- Line 25: Connect to SQL Server with SQL Authentication Mode (Specify username and password). The code above is set to connect by this method.
- Line 27: Set to True to overwrite the existing backup file.
- Line 28-29: Backup attributes,
- Line 30: Set location of the backup file.
- Line 31: Perform backup operation.
- Line 33: Close the connection to SQL Server.
- Customize the code as you desired. You should change the configurations on line 13-17 to match your environment. Then, save the file to .vbs format. In this example, I save to Northwind.vbs.
- Next, test the script by double-click the script to execute it. You should see the Northwind.bak file in the location where you have specified in the script.
- If you didn’t see the Northwind.bak, check the Application event log to see if there is any error. The figure below is the success backup message.
- For create a schedule task, I’ll write it soon.