Backup whole instance SQL Server 2008

  • Farik013

    Old Hand

    Points: 393

    Hi. Is there a was to take backup of whole instance (databases, logins, jobs, etc.)? I have an instance where a lot of little databases and a lot of users. And know we must migrate it to SQL Server 2016, but before I want to take backup of all instance, because there is a lot of users and jobs and I can forget something.

  • Farik013

    Old Hand

    Points: 393

    I found how can I do backup of all agent jobs, logins and other objects:

    1) Start SQL Server Management Studio.
    2) Expand the SQL Server Agent folder, and then expand the list of Jobs.
    3) Click to the View menu panel, then to Object Explorer Details (or press F7) to show the details window (see image below).
    4) The new details window should show a list of jobs which allows to select multiple jobs.
    5) Use Shift+Left-Click or CTRL+Left-Click to select the jobs to create a backup script for, then right-click on them and select Script Job as.
    6) Click CREATE To or DROP To, then click New Query Editor Window, File, or Clipboard to select a destination for the script that will contain all the job you want to backup. Typically, the destination is a file with a .sql extension.

    and restore as:

    1) Start SQL Server Management Studio.
    2) Click File, click Open, then click File.
    3) Locate and open the file containing the scripted job(s).
    4) Execute the script to create the job(s).

  • ThomasRushton

    SSC-Insane

    Points: 22591

    It might be worth checking out the powershell DBA tools - https://dbatools.io/commands - particularly as it started off as a toolkit for doing full server migrations...

    If, on the other hand, you just want to "backup the whole instance", then what's wrong with just that?  ie making sure you have a BACKUP of every database on the instance (including the system databases 'master' and 'msdb' (and maybe 'model')).  Just make sure you know how to restore them...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Grant Fritchey

    SSC Guru

    Points: 395316

    Reinforcing Thomas' answer. Backup every database on the server (except tempdb) and you've backed up the server. All the logins, etc., are stored in system databases. Capture those, you've captured the logins.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Farik013

    Old Hand

    Points: 393

    Thanks both. I will backup up manually databases, system databases and save scripts to create logins, jobs, linked servers.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply