How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
Purpose: To clearly document a specific automatic SQL database backup method for Eclipse.net.
Please note that it is not MLS’s responsibility to support you in backing up your databases.
Should you require any further support with automatically backing up your SQL database,
please contact Microsoft support.
Automatic Backup
1.0 CREATING THE .SQL FILE
If you have not already installed Microsoft SQL Server Management Studio please do so as this step requires the software.
1.1 Open up SQL Server Management Studio, expand Databases and right click on your WBLS database
1.2 Right click and go to Tasks / Backup... to bring up the below screen
Right click on the WBLS database and select Tasks / Backup... Click on the Remove button to remove the current file destination
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
1.3 Once you’ve removed the current destination, click on the Add button, which will bring up the screen below; click on the button as instructed.
1.4 Either navigate to the desired file path on the window shown below or simply type in the name of the backup file in the “File Name” area
Click on this button to change the file path and/or to name the backup file.
Change File Path by navigating through the files structure shown.
Type the required file name into this area. Don’t forget to follow the file name with a .bak extension. e.g. WBLS_Auto_Backup.bak.
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
1.5 Click OK to the above screen, and then OK on the below screen
1.6 Click the Options page on the left-hand column. Continue configuring options accordingly. For example, you may want to select Overwrite all existing backup sets and tick Verify backup when finished
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
1.7 Once all desired options are set, select Script / Actions to File.
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
1.9 Click “Cancel” and now a script query should appear with the backup script as shown below:
The script should read as below:
BACKUP DATABASE [WBLS] TO DISK = N'D:\EclipseBackup\WBLS_Auto_Backup.bak'
WITH NOFORMAT, INIT, NAME = N'WBLS-Full Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10 GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N'WBLS' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'WBLS' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''WBLS'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\EclipseBackup\WBLS_Auto_Backup.bak'
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
1.11 Save the file into the ‘EclipseBackup’ location where the backup is going to be store as EclipseAutoBackup.sql
1.12 Now it is time to test your .SQL file; run the following from a command prompt: Sqlcmd-S.\<SQL instance>-i “<location of SQL file>”
Example:
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
1.13 If the command prompt looks as below and the backup was created in the location you have specified, it will have the correct date and time stamp; you then know your script works.
1.14 Once you have verified that the SQL file works create a MS-DOS batch file with the code used in step 1.12.
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
PLEASE NOTE:You need to ensure the following within your SQL server client configuration (Start / All Programs / Microsoft SQL Server 2005 / Configuration Tools SQL Server Configuration Management) Make sure that the port specified within the properties of SQL Server 2005 Network Configuration --> Protocols for *instance name* TCP/IP *Properties* IP Addresses IP All TCP Dynamic Port (for example, 58968) matches the port specified within the following:
SQL Native Client Configuration Client Protocols TCP/IP *Properties* Default Port
If these do not match, then the two will not be able to communicate when running the batch file / the above MS-DOS command.
2.0 AUTOMATING THE DATABASE BACKUP
2.1 You need to create a scheduled task, which is located in the Windows Control Panel. See screen print below, click Start / Settings / Control Panel / Scheduled Tasks / Add Scheduled Task
How to Backup Your Eclipse.Net Database
Automatically
ID: 020H
Version Number: 1.2
Date: 30/07/2010
2.2 Once you have clicked Next on the Add New Scheduled Task Wizard, the box below will appear asking you to select a program. Click Browse... and select the batch file you created earlier before clicking on the Next button.
2.3 Follow the instructions and add your own set of options. This should create an automated task which should run daily, weekly etc..
2.4 From here you can backup your database to a different location using backup software or creating a Batch File to move to a different location.
