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.
Occasionally an SQL database can lock-down in suspect mode. There are many causes, but the main is an expected shot-down or power loss. I had a slightly loose fitting CPU fan (and I mean ightly, one of the pins was a little less secure than the rest) in a server running SQL server 2005. It took a morning of stripping back the chassis to find it. Since re-seating the fan pins all is well.
To cut a long story short, the following script allowed me to restore the suspect database in its entirety. SQL server 2005 comes with the DB status of Emergency. This can be used to alter the status from Suspect mode (in which the database is inaccessible), to Emergency mode where you can gain access to the database and retrieve data from the tables along with those all important stored procedures and other scripts.
Once in Emergency mode you can re-run DBCC checkDB to check the integrity of all the pages and structures that make up the tables.
This script uses the REPAIR_ALLOW_DATA_LOSS argument which tries to repair all reported errors. These repairs can cause some data loss. Hopefully you have a recent back up, if not take backups from the database while you can access it in emergency mode!
REPAIR_ALLOW_DATA_LOSS is used to ensure the database is returned to a structurally and transitionally consistent state
This cannot be undone.
Here is the script:
EXEC sp_resetstatus DATABASENAME;
ALTER DATABASE DATABASENAME SET EMERGENCY
ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (DATABASENAME, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASENAME SET MULTI_USER
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