Question about wahat a backup contains

SQL Server Administration
1

Hi,
Maybe I am searching in the right way but I cannot find this. I am in a small company so we have only one database that we backup, and we do that each night. However, I was wondering if we had to set up another server and recover that backup on there, what would that have. I mean I know it has the data ad transactions table objects, views, procedures. But what about things like all my security settings/ objects? Jobs?
triggers, reports server objects, or SSIS object in the tree?

Thank you

2

You need to back up all the system databases as well. That includes at least:
master (system configuration, logins, etc.);
model (only if you made customized changes to model);
msdb (jobs);
ReportServer (or whatever name you used for the SSRS db)

3

So, would I make another full back up for that to run at night also?
Thanks

4

Yes. You would use a similar method to whatever you use to back up your application db to back up those other dbs as well.

5

Okay thanks I really appreciate the help.

6

You can also script out jobs in SSMS or SSIS has a Transfer Jobs task you can use to copy over jobs.

7

One of the axioms for database administration is that you don't REALLY have a backup until you have restored from the backup and tested it. If you are not doing that routinely, that would be something to consider.

The company that I work for routinely conducts disaster recovery (DR) tests where the production environment runs out of the DR site for a day or two. Being a small company, you may not be in a position to have standby servers to do that, but it would be advisable to practice your disaster recovery on a regular periodic basis even if on a limited basis.

8

Just scripting out jobs won't do it. There's a lot more info in msdb, including proxy settings, job-related permissions, job history, backup history and so on. The best method is to backup the msdb db itself.

If it seems (way) too large, research procs:
msdb.dbo.sp_delete_backuphistory
msdb.dbo.sp_purge_jobhistory
and see if you need to run either/both of those jobs.

9

thank you

10

that is something I did not think of,

Thanks