August 6, 2009 at 7:54 am
I hope that I am not just re-hashing an old topic, but I am trying to find a list of everything humanly possible to do to try and have a backup that will help me re-create a SQL server on to a new machine or a restored machine, without commercially boughten software, that is as close to the exisiting settings were. I know that having full backups of all of the databases is the majority of the fight, but I am trying to figure out any "gotchas" such as any tweakings that has been done to the server settings or security. Any additional "generate SQL scripts" kind of things. What got me to thinking about this was an article about how if you detach and attach a database, to move the physical files to a new location, you can lose database properties like cross database ownership chaining.
August 6, 2009 at 8:31 am
Do you mean database backup only and not server backup?
Pretty much everything is stored inside SQL Server. If you restore master and msdb, you have covered all the issues with logins, users, jobs, etc.
The things you could be missing are the error log location (register, services parameter), possibly the port (not sure if that's stored inside), and AD/Windows rights for the service accounts.
August 6, 2009 at 11:57 am
What I am referring to is let's say at midnight I do a full back up of all databases and no other updates are done to them. Then an electrical problem causes the hard drives to be trashed. My server group gives me new hardware and installs the OS and such. I will now have to re-install SQL and restore databases. Will the system be just as it was at midnight when it was backed up? Or will there voids that the normal backups wouldn't have caught. I am just trying to cover all of the bases I can, especially since I have inherited all of the systems I have. If there were startup paramenters added that I am not aware of should I have copied something or interogated and documented information? I was just wondering if anyone had a rather thorough list of all of the little tasks someone should do get back as close as possible to their original system.
August 6, 2009 at 12:37 pm
If you've backed up the system databases (specifically master and msdb) and restored those, the system will be as it was at midnight. If you haven't backed up the system databases, there will be stuff missing like logins, jobs, backup history, linked servers.
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
August 6, 2009 at 8:31 pm
Ummm... not 100% sure but it seems to me that we needed to change the IP address, as well. Maybe that's because we also copied the Windows Server, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply