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.

5
  • Deleted previous comment when what I meant to say was, "How big are these databases?" Sep 30, 2011 at 13:19
  • 12 gigs in total
    – Kjensen
    Sep 30, 2011 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, 2011 at 13:27
  • That's a full backup or diff backup or a dump of the contents? Because 12 gig...10 minutes... O_o Sep 30, 2011 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, 2011 at 13:37

2 Answers 2

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.

3
  • That will forever with 50 databases. There must be a better way.
    – Kjensen
    Sep 30, 2011 at 13:11
  • You can create a SQL job to backup all of the databases at the same time.
    – joeqwerty
    Sep 30, 2011 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, 2011 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.

2
  • That will take forever with 50 databases. And of course I have backup.
    – Kjensen
    Sep 30, 2011 at 13:11
  • That's just the price of keeping 50 DBs.
    – Tablemaker
    Sep 30, 2011 at 13:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .