September 27, 2018 at 11:17 pm
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.
September 28, 2018 at 4:27 am
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).
September 28, 2018 at 4:51 am
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
September 28, 2018 at 6:42 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 1, 2018 at 5:13 am
Thanks both. I will backup up manually databases, system databases and save scripts to create logins, jobs, linked servers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply