Configuration Backing up SQL Server in an environment with log shipping
Instant data file initialization
When you restore a database, filegroup, or database file, SQL Server zeroes the file space before beginning the restore operation. This can slow the total recovery time by as much as a factor of 2. To eliminate file initialization, run the MSSQLSERVER service under a Windows account that has been assigned the SE_MANAGE_VOLUME_NAME. For more information about eliminating file initialization, see the SQL Server 2005 and Windows 2003 documentation.
Using read-write and read-only filegroups
You can significantly reduce backup time and storage media needed if you periodically back up only read-write filegroups and keep a single backup of read-only filegroups, which is retained infinitely.
See “Adding schedules” on page 30 for information on setting the retention in a schedule contained in a NetBackup for SQL Server Policy.
Backing up SQL Server in an environment with log shipping
Log shipping is a SQL Server feature that may be employed to enhance the overall availability of your installation. It uses a primary server, which contains the active database, a monitor, and one or more secondary servers. Under log shipping, copies of the transaction log are supplied to the secondary servers on an on-going basis to the secondary servers. This allows each secondary server to be in a standby state in case the primary goes offline.
Many sites also use the secondary server to off-load certain activities from the primary in order to minimize its load. Howeve , a backup must not be performed on a secondary (or standby) server. Databases must always be backed up on the primary server and restored on the primary server. This requirement is based on Microsoft SQL Server restriction that is outlined in Microsoft Knowledge Base Article 311115. If an attempt is made to perform a backup on the secondary server, the result cannot be predicted. However, you may see a message in the
dbclient log similar to the one below: 16:33:26 [1208,2348] <16> CODBCaccess::LogODBCerr: DBMS MSG - ODBC message
. ODBC return
code <-1>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'Mumbo' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.>