How to Backup Sql Express Database Manually

Protecting Your Data Through Regular Backups

As the database administrator for your organization's implementation of an enterprise database in any ReliaSoft desktop application, you have the ability to perform the backups and other maintenance activities necessary to protect the information stored in the SQL Server database. Please refer to the SQL Server documentation for detailed coverage of this topic. In addition, this document provides specific instructions for two ways that you can perform backups: on demand or scheduled.

To quickly jump to sections in this document, use these links:

• Create a Backup on Demand Using SQL Server Management Studio Express

• Automate Backups Using SQL Server Management Studio Express and Windows Task Scheduler

  • Creating the .sql Script

  • Create a Backup Task Using Windows Task Scheduler

    • Configuring the Scheduled Task to Run the Database Backup Script

• Restoring the Data

• Additional Support

Note: These instructions were prepared using SQL Server Express 2008 and Windows XP. The options may vary if using SQL Server Express 2005, Windows Vista or Windows 7.

Important: While the pictures in this document show Xfmea, the same instructions apply to any ReliaSoft database.

Create a Backup on Command Using SQL Server Management Studio Express

The free SQL Server Express Edition provides the option to create database backups on command through the SQL Server Management Studio Express.

• Open SQL Server Management Studio Express.

• ExpandDatabases, then right-click the application database.

• ChooseTasks > Back Up from the shortcut menu.

• Enter your preferences in the Back Up Database window and then clickAdd, which will allow you to specify the location and the filename of the backup that will be created.

  • Note: Make sure that the file name you create ends with the .bak extension (e.g.,  eXfmeabackup.bak) or SQL Server will not recognize this file if you need to use it to restore the database.

• The filename and location will appear in the window below the Destination header. Make a note of where this file is being saved so you can locate it to restore your database, if needed.

• Repeat these steps each time you wish to manually initiate a backup of your database or continue to the next section to see how to automate this process and run the backups on a schedule.

Return to top

Automate Backups Using SQL Server Management Studio Express and Windows Task Scheduler

The free SQL Server Express Edition does not provide a built-in utility to schedule automated backups. However, there is a workaround that can be set up using the Task Scheduling feature built into Windows. After using the steps above to create an on demand backup, you can create a script that will automate the backup process. Use the following steps to create an automated backup schedule for your database.

Creating the .sql Script

By following the steps described above, you have already selected the settings and the destination for the backup file, so you now need to generate the script that will allow you to automate the running of the backup. To do that:

• Click the arrow to the right of theScript menu at the top of the window and then chooseScript Action to File. This will create a simple text file (with a .sql extension) that contains the instructions required to perform the backup and save it to the pathname/filename that you specify. Be sure that this file is stored in a safe, accessible location because this is the file that Windows Task Scheduler needs to point to in order to automate the backups. Make sure to note the location for the final step.

• After you have created the .sql script file (that will run the backup), clickOK and then exit SQL Server Management Studio Express.

  Return to top

Creating the  Backup Task in the Task Scheduler

• Open the Windows Task Scheduler by clickingStart  > Programs  > Accessories  > System Tools and then chooseScheduled Tasks. (You can also find Scheduled Tasks in the Control Panel.) In this window, double-clickAdd Scheduled Task. The Scheduled Task Wizard will walk you through the next steps.

• When prompted for the program that you want Windows to run, clickBrowse and then go to the SQL installation location to select sqlcmd.exe (in default installations, the file is found here: C:\Program Files\Microsoft SQL Server\90\Tools\Binn). Create a name for the scheduled task (we have used "eXfmea Backup" in our example) and then specify the frequency of your desired backups. ClickNext.

• Select the time and days when you wish the task (the backup) to run. ClickNext.

• When prompted, type the login information and password (this must be an account with administrative rights on this computer). ClickNext.

• In the final step of the wizard, select the check box that says "Open advanced properties for this task when I click Finish." This will allow you to add the reference to the script that you created in SQL Server Management Studio Express. ClickFinish.

• If you missed the check box in the previous step, just double-click the newly created task in the Scheduled Tasks window.

Return to top

Configuring the Scheduled Task to Run the Database Backup Script

After completing the wizard (or double-clicking the task in the Scheduled Tasks window), you will see the properties of the task you have created.

• On the Task page, you will need to add information at the end of the existing text in theRun field. Click inside the field and use the right arrow key to scroll all the way to the right (or press theEnd key). Type or copy/paste the following textafterthe final quote in theRun line:

  -S \sqlexpress -i

  • Note: "-S" tells the task which instance of SQL server to back up (\sqlexpress if the default was used during the SQL setup) and "-i" tells it which input file to run on the server. (The input file is the name/location of the .sql script file we created for the on demand backup. We will add the name of that file to the end of this line in the following step.)

• In the same field, and after the line you just typed or pasted, type the location of the script file that you created using SQL Management Studio Express. If you called the file "sqlbackup.sql" and saved it in the root of your C:\ drive, then you would type this (using the quotes):

  "C:\sqlbackup.sql"

• The full Run line will look similar to this:

  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S \sqlexpress -i "C:\sqlbackup.sql"

• You can adjust when the task runs by clicking theSchedule tab and making changes. On theTask tab, if you have an account with a password that periodically expires or changes, you can click theSet passwordbutton to update your password.

  • Note: You will not be notified if the scheduled task does not complete successfully. You will have to come back to the Scheduled Tasks window to view the scheduled time, next run time and last run time. If your password has expired or changed, the task will not run.

Return to top

Restoring  the Data

To recover the data from one of the backups:

• Open SQL Server Management Studio Express.

• ExpandDatabases, then right-click the application database.

• ChooseTasks > Restore > Database.

• In the Restore Database window, selectFrom device: and then click the... button to the right of the field.

• ClickAdd and then browse to the location of the backup file and select it (in our example, the default location for the backup is used). ClickOKto close the browse window. ClickOK to close the Specify Backup window after it shows the file in the Backup location field, as shown below.

• In the Restore Database window, select the remaining options appropriate for your restoration and then select theRestore check box in theSelect the backup sets to restore: field. ClickOK to restore the data from the selected backup.

Return to top

Additional Support

If you have any further questions about how to use ReliaSoft database with SQL Server, or if you experienced any problems with this instruction document, please contact technical support.

Additional online support for SQL Server Express can also be found at this location:

SQL Server Express Forums: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/threads

Information is provided "as is" without warranty of any kind.

How to Backup Sql Express Database Manually

Source: https://www.reliasoft.com/sql-server-express-backup-instructions

Share this post