question

ASHOK 1 avatar image
ASHOK 1 asked

What is diffence between SQL server 2000 full backup and sql server 2005 full backup??

What is diffence between SQL server 2000 full backup and sql server 2005 full backup??
backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
I could imagine that the resource usage or internals of how the backup takes place have been improved upon. However, there is no real difference - a full backup is a full backup. You will not be able to restore a SQL 2005 backup to a SQL 2000 server, they are not backwards compatible. A SQL 2000 backup **can** be restored to a SQL 2005 server though.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
What happens during a full backup is that first are the datapages written to the backupdevice. During the backup, no new data is written to the datafile, instead all transactions are written to the transaction log. The parts of the transaction log that was written AFTER the backup started are written to the backupdevice, and then SQL Server writes these transactions to the datafile. This is - as far as I know - the same between SQL Server 2000 and SQL Server 2005.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
The database-level full backup appears to be the same process, but the more granular backups (full backup on single data file or filegroup) seem to have changed after SQL 2000. I get that by this quote from an [MSDN article][1] by Paul Randal: > The simplest kind of backup is a full > database backup. It's also possible to > do a full backup of a single data file > or filegroup. However, these are not > commonly used and as all the > principles discussed apply to them, > too, I'm going to focus on > database-level backups. But as of SQL > Server 2005, each of the more granular > backup types work exactly the same > (this was not true in SQL Server > 2000). Which from quick reading of a few articles on [MSDN][2] and [MSDN][3], it appears SQL 2005 added the ability to do [partial backups][4]. Which allowed for taking full filegroup backups under simple recover model. SQL 2000 did not allow this, you could only take filegroup backups under Full or Bulk Recovery. [1]: http://technet.microsoft.com/en-us/query/1279fc92-2577-45e5-a425-f56007280298 [2]: http://msdn.microsoft.com/en-us/library/aa196683(v=SQL.80).aspx [3]: http://msdn.microsoft.com/en-us/library/ms179401(v=SQL.90).aspx [4]: http://msdn.microsoft.com/en-us/library/ms191539(v=SQL.90).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
+1. Nice research!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.