June 20, 2006 at 6:51 pm
I'm very new to the DBA role (I'm a report writer that is being give the opportunity to learn the DBA role) and I need to increase the storage capacity on a non-production server. It currently has 3 disk bays (raid 5) w/ 73 GB hard disks, and I have three 146 GB replacement disks ready to go. I've created back-ups of the 10 user databases and saved off copies of the DTS files (all are on a network share). There are only about 6 user accounts to move.
My proposed maintenance looks like this -
*Use a procedure from a Knowledge Base article ("How to transfer logins and passwords between instances of SQL Server") to exec sp_help_revlogin to create login creation scripts for the new server.
*Take the server offline, yank the old disks, install the new disks, configure the raid, re-install the OS, join the domain, etc. Re-install SQL server.
*Use the login creation scripts to add the users back into SQL server (and bring over passwords and SIDs).
*Restore the backups of the user databases from the network share.
*Check for orphaned users (there are KB articles on identifying orphaned users)
Here are my questions:
Will this approach to moving the databases work? I've done research through technet and *hope* I've got the bases covered - since I don't have anyone experienced at the office I thought I'd try these forums, I've learned a ton from the newsletter. I don't believe I'll need to worry about moving/restoring any of the old system databases as long as I get the logins ahead of time. I'm researching attach/detach options in some of the KB articles as well but b/up restore looks pretty straightforward.
Time isn't a huge factor, this is a non-production server used for occasional testing, so it's a good opportunity for me to learn. It can be down for a day or two while I work through this.
Thanks in advance for any cautions / pitfalls I may have overlooked!
John
June 22, 2006 at 8:35 am
John,
What you propose would work just fine. When I am in this situation, I find it easier and quicker to stop SQL and copy the data and log files to another server or to tape. Once the new OS and SQL is installed on the new server, I would then stop SQL and replace the data and log files from the new install with the ones you copied off from the old build including master, msdb and all your user db's. This is assuming you have installed master in the same location that it was installed before.
If you take this approach, there is no need to restore databases, worry about log ins or DTS packages. Everything is already there.
Good Luck.
June 22, 2006 at 11:27 pm
Generally, yes, if you can copy off the database files (including transaction logs), while SQL Server is in a stopped state, it's relatively easy to get back to where you were after the reinstall by copying the database files back (including the system databases). However, it is good to have the backups and the user script on-hand, just in case.
As a proviso, before you go restoring files, either from backup or via copy, make sure the SQL Server is brought back to the same service pack / hot fix state as it is currently.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply