One-off Backup and restore of all SQL Server Agent jobs using SQL Server Management Studio
1. Connect to the SQL server using SQL Server Management Studio.
2. Navigate to the Object Explorer Details view by selecting View à Object Explorer Details from the menu bar or press F7.
3. Double-click SQL Server Agent from the Object Explorer Details window.
4. Double-click the Jobs folder to expand the list of agent jobs currently configured on the SQL Server.
5. At this point you have the option to select only the jobs you want to backup, or select all jobs on the server (Ctrl+A).
6. Once your selection has been made, right-click over the selected area, hover Script Job As à CREATE To à File …
7.Navigate to a file location and save your SQL Server Agent job(s) creation code to a .SQL file.
Open the SQL file to execute on another SQL Server to restore a previous version of agent job(s), or to create the job(s) at a new location.
If you have backed-up a database with password protection such as:
BACKUP DATABASE [DATABASE] TO DISK = N‘\\NETWORK_STARAGE_LOCATION\database.BAK’ WITH PASSWORD = ‘PASSWORD’,
NOFORMAT, NOINIT, NAME = N‘DATABASE-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10
As outlined in a previous post https://robbamforth.wordpress.com/2008/11/07/sql-server-2005-database-backup/
Then you will be required to restore the database programmatically as both Enterprise Manger and SQL Server Management Studio do not support the password option when restoring with the GUI wizard.
To view the files available in the backup:
To run a full simple backup:
Northwind FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK’
This post shows how to restore a backup to a different location to where it was taken: https://robbamforth.wordpress.com/2009/11/18/sql-%e2%80%93-how-to-restore-a-database-backup-to-a-different-location/
You may be required to restore a database backup to a location different from the location of the original backup. If you backed up with a password then this will need to be done programmatically.
The following example shows how to use a backup taken from a database called Database and restore it to a new location on a new server with the name Database2.
RESTORE DATABASE [NEW_DATABASE]
FROM DISK = N’\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH password = ‘password’,
FILE = 1,
MOVE N’database’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.mdf’,
MOVE N’database_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.log,
NOUNLOAD, STATS = 10
The backup procedures in SQl Server 2005 a very complex. The following code is a simple way to conduct a backup of an entire database to a network location. You can use the back up wizard by right-clicking on your database in the Management Studio – and selecting backup.
However, I have many databases spread across multiple servers and found this bit of code very useful to copy, paste and edit for each database that i want to backup.
BACKUP DATABASE [DATABASE1] TO DISK = N‘\\NETWORK_STARAGE_LOCATION\Data\20081107\DATABASE1_200811070.BAK’ WITH PASSWORD = ‘PASSWORD’, NOFORMAT, NOINIT,
NAME = N‘DATABASE1-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10
This will create a full backup of a database called DATABASE1 in the following directory:
to a file called
You can read a lot more about SQL Server backups, such as differential backups in online docs. But I have found this helpfull when conducting regular backups of the same structure.