Showing posts with label Backup. Show all posts
Showing posts with label Backup. Show all posts

Monday, March 16, 2020

Scripting Jobs in SQL Server

Have you every wanted to save your SQL Server jobs to a SQL script file?  It's not straight-forward from the interface, but is easily done.  Just follow these steps:

  1. In SQL Enterprise Manager, open the list of Jobs by opening the Object Explorer tree to SQL Server Agent -> Jobs
  2. Next, press F7 to open the "Object Explorer Details" (Can also use the menu View -> Object Explorer Details)
  3. You should see all your jobs listed.  If not,  you might need to navigate through the list and find them.
  4. Select the jobs you want to backup (You can use Shift and Ctrl)
  5. Right click and select Script Job As -> Create To -> File
  6. Finally enter a filename and press Save
I hope this makes your life easier.  Now get to backing up those jobs!

Tuesday, September 12, 2017

Restoring SQL Server Database: Exclusive access could not be obtained because the database is in use

Here is the scenario - you are in a rush to restore a down SQL database.  Then you get the dreaded:


Exclusive access could not be obtained because 
the database is in use.
Don't panic.  You can do two things.

Option 1

If you are using the SQL Manager Tool, you select "Options" from the side panel.  On there you will find a checkbox to go into single-user mode for the Restore.  This will do the trick.


If this option is grayed out, cancel the restore operation, then bring it up again.  Check this box first before doing anything else.  It should just work.

Option 2

If you are using the command line, use this command:

USE master
GO
ALTER DATABASE DATABASE_NAME
SET SINGLE_USER
Now do your restore as normal.

Afterward

Don't forget to make sure you are in multi-user mode!
USE master
GO
ALTER DATABASE DATABASE_NAME
SET MULTI_USER