• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Completely rebuilding SQL 2005 server

I need to rebuild our Windows Server 2003 machine running SQL 2005.  I have done a lot of research (and several posts here) but I am still needing some guidance.  What is the best way to move the ENTIRE SQL 2005 database to a temporary location while I rebuild the Windows Server 2003 box, and then move the SQL database back?  I need to move the ENTIRE (including system databases) SQL database to a temp location and then restore the ENTIRE SQL database back.
0
r270ba
Asked:
r270ba
  • 6
  • 5
1 Solution
 
SireesCommented:
0
 
r270baAuthor Commented:
I have already found that link and it applies to SQL Server 2000 or earlier.  Also, I will be changing the OS from Windows Server 2003 Ent. to Std. and SQL Server from 2005 Ent. to Std.  I am not sure that link will work for me.
0
 
twoboatsCommented:
If it's going to have exactly the same file folder structure, I'd

- backup master
- stop sql
- put all database files & master backup somewhere for later use
- rebuild
- copy files into the 'same' places as before
- restore master

You're done
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
r270baAuthor Commented:
What about all of my other databases?
0
 
twoboatsCommented:
whaddya mean?

When you restore your master backup, it contains all info about your user databases. When sql starts, it looks in master, and starts up all the databases you had previously, and because you've put the db files in the same places they were before, you get everything back
0
 
twoboatsCommented:
Oh yes

- copy files into the 'same' places as before (not the master db ones)
0
 
r270baAuthor Commented:
Do I need to detach any of the non master database, or do I just move them to the temp location (after stopping SQL sserver)?  So I make a backup of the Master database b/c I cannot detach it correct?
0
 
twoboatsCommented:
You don't need to detach - shutting sql down closesd them neatly, ready for the process outlined.

"So I make a backup of the Master database b/c I cannot detach it correct?"

Yeah, effectively.
0
 
r270baAuthor Commented:
Ok then,  I will backup ALL my databases and move them to a safe location to begin with for good measures.  Then, once I shut down SQL, it will allow me to move those databases to a temp directory along with the backup I make for the master database.  

Next I rebuild Windows Server 2003, install SQL Server 2003, then move all the files back to their original directory structures.  I then will start SQL and restore my backed up master database.  This will complete the rebuild?

Now I want to make sure there will be no problem going from SQL Server 2005 Enterprise to SQL Server 2005 Standard?
0
 
twoboatsCommented:
"This will complete the rebuild?"

Yes - you need to be sure you know how to restore master db - this is documented step by step in Books Online

"Now I want to make sure there will be no problem going from SQL Server 2005 Enterprise to SQL Server 2005 Standard?"

I don't believe there's any problem
0
 
r270baAuthor Commented:
OK I am goign to try this tomorrow.  I will let you know how it turns out.
0
 
twoboatsCommented:
Remeber to keep copies of all files.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now