December 11, 2009 at 11:14 am
Hi,
Why do i need system databases backups?
Assuming that i am taking only user database backups on daily basis, and if crash occurs to the system,can't i install
sql 2005 s/w and on top of that can't i restore the latest backups and fix the orphan users.
Is that not enough?
What is the need of taking system databases ? on which scenarios these database backups plays an important role?
Thanks in advance!
December 11, 2009 at 11:17 am
All your scheduled jobs are stored in MSDB. If you need to re-install SQL, it would be quite useful to be able to recover your jobs.
December 11, 2009 at 11:25 am
Logins, along with their passwords, and all server-level permissions are stored in master. You can't fix orphaned users if you don't have the logins.
Linked servers are in master.
MSDB stores backup history, jobs, maintenance plans, SSIS packages (if they're stored in the server). There might be service broker stuff in master or msdb, not sure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2009 at 12:06 pm
Besides the system databases, don't forget any certificates, master keys or otherwise that need to be backed up separately.
The idea is to be able to get back up and running as quickly as possible - in the same state you were running prior to the outage. Losing jobs, logins, encryption keys will delay the return to production and could cause a bigger headache.
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
December 11, 2009 at 12:29 pm
If that is the case, tell me whether following thing works for side - by - side upgade?
1.Install SQL 2008 with SP1
2.Restore or overwrite the system databases. i.e. master,msdb.
3.restore all user databases
4.fix orphan users
5.update stats on each database
6.update usage(0) on each database
Will this work out??????
Correct me if am wrong!
December 11, 2009 at 12:40 pm
All the backups are from SQL Server 2000 Standard with SP3!
December 11, 2009 at 12:46 pm
mahesh.vsp (12/11/2009)
If that is the case, tell me whether following thing works for side - by - side upgade?1.Install SQL 2008 with SP1
2.Restore or overwrite the system databases. i.e. master,msdb.
3.restore all user databases
4.fix orphan users
5.update stats on each database
6.update usage(0) on each database
Will this work out??????
Correct me if am wrong!
Side-by-side? No, you won't be able to restore the SQL Server 2000 system databases over the SQL Server 2008 system databases. After installing SQL Server 2008, you'll need to transfer the logins, jobs, etc to the SQL Server 2008 instance and then restore the user databases and fix orphans.
Of course, this is a high level view, the details may be much more involved.
December 11, 2009 at 10:48 pm
Hi,
Would like to know, how much impact will be there in the front end changes if i have migrated from sql 2000 to 2008.
For example) Connection String , dts to ssis calling using dtExec..., performance issues,drop n recreate replication ANSI join standards so on.
Would like to understand what more needed from the front end changes. How much percentage there can be front changes.
I know i might depend on application.
But am asking in a generic way, what are common obstacles/impact/areas where we need to interact with front - end developers
and address the issues so that we can say our migration is done smooth.
Any inputs will be greatly appreciated.
Thanks in Advance.
December 11, 2009 at 10:50 pm
Hi,
Would like to know, how much impact will be there in the front end changes if i have migrated from sql 2000 to 2008.
For example) Connection String , dts to ssis calling using dtExec..., performance issues,drop n recreate replication ANSI join standards so on.
Would like to understand what more needed from the front end changes. How much percentage there can be front changes.I know it might depend on application.
But am asking in a generic way, what are common obstacles/impact/areas where we need to interact with front - end developers and address the issues so that we can say our migration is done smooth.
Any inputs will be greatly appreciated.
Thanks in Advance.
December 12, 2009 at 1:57 am
Connection string, only if the server or instance name changes.
As for everything else, you need to do some serious, thorough testing before you even consider upgrading the production server. There's a lot of stuff that worked on 2000 that won't work (or works differently) on 2008. Some can be fixed through use of compatibility level (but setting a lower compat mode means you lose some SQL 2008 features)
You absolutely have to set up a test server with your app running against a SQL 2008 database and do and end-to-end test, fix any problems that you encounter and test again. Only when all the tests pass should you plan to upgrade the production server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2009 at 2:05 am
There's a lot of stuff that worked on 2000 that won't work (or works differently) on 2005.
Gila,
Can you please list out those which does work in 2000 and 2005??????
Like dts,
sp_addlogin ......
what else??????
December 12, 2009 at 2:45 am
mahesh.vsp (12/12/2009)
Can you please list out those which does work in 2000 and 2005??????
Do the testing!
I don't remember every single command (though I'd imagine Books Online does <hint>). It's not just that, some T-SQL constructs were valid in SLQ 2000 and are not valid in SQL 2008. Some will produce different results.
You absolutely have to test your own system, end to end, carefully. Plus run the SLQ 2005/2008 upgrade advisor (don't ask me where to get it, google it) and fix any issues that it finds.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2009 at 11:32 am
Thank you Gila!
December 12, 2009 at 3:38 pm
Hi,
I can find the deprecated features in the below url
http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx#appdev
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply