attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
Tom
On Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent
|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.

> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Steen Schlter Persson (DK) wrote:[vbcol=seagreen]
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).