August 24, 2011 at 1:14 pm
I would like to setup some maintenance plan for a SQL server reporting server.
I would like to know if the database reportserver and reportservertempdb are system databases or user databases, where can I find out?
Thank you
August 24, 2011 at 2:08 pm
Reportserver and reportservertempdb are user databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 2:42 pm
in truth, to SSRS, they are system databases. They hold metadata and temporary storage (sort of like a TEMPDB) used by the reporting services instance(s)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2011 at 2:56 pm
Perry Whittle (8/24/2011)
in truth, to SSRS, they are system databases. They hold metadata and temporary storage (sort of like a TEMPDB) used by the reporting services instance(s)
That is true - with regards to ssrs. SQL treats them like user databases though. There is no real good documentation on whether it is user or system. But if you go by systemdbs are id <=4 that should help.
I have seen some say that id<=6 is system, but that is false. If installed right when SQL Server is installed then Reportingservices dbs will get ids 5 and 6. If you install ReportingServices after another userdb, then reportingservices will get the next available database ids.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2011 at 5:31 pm
Yes, it seems sql server thinks it as user dbs.
In maintenance plan if I choose to backup system db, it will not include the two databases, vise versa if I chose to backup user dbs, it will backup the reportserver and reportservertempdb.
Thanks
August 25, 2011 at 2:39 am
you might like to read my experience here http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/09/04/ssrs-do-you-back-up-reportservertempdb.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 25, 2011 at 9:27 am
Thanks, and it's good to know reporttempdb needs to be backed up.
August 25, 2011 at 10:10 am
SQLRNNR (8/24/2011)
Perry Whittle (8/24/2011)
in truth, to SSRS, they are system databases. They hold metadata and temporary storage (sort of like a TEMPDB) used by the reporting services instance(s)That is true - with regards to ssrs. SQL treats them like user databases though. There is no real good documentation on whether it is user or system. But if you go by systemdbs are id <=4 that should help.
I have seen some say that id<=6 is system, but that is false. If installed right when SQL Server is installed then Reportingservices dbs will get ids 5 and 6. If you install ReportingServices after another userdb, then reportingservices will get the next available database ids.
distribution db_id on my servers is 5 or greater but that appears as a system database? How about mssqlsystemresource? Thats not even in there.
They are user databases by definition in SQL server.
August 25, 2011 at 10:17 am
MysteryJimbo (8/25/2011)
SQLRNNR (8/24/2011)
Perry Whittle (8/24/2011)
in truth, to SSRS, they are system databases. They hold metadata and temporary storage (sort of like a TEMPDB) used by the reporting services instance(s)That is true - with regards to ssrs. SQL treats them like user databases though. There is no real good documentation on whether it is user or system. But if you go by systemdbs are id <=4 that should help.
I have seen some say that id<=6 is system, but that is false. If installed right when SQL Server is installed then Reportingservices dbs will get ids 5 and 6. If you install ReportingServices after another userdb, then reportingservices will get the next available database ids.
distribution db_id on my servers is 5 or greater but that appears as a system database? How about mssqlsystemresource? Thats not even in there.
They are user databases by definition in SQL server.
Correct, the resource database will not show in either location as a database. It is a hidden database and protected for good reason.
As for a resource that explicitly states the system databases, read this. http://msdn.microsoft.com/en-us/library/ms190190.aspx.
Distribution is certainly different than the reportingservices databases. All, I am saying is that the assumption of database ids <=6 means that it is a system db is completely inaccurate.
Also, since a maint plan using the option of "user databases" backs up the reporting services databases, I would stick with the assertion that those are user databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2011 at 10:25 am
SQLRNNR (8/25/2011)
Correct, the resource database will not show in either location as a database. It is a hidden database and protected for good reason.
Distribution is certainly different than the reportingservices databases. All, I am saying is that the assumption of database ids <=6 means that it is a system db is completely inaccurate.
<=4 is partitially inaccurate also as it does not cater for the distribution db.
August 25, 2011 at 10:30 am
MysteryJimbo (8/25/2011)
SQLRNNR (8/25/2011)
Correct, the resource database will not show in either location as a database. It is a hidden database and protected for good reason.
Distribution is certainly different than the reportingservices databases. All, I am saying is that the assumption of database ids <=6 means that it is a system db is completely inaccurate.
<=4 is partitially inaccurate also as it does not cater for the distribution db.
Point taken.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply