• Fix for single point of failure for metadata on primary filegroup for a database (mean that dump transaction with truncate_only fails in 7.0 when primary filegroup is lost). Data is kept in master and other files as well now.
• Allows you to restore one or more filegroups of the database somewhere else and then BCP out of it. The other files of the DB are marked as "offline" and are not restored.
• Enhanced support for controller based backups (i.e. EMC, also Compaq Storageworks are doing one shortly). Now has VDI interface to support split mirror or copy-on-write. Backup created now looks like a database backup and so can have logs etc. applied. Can be used to maintain a standby system. Can restore from it easily. History maintained in msdb. For a split mirror you would have 3-way mirroring so when it is split there is still a mirror for live data. Gives a backup with almost no overhead and taking just seconds. The backup can then be copied to tape as necessary. (All done in hardware – not going via the server so much faster and no overhead).
• Differential backup for files and filegroups now available
• 7.0 dumps can be loaded into 2000 (unless a create index was running at the time)
• Inability to restore log from a disk with a different physical sector size fixed.
• Differential backup now much faster. Looks at extent map to see what has changed rather than sweeping though every extent. For terabyte sized databases can make a real difference. Bitmap provides i/o map to drive skip-sequential i/o. When a database backup or file backup is done this bitmap is reset.
• New recovery model. Truncate Log/Select into options are gone. Replaced with three recovery modes which are set with ALTER DATABASE. All applications work with all modes and vice-versa.
• Simple. No media recovery possible. Equivalent to truncate log on checkpoint. Enough log kept for recovery/rollback of transactions and that’s all. No recovery to point in time or marked transaction available.
• Bulk-logged. As per select into/bcp/writetext. Just extent allocations for bulk operations are logged (e.g. Create Index, Bulk Load, Select into, writetext/updatetext). When log is backed up the extents that have been altered by the bulk operation are written out with the log too. No point in time or marked transaction recovery allowed to a point when a bulk operation was occurring.
• Normal/Full. Log shipping, database recovery to point in time etc. Everything is logged – bcp, create index, select into etc. Bulk operations are all fully logged – but does it a page at a time for performance.
• Default for Desktop and MSDE is SIMPLE. Full for Server and above. If trunc log is true then use simple. If false and select into true then bulk logged. If false and select into is false then will use full.
• Dumps can now occur during CREATE INDEX (restriction with 7.0)
• All operations are recoverable – e.g. ADD FILE (which wasn’t).
• Log and file backups don’t block each other any more and can run concurrently (still can’t backup database and log at the same time)
• If want to backup more than one file at a time then they must be put into the same backup statement.
• Tape appends faster than 7.0. 7.0 needed tape rewinds and checks on filemarks which slowed it down compared to SQL 6.5. With 2000 there is syntax to leave the tape where it is for a sequence of backups/restores.
• Online backups now have even less effect on performance than 7.0
• Now 64 backup devices (tape drives) compared to 32 for 7.0
• Password protection on backup media and backup sets. Data isn’t encrypted though. Prevents unauthorized append, but not media overwrite.
• User defined "marks" can be put into log so that recovery can be specified to this point. (i.e. make your own mark before running a script you are worried about). Recovery can include the transactions running at the time of the mark or roll them back (user definable). Can combine with a time. Marks are recorded in msdb.
• Can recover "related" databases – e.g. replication publisher/distributor – to a point in time. Use BEGIN DISTRIBUTED TRANSACTION <mark/tran name> WITH MARK. Can also exclude/include that tran on recovery. The transaction doesn’t actually have to do anything much – it can just be used to put the marks in. But it does have to involve all related databases. If it deadlocks then will retry x number of times and then rollback if not solved.
  For publisher/distributor the replication process will synch up the databases if necessary if cannot recover both to same point because of media failure.
• Log shipping built-into product. Uses restore with standby. Setup and managed via the maintenance plan wizard. Only 2000 to 2000. (Enterprise Edition?). For planned failover and for when data files haven’t been lost it is possible to switch back to the primary and apply the transaction logs from the secondary.
• Log shipping can be used from one primary to multiple secondaries. Variable settings for backup frequency, copy frequency and restore latency. Setup can create the backup database as well if you like.
• Log shipping – can change role from primary to secondary. Done using the log shipping monitor.
• For failing back don’t need to restore the whole database from new primary to old one. Uses BACKUP LOG … TO .. WITH norecovery/standby. Very useful for planned failovers.
• Log shipping with replication – either synchronous or semi-synchronous. Synchronous means subscribers don’t get ahead of standby – so updates aren’t put on the distributor by the log reader until the log is applied to the standby. Semi-synchronous has subscribers updated immediately, so if a failure occurs and the subscribers get out of synch and the transactions are sent down again from the standby then the distributor can be configured to ignore the errors (duplicates).
• Can switch recovery models – e.g. for overnight loads switch to bulk logged whilst bcp/create index. Then backup database and switch to full recovery.

SQL Server 2000 Preview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19