Backup/Restore vs Detach/Attach 2000 Databases to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach. I
backup and detach a database from SQL Server 2000 SP3 and then attach and
restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is
same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is
showing owned by the database owner of the database restored over but
syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
PeterMy best guess on #1 would be that the database size was increased because
the database was upgraded to 2005 and so the file expanded to accomidate the
necessary operations. Or because additional objects were added (system sp's,
tables or views) which are part of the 2005 functionality.
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> I'm trying to see the differences between Backup/Restore and
> Attach/Detach. I
> backup and detach a database from SQL Server 2000 SP3 and then attach and
> restore it to SQL Server 2005 SP1.
> The differences I noticed are:
> 1. The restored database has a much larger initial size (database size is
> same) for data and log.
> 2. The attached database has a last backup date
> 3. If the backup is restored over a database, the restored database is
> showing owned by the database owner of the database restored over but
> syslogins and sysusers do not match.
> I don't understand why #1 happens.
> Are there any other differences between Backup/Restore and Attach/Detach?
>
> Thanks,
> Peter
>|||Hi Mark,
If your guess is correct, should it happen to both cases: restored database
and attached database not just restored database.
Peter
"Mark Miller" wrote:
> My best guess on #1 would be that the database size was increased because
> the database was upgraded to 2005 and so the file expanded to accomidate the
> necessary operations. Or because additional objects were added (system sp's,
> tables or views) which are part of the 2005 functionality.
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> > I'm trying to see the differences between Backup/Restore and
> > Attach/Detach. I
> > backup and detach a database from SQL Server 2000 SP3 and then attach and
> > restore it to SQL Server 2005 SP1.
> >
> > The differences I noticed are:
> >
> > 1. The restored database has a much larger initial size (database size is
> > same) for data and log.
> > 2. The attached database has a last backup date
> > 3. If the backup is restored over a database, the restored database is
> > showing owned by the database owner of the database restored over but
> > syslogins and sysusers do not match.
> >
> > I don't understand why #1 happens.
> >
> > Are there any other differences between Backup/Restore and Attach/Detach?
> >
> >
> >
> > Thanks,
> >
> > Peter
> >
>
>

Backup/Restore vs Detach/Attach 2000 Databases to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach.
I
backup and detach a database from SQL Server 2000 SP3 and then attach and
restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is
same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is
showing owned by the database owner of the database restored over but
syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
PeterMy best guess on #1 would be that the database size was increased because
the database was upgraded to 2005 and so the file expanded to accomidate the
necessary operations. Or because additional objects were added (system sp's,
tables or views) which are part of the 2005 functionality.
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> I'm trying to see the differences between Backup/Restore and
> Attach/Detach. I
> backup and detach a database from SQL Server 2000 SP3 and then attach and
> restore it to SQL Server 2005 SP1.
> The differences I noticed are:
> 1. The restored database has a much larger initial size (database size is
> same) for data and log.
> 2. The attached database has a last backup date
> 3. If the backup is restored over a database, the restored database is
> showing owned by the database owner of the database restored over but
> syslogins and sysusers do not match.
> I don't understand why #1 happens.
> Are there any other differences between Backup/Restore and Attach/Detach?
>
> Thanks,
> Peter
>|||Hi Mark,
If your guess is correct, should it happen to both cases: restored database
and attached database not just restored database.
Peter
"Mark Miller" wrote:

> My best guess on #1 would be that the database size was increased because
> the database was upgraded to 2005 and so the file expanded to accomidate t
he
> necessary operations. Or because additional objects were added (system sp'
s,
> tables or views) which are part of the 2005 functionality.
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
>
>

Backup/Restore vs Attach/Detach 2000 Database to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.

The differences I noticed are:

1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.

I don't understand why #1 happens.

Are there any other differences between Backup/Restore and Attach/Detach?

Thanks,

Peter

I guess I don't understand what your #1 means. You seem to be saying that (1) the database is much larger if you restore it vs. an attach but (2) that the database size is the same for both. That seems contradictory...

As for other differences, the primary differences for most people relate to speed. If I'm just moving the log file from the F:\ drive to the E:\ drive, a backup/restore operation for a large database would take much, much longer than an detach/attach. I don't mean to be too simplistic as it sounds, from your testing, that you're very comfortable with SQL Server. Outside of that, there's not going to be a difference between RECOVERY, the orphaned users (#3 above), or anything else that I'm aware of. There's probably some niggling things here and there that are minor differences but nothing major that I know of.

/****************************************************

Check out our Sql Server 2005 and 2000 tutorials

****************************************************/