0

I am reinstalling Windows on a developer workstation, that runs SQL Server 2008R2 and contains a lot of SQL Server databases (~50).

After the reinstall, I want the SQL Server up and running with the same databases as easy as possible. It is just a workstation, so downtime is not an issue.

What is the easiest way to accomplish this?

Is it to attach the new SQL Server to the datafiles one at a time, or is there an easier way?

Edit:

I am looking for the EASIEST way to do this with a lot of databases. Doing it one at a time, whether with backup/restore or attach/detach is not what I am looking for.

And of course I have backups.

Community
  • 1
Kjensen
  • 1,039
  • 10
  • 28
  • 39
  • Deleted previous comment when what I meant to say was, "How big are these databases?" – Bart Silverstrim Sep 30 '11 at 13:19
  • 12 gigs in total – Kjensen Sep 30 '11 at 13:22
  • It's not too bad. I have a single DB that is 12 GB and the backup takes about 10 minutes on it's own. I'm not saying it will be amazingly fast, but it won't take as long as you'd think. – Tablemaker Sep 30 '11 at 13:27
  • That's a full backup or diff backup or a dump of the contents? Because 12 gig...10 minutes... O_o – Bart Silverstrim Sep 30 '11 at 13:28
  • I am not concerned with the time of the backup (I have a script that backs up all databases at once). It is the manual, tedious restoring one database at a time, that I am trying to avoid. – Kjensen Sep 30 '11 at 13:37

2 Answers2

2

  1. Backup and Restore

  2. Detach and reattach

Whether you backup the databases or detach them, make sure you copy/move them to removable storage before you rebuild the workstation.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • That will forever with 50 databases. There must be a better way. – Kjensen Sep 30 '11 at 13:11
  • You can create a SQL job to backup all of the databases at the same time. – joeqwerty Sep 30 '11 at 14:11
  • It seems to me that you're asking "what's an easy way to do this?", which is not the same as asking "what's the easiest way to do this?". The easiest way is not neccessarily easy. Create a SQL job that backups up all of the databases, rebuild workstation, restore databases, done. Other than that you could look at using a third party backup program (BackupExec comes to mind) that can back up all of the databases and restore them in a single job (1 backup job and 1 restore job). – joeqwerty Sep 30 '11 at 18:06
1

There's a few ways you can do this. You can either

  • Backup the databases to a removable storage and then restore them to the new DB installation on your workstation.
  • Or you can use the copy database wizard to copy the DB over to the new installation.

Just make sure you choose the option to not delete your current DB if you are using the copy option, as I assume that would be bad for you in case things go wrong.

You do have a backup, right?

All of this is easily done in SQL Server Management Studio.

I refer you here for a similar situation and more step by step instructions for a DB that could not be taken offline.

Community
  • 1
Tablemaker
  • 1,149
  • 1
  • 11
  • 23