SQL Server full upgrade

Question

our current SQL server is on windows 2003 32bit hence we have planned to rebuild the server from scratch in 64bit, however we have got about 200 databases running in the server so i may have to backup everything at ones and restore them after the 64bit windows installation. is there any easier way to restore all these databases in bulk or any safest options would appreciated.

Answer 1

Use SQL itself to generate your backup and restore scripts. You'll have to handle MASTER and MSDB first via the documented means to get user accounts, but otherwise, you do something like this (not tested code):

/* script to generate backup script */
    use master
go
select 'backup database ['+ [name] +'] to DISK=''\\myserver\mypath\' + [name] + '.BAK'' WITH COPY_ONLY, INIT'
    from sys.databases where name not in ('master','msdb','tempdb','model')

/* generate restore script */
    select 'restore database ['+ [name] +'] from DISK=''\\myserver\mypath\' + [name] + '.BAK'' '
    from sys.databases where name not in ('master','msdb','tempdb','model')

Run that in query analyzer, then paste the results of each part into a new query editor window and save. This is the safest way to do it I think, and the whole thing can be run scheduled and automated (with someone monitoring progress of course). You obvously need to shut off client access on the source server before these "final" backups. You will have to adjust the restore portion if your drive layout on the new server is signifgicantly different.

The fastest way would be to install SQL server with the same path and drive layouts, then simply shut down the source server and copy all the files over into the same locations on the new server (including master, etc.). This has worked for me in the past, but restoring master to a new server this way isn't totally trouble-free depending on evnironmentals (nor is it officially supported by MSFT I think). It should work as long as you have the same SQL Server edition, version, and service pack + patch level, even if the source is 32bit and destination is 64bit.