Skills required for Microsoft Server Administrator

Skills required for Microsoft Server Administrator
Microsoft has specified more than twenty-five objectives for the 70-297 test, which are grouped under four
topics. Following are the important areas in which an individual should possess good knowledge before taking
the 70-297 test:
1. Analyzing business and technical requirements of an organization.
2. Analyzing the impact of Active Directory on the existing technical environment.
3. Analyzing existing and planned business models and organizational structure.
4. Analyzing the structure of IT management.
5. Evaluating the company's existing and planned technical environments.
6. Analyzing existing network operating system implementation.
7. Analyzing the impact of Active Directory on a planned environment.
8. Analyzing the business requirement for client computer desktop management.
9. Analyzing security requirements for the Active Directory directory service.
10. Designing an Active Directory and domain structure.
11. Designing an Active Directory naming strategy including planning of DNS.
12. Designing an organizational unit structure and a site structure. Designing a replication strategy.
13. Designing a user and computer authentication strategy.
14. Designing the placement of operations masters, global catalog servers, domain controllers, and DNS
servers.
15. Identifying network topology and performance levels.
What is Active Directory Migration Tool (ADMT) ?
The Active Directory Migration Tool (ADMT) is used to migrate from an earlier implementation of Windows NT to
Windows Server 2003 or Windows 2000 Server. ADMT supports not only migration from Windows NT 4.0 to
Active Directory but also interforest and intraforest migrations. ADMT is designed to migrate an Active Directory
schema from one forest to another, regardless of whether a change in operating systems is involved.
ADMT 2.0 has many new features such as a command-line interface and a better interface to work with
Microsoft Exchange Server. ADMT also supports a user-account password migration.
How to restart Active Directory Domain Services?
Take the following steps to restart Active Directory Domain Services:
Start the Services console through Start > Administrative Tools > Services.
What is LDIFDE
LDIFDE is a command-line tool in the Windows Server 2003 operating system. It is used to create, modify, and
delete objects on computers running on Windows Server 2003 and Windows XP Professional. LDIFDE is also
used to extend the schema, export Active Directory user and group information to other applications or services,
and populate Active Directory with data from other directory services.
What is primary restore method?
The primary restore method is a type of backup restoration of the System State data. This method is used to
restore Active Directory data on a stand-alone domain controller. This method of restoration is also used in a
situation when a completely failed forest needs to be restored
What is replication?
Replication is a process through which the changes made to a replica on one domain controller are
synchronized to replicas on all other domain controllers in the network. Each domain controller stores three
types of replicas:
Schema partition: This partition stores definitions and attributes of objects that can be created in the forest.
Changes made in this partition are replicated to all the domain controllers in all the domains in the forest.
Configuration partition: This partition stores the logical structure of the forest deployment. It includes the
domain structure and replication topology. Changes made in this partition are replicated to all the domain
controllers in all the domains in the forest.
Domain partition: This partition stores all the objects in a domain. Changes made in this partition are replicated
to all the domain controllers within the domain.
Note: Windows supports a new type of directory partition named Application directory partition. This partition is
available only to the Windows 2003 (or above) domain controllers. The applications and services use this
partition to store application-specific data.
Creating, modifying, moving, or deleting an object triggers a replication between domain controllers.
Replications are of two types:
Intrasite: In the intrasite (within a site) replication, the data is not compressed, as the replication mostly uses
LAN connections. This saves the computer's CPU time of processing data. In the intrasite replication, the
replication partners poll each other periodically and notify each other when changes need to be replicated, and
then pull the information for processing. Active Directory uses the remote procedure call (RPC) transport
protocol for intrasite replication.
Intersite: As intersite (between sites) replication uses WAN connections, a large amount of data is compressed
to save bandwidth. For the same reason, the replication partners do not notify each other when changes need
to be replicated. Instead, administrators configure the replication schedule to update the information. Active
Directory uses the IP or SMTP protocol for intersite replication.
What is NLB Manager?
Network Load Balancing (NLB) Manager is a Windows Server 2008 GUI tool to manage NLB. NLB Manager is
used to add or remove hosts from an NLB cluster, to configure a cluster, and to manage a cluster. NLB Manager
can be installed by using Add Features within Server Manager
Group Policy and Group Policy Object (GPO)
What are group policies?
Group policies specify how programs, network resources, and the operating system work for users and
computers in an organization. They are collections of user and computer configuration settings that are applied
on the users and computers (not on groups). For better administration of group policies in the Windows
environment, the group policy objects (GPOs) are used.
What is GPO?
Group policy object (GPO) is a collection of group policy settings. It can be created using a Windows utility
known as the Group Policy snap-in. GPO affects the user and computer accounts located in sites, domains, and
organizational units (OUs). The Windows 2000/2003 operating systems support two types of GPOs, local and
non-local (Active Directory-based) GPOs.
Local GPOs
Local GPOs are used to control policies on a local server running Windows 2000/2003 Server. On each
Windows 2000/2003 server, a local GPO is stored. The local GPO affects only the computer on which it is
stored. By default, only Security Settings nodes are configured. The rest of the settings are either disabled or
not enabled. The local GPO is stored in the %systemroot%SYSTEM32GROUPPOLICY folder.
Non-local GPOs
Non-local GPOs are used to control policies on an Active Directory-based network. A Windows 2000/2003
server needs to be configured as a domain controller on the network to use a non-local GPO. The non-local
GPOs must be linked to a site, domain, or organizational unit (OU) to apply group policies to the user or
computer objects. The non-local GPOs are stored in %systemroot%SYSVOLPOLICIESGUID>ADM, where is the GPO's globally unique identifier. Two non-local GPOs are created by
default when the Active Directory is installed:
Default Domain Policy: This GPO is linked to the domain and it affects all users and computers in the domain.
Default Domain Controllers Policy: This GPO is linked to the Domain Controllers OU and it affects all domain
controllers placed in this OU.
What is ADS Automated Deployment Services?
Microsoft Windows Server 2003 Automated Deployment Services (ADS) is used by administrators to build and
manage very large and scaled out deployment of Windows servers. It includes a new set of imaging tools for
rapidly deploying Windows 2000 Server and Windows Server 2003 remotely. ADS offers improved
communication security and a reliable script execution framework. It uses the image-based deployment method
Under what conditions should Administrators create multiple forests?
Microsoft recommends the creation of multiple forests under the following conditions:
If Administrators do not trust each other: An Administrator can create a "denial of service" condition.
One can create this condition by rapidly creating or deleting objects, hence causing a large amount of
replication to the global catalog. This replication can waste network bandwidth and slow down global
catalog servers, as they spend time in processing replication. This condition forces administrators to
create multiple forests.
Organizations cannot agree on a forest change policy: Changes in schema, configuration, and the
addition of new domains to a forest have forest-wide impact. If organizations in a forest cannot agree on a
common policy, they cannot share the same forest, forcing administrators to create multiple forests.
If one wants to limit the scope of a trust relationship: All domains in a forest trust each other. In order
to prevent certain users from being granted permissions to certain resources, those users must be placed
in a forest different from the forest containing those resources. Administrators can use explicit trust
relationships to allow those users to be granted access to resources in specific domains, if required
What is GPMC tool?
The Group Policy Management Console (GPMC) is a tool for managing group policies in Windows Server
2003. It provides administrators a single consolidated environment for working on group policy-related
tasks. GPMC provides a single interface with drag-and-drop functionality to allow an administrator to
manage group policy settings across multiple sites, domains, or even forests. GPMC is used to back up,
restore, import, and copy group policy objects. It also provides a reporting interface on how group policy
objects (GPOs) have been deployed.
What is Performance Monitor?
Performance Monitor is used to get statistical information about the hardware and software components of
a server. Performance Monitor is used for the following:
· Monitor objects on multiple computers.
· Log data pertaining to objects on multiple computers, over time.
· Analyze the effects of changes made to a computer.
· Launch programs and send notifications when thresholds are reached.
· Export data for analysis in spreadsheet or database applications.
· Save counter and object settings for repeated use.
· Create reports for use in analyzing performance, over time.
What is System Monitor?
System Monitor is a Windows graphical tool for measuring the performance of a host or remote computer.
It is used to view reports on CPU load, memory usage, and interrupt rate, and the overall throughput of the
traffic on a network. Using System Monitor, administrators can perform the following functions:
· Create charts and reports to measure a computer's efficiency.
· Identify and troubleshoot possible issues, such as unbalanced resource use, insufficient hardware, or
poor program design.
· Plan for additional hardware needs.
System Monitor can also be used to monitor the resource use of specific components and program processes.
What is the SQL Server: General Statistics: User Connections counter?
The SQL Server: General Statistics: User Connections counter displays the number of user connections in
SQL Server. Its maximum value is 255. An increase in the value of the counter causes performance
problems and affects throughput. A Database Administrator should monitor this counter to resolve
performance issues.
What is Simple Mail Transfer Protocol (SMTP)?
Simple Mail Transfer Protocol (SMTP) is a protocol used for sending e-mail messages between servers. It
is mostly used to send messages from a mail client such as Microsoft Outlook to a mail server. Most of the
e-mail systems that send mails over the Internet use SMTP to send messages from one server to another.
Due to its limitations in queuing messages at the receiving end, it is generally used with either the POP3 or
IMAP protocol, which enables a user to save and download messages from the server.
What is bluescreen error?
Bluescreen error, sometimes called Blue Screen of Death (BSOD), is the condition that occurs when a
Windows computer fails to boot properly or quits unexpectedly. Microsoft refers these blue screens as
"Stop errors". There are several causes of the blue screen popping up. It can be due to a poorly written
device driver, bad memory, damaged registry, or usage of incompatible versions of DLLs. In Windows NT,
Windows 2000, Windows XP, Windows Server 2003, and Windows Vista, a blue screen of death occurs
when the kernel or a driver running in kernel mode encounters an error from which it cannot recover. This
is usually caused by an illegal operation being performed. The only safe action to overcome such
situations is to restart the computer.
What is the netstat command?
The netstat command displays protocol-related statistics and the state of current TCP/IP connections. It is
used to get information about the open connections on a computer, incoming and outgoing data, as well as
the ports of remote computers to which the computer is connected. The netstat command gets all this
networking information by reading the kernel routing tables in the memory.
What is IIS?
Internet Information Services (IIS) is a software service that supports Web site creation, configuration, and
management, along with other Internet functions. Microsoft Internet Information Services includes Network
News Transfer Protocol (NNTP), File Transfer Protocol (FTP), and Simple Mail Transfer Protocol (SMTP).
Clustering
A cluster is a group of two or more computers (servers) connected to provide fault tolerance and load
balancing. It is dedicated to run a specific application. Each server in a cluster is known as a node. The
failover and failback capabilities of a cluster bring the application downtime to zero.
Note: Server clustering is intended to provide high availability for applications and not for data.
Failover
In the cluster, each node or computer runs the same critical application. In case one computer fails, the
other computers detect the failure and take charge immediately. This phenomenon is called failover.
Failback
When the failed node returns back to the network, other nodes take notice and the cluster begins to use
the restored node again. This phenomenon is called failback.
Types of Clusters
Windows Server 2003 supports two types of clusters:
· Server clusters
· Network Load Balancing (NLB)
Server Clusters
In server clusters, all nodes are connected to a common data set, such as a storage area network. All nodes
have access to the same application data. Any of these nodes can process a request from a client at any time.
Nodes can be configured as either active or passive. Only an active node can process requests from clients. In
the event of a failure of the active node, the passive node takes charge and becomes active. Otherwise, the
passive node remains idle.
Server clusters are created for running applications that have frequently changing data sets and have longrunning
in-memory states. The applications such as database servers, e-mail and messaging servers, and file
and print services can be included in server clusters.
A server cluster is treated as a single destination for a client. It has its own name and IP address. This address is
different from the individual IP addresses of the servers in the cluster. Hence, when any server fails in the cluster,
the passive server becomes active. Clients send their requests to the server cluster address. Therefore, this
change over does not affect the functionality of the cluster.
Windows Server 2003 supports eight nodes in a cluster. However, Windows 2000 Server supports only two
nodes in a cluster.
Network Load Balancing
Network Load Balancing (NLB) is a type of clustering. It is used to provide high availability and reliability of the
application servers. NLB is configured for the applications that rarely change and that have very small data sets.
Web servers, FTP servers, VPN servers are the areas where NLB can be used successfully.
In the NLB cluster, all nodes are active and have separate identical data sets. Multiple servers (or nodes) are
used to distribute the load of processing data. Clients send the requests to the cluster, and then the clustering
software distributes incoming client requests among the nodes. If a node fails, the clients' requests are served by
other nodes. Network Load Balancing is highly scaleable. Both Windows 2003 and Windows 2000 operating
systems support NLB clusters of up to thirty-two nodes.
What is the Task Manager utility?
Task Manager is a utility that is used for managing applications, processes, and the general system
performance and also for viewing the networking and user statistics. The Task Manager utility is used to
run or end programs or applications. Administrators use this tool to quickly identify and terminate a rogue
application.
What is Task Manager utility?
The Task Manager utility provides information about programs and processes running on a computer. By
using Task Manager, a user can end or run programs, end processes, and display a dynamic overview of
his computer's performance. Task Manager provides an immediate overview of system activity and
performance.
What is DNS namespace?
DNS namespace is the hierarchical structure of the domain name tree. It is defined such that the names of
all similar components must be similarly structured, but similarly identifiable. The full DNS name must point
to a particular address. Consider the following image of DNS namespace of the Internet:
The salessrv1 and salessrv2 are host names of the hosts configured in the sales.ucertify.com domain. The
fully qualified domain name (FQDN) of the host salessrv1 is salessrv1.sales.ucertify.com. No two hosts can
have the same FQDN.
What is ADSIEdit ?
ADSIEdit is a Microsoft Management Console (MMC) snap-in that acts as a low-level editor for Active
Directory. It is a Graphical User Interface (GUI) tool. Network administrators can use it for common
administrative tasks such as adding, deleting, and moving objects with a directory service. The attributes
for each object can be edited or deleted by using this tool. ADSIEdit uses the ADSI application
programming interfaces (APIs) to access Active Directory. The following are the required files for using this
tool:
· ADSIEDIT.DLL
· ADSIEDIT.MSC
Regarding system requirements, a connection to an Active Directory environment and Microsoft Management
Console (MMC) is necessary.
What are group scopes?
The scope of a group defines two characteristics:
· It determines the level of security applying to a group.
· It determines which users can be added to a group.
Windows Server 2003 supports the following scopes:
Domain Local: Domain local groups are used to assign permissions to local resources such as files and printers.
Members can come from any domain.
Global: Members of this group can access resources in any domain. Members can only come from the local
domain.
Universal: Members can be added from any domain in the forest. Members can access resources from any
domain. Universal groups are used for managing the security across domains. Universal groups can also contain
global groups. Universal groups are only available in the domains having functional level Windows 2000 native or
Windows Server 2003.
What is IPv6?
IP addressing version 6 (IPv6) is the latest version of IP addressing. IPv6 is designed to solve many of the
problems that were faced by IPv4, such as address depletion, security, auto-configuration, and
extensibility. With the fast increasing number of networks and the expansion of the World Wide Web, the
allotted IP addresses are depleting rapidly, and the need for more network addresses is arising. IPv6
solves this problem, as it uses a 128-bit address that can produce a lot more IP addresses. These
addresses are hexadecimal numbers, made up of eight octet pairs. An example of an IPv6 address is
45CF: 6D53: 12CD: AFC7: E654: BB32: 543C: FACE.
What is DSMOD?
DSMOD is a command-line utility that is used to modify existing objects, such as users, computers,
groups, servers, OUs etc., in Active Directory
What is NTDSUTIL utility?
NTDSUTIL.EXE is a command-line tool that is used to manage Active Directory. This utility is used to
perform the following tasks:
· Performing database maintenance of Active Directory.
· Managing and controlling operations master roles.
· Removing metadata left behind by domain controllers.
Note:The NTDSUTIL utility is supposed to be used by experienced administrators.
What is System File Checker utility?
The System File Checker utility is used to verify the integrity of the operating system files, to restore them if
they are corrupt, and to extract compressed files (such as drivers) from installation disks. It can also be
used to backup the existing files before restoring the original files.
What is SCHTASKS tool?
The SCHTASKS tool is used to schedule commands and programs to run periodically or at a specific time.
It adds and removes tasks from the schedule, starts and stops tasks on demand, and displays and
changes scheduled tasks.
What is CHKDSK?
CHKDSK is a command-line tool used to scan and repair volumes on the hard disk for physical problems
such as bad blocks. It also repairs volumes for logical structure errors such as lost clusters, cross-linked
files, or directory errors.
Network Configuration and Management Utilities
Administrators use various utilities to configure and manage networks. Following are some commonly used
utilities:
WINIPCFG: WINIPCFG is a Windows 9x Internet Protocol (IP) configuration utility used to display all
current TCP/IP network configuration values for a computer running Microsoft TCP/IP. Network
configuration values include the current IP address allocated to the computer and other useful data about
TCP/IP allocation. This utility is of particular use on networks using Dynamic Host Configuration Protocol
(DHCP), allowing users to determine which TCP/IP configuration values have been configured by DHCP.
IPCONFIG: IPCONFIG is a command-line utility used to display current TCP/IP network configuration
values, and to update or release the Dynamic Host Configuration Protocol (DHCP) allocated leases. It is
also used to display, register, or flush Domain Name System (DNS) names.
NSLOOKUP: NSLOOKUP is a utility for diagnosing and troubleshooting Domain Name System (DNS)
problems. It performs its function by sending queries to the DNS server and obtaining detailed responses
at the command prompt. This information can be useful for diagnosing and resolving name resolution
issues, verifying whether or not the resource records are added or updated correctly in a zone, and
debugging other server-related problems. This utility is installed along with the TCP/IP protocol through the
Control Panel.
PING: PING is a command-line utility used to test connectivity with a host on a TCP/IP-based network.
This is achieved by sending out a series of packets to a specified destination host. On receiving the
packets, the destination host responds with a series of replies. These replies can be used to determine if
the network is working properly.
TRACERT: TRACERT is a route-tracing Windows utility that displays the path an IP packet takes to reach
its destination. It shows the Fully Qualified Domain Name (FQDN) and the IP address of each gateway
along the route to the remote host.
PATHPING: PATHPING is a command-line utility that pings each hop along the route for a set period of
time and shows the delay and packet loss along with the tracing functionality of TRACERT, which helps
determine a weak link in the path.
NBTSTAT: NBTSTAT is a Windows utility used to check the state of current NetBIOS over TCP/IP
connections, update the NetBIOS name cache, and determine the registered names and scope IDs.
NETSTAT: NETSTAT is a command-line utility that displays protocol related statistics and the state of
current TCP/IP connections. It is used to obtain information about the open connections on a computer,
incoming and outgoing data, and also the ports of remote computers to which the computer is connected.
The NETSTAT command gets all this networking information by reading the kernel routing tables in the
memory.
TELNET: TELNET is a command-line connectivity utility that starts terminal emulation with a remote host
running the Telnet Server service. TELNET allows users to communicate with a remote computer, offers
the ability to run programs remotely, and facilitates remote administration. The TELNET utility uses the
Telnet protocol for connecting to a remote computer running the Telnet server software, to access files. It
uses TCP port 23 by default.
What is a certificate?
A certificate is a digital representation of information that identifies authorized users on the Internet and intranets. It
can be used with applications and security services to provide authentication. Certificates are issued by certification
authorities (CAs).
What is a nonclustered index?
A nonclustered index has the same B-tree structure as the clustered index. The index consists of a root page,
intermediate levels, and a leaf level. The leaf level of a nonclustered index does not contain the actual data. It contains
pointers to the data that is stored in the data pages. A nonclustered index does not physically rearrange the data.
Monitoring Physical Server Performance
SQL Server 2005 can be installed on a Windows 2000 or Windows 2003 server computer. A database administrator is
always concerned about the performance of the SQL Server database engine and the server computer. Database
Administrators monitor the performance of the server using various tools to analyze performance and resolve
performance issues.
System Monitor: System Monitor is a tool used to monitor the performance of the server. It gives information about the
resources that are under pressure. The values of various counters in System Monitor indicate which resource is under
pressure. Performance deterioration can be diagnosed by setting performance alerts. These alerts show the increase or
decrease in a counter value with respect to the pre-defined value. Normally the counters are monitored for a period of
24-hours. If an error occurs, a message regarding the error can either be sent to the administrator or written to the
Application log. Log files can be saved in various formats such as text file, binary file, or SQL database file.
The counters that are to be measured in order to resolve performance issues are as follows:
· Memory: Pages/sec
· Memory: Available Bytes
· SQL Server: Buffer Manager: Buffer Cache Hit Ratio
· Physical Disk: Disk Reads/sec
· Physical Disk: Disk Writes/sec
· Physical Disk: %Disk Time
· Physical Disk: Avg: Disk Queue Length
· Physical Disk: % Free Space
· Logical Disk: %Free Space
· Processor: %Processor Time
· System: Processor Queue Length
· Network Interface: Bytes Received/sec
· Network Interface: Bytes Sent/sec
· Network Interface: Bytes/sec
· Network Interface: Output Queue Length
· SQL Server: General: User Connection
Tip for server roles.
There are eight server roles. These roles are as follows:
· sysadmin
· dbcreator
· bulkadmin
· diskadmin
· processadmin
· serveradmin
· setupadmin
· securityadmin
What is virus?
A virus is a malicious program. A computer virus passes from one computer to another in the same way as a biological
virus passes from one person to another. Most viruses are written with a malicious intent, so that they can cause
damage to programs and data in addition to spreading themselves. Viruses infect existing programs to alter the
behavior of programs, actively destroy data, and perform actions on storage devices that render their stored data
inaccessible.
Computer viruses attack the software of a computer such as operating systems, data files, application software, and emails.
However, viruses do not affect the computer hardware
Network Protocols
Protocol is a set of rules and conventions by which two computers pass messages across a network. Sets of standard protocols
facilitate communication between the computers in a network having different types of hardware and software. Both the sender
and the receiver computers must use exactly the same set of protocols in order to communicate with each other. A protocol can
lay down the rules for the message format, timing, sequencing, and error handling.
The description of the primary protocols in the suite is as follows:
Protocol
Name
Description
IP Internet Protocol (IP) is a connectionless network-layer protocol that is the primary carrier of data on a TCP/IP network.
TCP Transmission Control Protocol (TCP) is a reliable, connection-oriented protocol operating at the transport layer. This protocol
can transmit large amounts of data. Application-layer protocols, such as HTTP and FTP, utilize the services of TCP to transfer
files between clients and servers.
UDP User Datagram Protocol (UDP) is a connectionless, unreliable transport-layer protocol. UDP is used primarily for brief exchange
of requests and replies.
Telnet Telnet is a protocol that enables an Internet user to log onto and enter commands on a remote computer linked to the
Internet, as if the user were using a text-based terminal directly attached to that computer.
FTP File Transfer Protocol (FTP) is a primary protocol of the TCP/IP protocol suite, used to transfer text and binary files between
computers over a TCP/IP network.
SMTP Simple Mail Transfer Protocol (SMTP) is used for transferring or sending e-mail messages between servers.
PPP: Point-to-Point Protocol (PPP) is a set of industry-standard framing and authentication protocols included with Windows
remote access to ensure interoperability with third-party remote access software. It is a data link-layer protocol designed to
create a direct connection between two computers, typically using telephone lines.
POP3: Post Office Protocol version 3 (POP3) is a protocol used for retrieving e-mail messages. The POP3 servers allow access to
a single Inbox in contrast to IMAP servers that provide access to multiple server-side folders.
IMAP: Internet Message Access Protocol (IMAP) is a protocol for receiving e-mail messages. It allows an e-mail client to access
and manipulate a remote e-mail file without downloading it to the local computer. It is used mainly by the users who want to
read their e-mails from remote locations.
PPTP: Point-to-Point Tunneling Protocol (PPTP) is an encryption protocol used to provide secure, low-cost remote access to
corporate networks through public networks such as the Internet. Using PPTP, remote users can use PPP-enabled client
computers to dial a local ISP and connect securely to the corporate network through the Internet.
HTTP: Hypertext Transfer Protocol (HTTP) is a client/server TCP/IP protocol used on the World Wide Web (WWW) to display
Hypertext Markup Language (HTML) pages. HTTP defines how messages are formatted and transmitted, and what actions Web
servers and browsers should take in response to various commands. For example, when a client application or browser sends a
request to the server using HTTP commands, the server responds with a message containing the protocol version, success or
failure code, server information, and body content, depending on the request. HTTP uses TCP port 80 as the default port.
HTTPS: Hypertext Transfer Protocol Secure (HTTPS) protocol is a protocol used in the Uniform Resource Locator (URL) address
line to connect to a secure site. If a site has been made secure by using the Secure Sockets Layer (SSL), HTTPS (instead of HTTP
protocol) should be used as a protocol type in the URL.
ARP: Address Resolution Protocol (ARP) is a network maintenance protocol of the TCP/IP protocol suite. It is responsible for the
resolution of IP addresses to media access control (MAC) addresses of a network interface card (NIC). The ARP cache is used to
maintain a correlation between a MAC address and its corresponding IP address. ARP provides the protocol rules for making this
correlation and providing address conversion in both directions. ARP is limited to physical network systems that support
broadcast packets.
ICMP: Internet Control Message Protocol (ICMP) is a maintenance protocol and is normally considered a part of the IP layer.
ICMP messages are encapsulated within IP datagrams, so that they can be routed throughout an internetwork.
Internet Message Access Protocol 4 (IMAP4): It is an e-mail message retrieval protocol that allows e-mail clients to retrieve email
messages from e-mail servers. IMAP4 has the following advantages over the POP3 protocol:
· IMAP4 can be used to download only specific mails from the mail server, while POP3 downloads all the mails from
the mail server at a time.
· IMAP4 can download only a part of the message (e.g., the header) initially. Then depending upon the user, the
entire message can be downloaded afterwards. However, POP3 downloads the entire message at a time.
· IMAP4 only marks a message as deleted as soon as it is being read. The message will then be deleted as soon as
the user logs off, or sends the EXPUNGE command to the mail server.
· IMAP4 supports server side storage. Hence, the location of the user is insignificant. However, POP3 uses a local
client application to read the mails.
· Since IMAP4 stores messages on the server side, the user does not have to bother about fault tolerance and
system crashes. When the POP3 protocol is used, the messages once downloaded from the server are stored
locally and can be lost if the local system crashes.
· IMAP4 allows a user to create multiple mailboxes on multiple servers under the same user name. The user can
personalize these mailboxes for receiving specific kinds of mails in each mailbox. However, POP3 allows only a
single user account to be configured.
· Changes made to a mail are propagated to the IMAP4 server. This feature is not available under POP3 protocol.
However, there are some disadvantages of IMAP4 over the POP3 protocol, which are as follows:
· If the connection with the mail server drops while reading a mail, it has to be re-established. On the other hand,
POP3 downloads the entire mail at a time. Hence, if the connection with the mail server is dropped at the time of
reading a mail, it does not affect the reading.
· The POP3 protocol is mostly supported by the commercially available mail servers.
· Since the mails in IMAP4 are stored on the server, the space storage management is a primary concern on such
mail servers.
IP Addressing
IP Addresses are used to uniquely identify the computers in a network, so each computer must have its own unique IP address.
An IP address consists of two parts: a network identifier and a host identifier. The network identifier denotes the type of
network, and the host identifier is a unique number of a particular computer. So in a particular type of network, each node has
the same network id and a host id, which are unique.
The type of IP address also depends on the subnet mask, which is used to determine which part of the IP address denotes the
network id and which part is the host id. For example, if the IP address is 192.168.1.200 and the subnet mask is 255.255.255.0,
the network id will be 192.168.1 and the host id will be 200. In the same way, if the subnet mask is 255.255.0.0, the network id
will be 192.168 and the host id will be 1.200. If the subnet mask is 255.0.0.0, the network id will be 192 and the host id will be
168.1.200.
There are two versions of IP addressing, the commonly used IPv4 and the latest version known as IPv6. They have been
discussed in detail in the following paragraphs.
IPv4
IP Address
In this version of IP addressing, an IP address is of 32 bits in length, and is divided into four 8 bit decimal values known as
octets. In these types of IP addresses, the leftmost bit has the value of 128, which is followed by 64, 32, 16, 8, 4, 2, and 1. An IP
address can have values from 0 to 255 because each bit can be either a 0 or a 1. So if all the bits are 1, the value will be 255;
and if all the bits are 0, the value will be 0.
Subnet Mask
A subnet mask determines which part of the IP address denotes the network id and which part is the host id. It is also a 32-bit
number, which is expressed in decimal format. The subnet mask is assigned according to the class of IP address used.
IP Address Classes
The Internet Assigned Number Authority registers the IP addresses used in the networks to ensure their uniqueness. IP
addresses have been divided into five groups or classes known as IP Address classes. Each class of IP address has a particular
subnet mask associated with it. The five classes of IP addresses are class A, B, C, D and E, in which class D is reserved for
multicast addressing and class E is reserved for future use. So only classes A through C are used for assigning IP addresses to
client computers.
· In class A addresses, only the first octet is used to define the network id, and the rest are used for the host id. It
has the address range from 1 to 126 and so it can have only 126 numbers of networks. The number of hosts
possible in these types of networks is 16,777,214. It uses the subnet mask 255.0.0.0.
· In class B networks, the first two octets represent the network id and the rest are the host id. It has a range of
128-191 and can have 16384 networks with 65,534 hosts. The standard subnet mask assigned to these IP
addresses is 255.255.0.0.
· In class C addresses, the first three octets are used to represent the network id. It has a range of 192-223 and can
have 2,097,152 networks with 253 hosts. The subnet mask associated with it is 255.255.255.0.
· Class D addresses have an address range of 224-239, and class E addresses have an address range of 240-255.
Default Gateway
Default gateway is a TCP/IP configuration option, used to communicate with TCP/IP nodes on remote network segments. At least
one interface must be configured with the IP address of a default gateway.
IPv6
The current version of IP addressing (i.e., IPv4) has its limitations. With the fast increasing number of the networks and the
expansion of the World Wide Web, the IP addresses allotted are finishing fast and the need for more network addresses has
arisen. IPv6 can solve this problem, as it uses a 128-bit address that can produce a lot more IP addresses. These addresses are
hexadecimal numbers, made up of eight octet pairs. An example of an IPv6 address can be 45CF: 6D53: 12CD: AFC7: E654:
BB32: 543C.
Subnetting
Subnets are subdivisions of an IP address network, used for creating smaller broadcast domains and for better utilization of the
bits in the host ID. Through subnetting, the host id portion of an IP address can be used to create more networks than by using
the default subnet mask.
Suppose that a company has been assigned a Class C IP address 200.1.1.0, and the standard subnet mask is 255.255.255.0.
This means that the network id will be 200.1.1 and the total number of hosts will be 254. The company has two departments:
production and sales. Members of the production department do not need to access the computers of the sales department. So
it is better to have separate networks for both the departments for better security and manageability. Through subnetting, the
bits from the host id portion can be used to create more networks, which will work as separate networks.
Public and Private Networks
Network can be differentiated as private and public. A public network is a network, which can be accessed by anyone from the
general public, an example being the Internet. In contrast, a private network is accessible only by those people who have
special permissions on that particular network. An example of a private network is a network within an organization such as a
company, a hospital, or a college.
Public and private networks have different types of IP addressing schemes. Addresses on the Internet are assigned by the IANA
(Internet Assigned Numbers Authority), which assigns them to the Internet Service Providers (ISPs), who then distribute them to
the users. Apart from the public address, some addresses have been reserved for the private networks. These are not available
for general public and are used in private networks.
Some addresses from each of the classes A, B, and C have been assigned for use by private networks. The address range for
class A addresses is from 10.0.0.0 to 255.255.255, for class B addresses it is from 172.6.0.0 to 172.31.255.255, and for class C
addresses, it is from 192.168.0.0 to 192.168.255.255.
IP Addressing Methods:
Static Addressing
In static addressing, every computer is assigned an IP address manually. It is not preferred in large networks, which have lots of
hosts, because the chance of assigning duplicate addresses will be more. This will result in a conflict of IP addresses and
deterioration of the speed. Also it is time consuming, as every system is configured manually and if some changes are to be
made afterwards, it will consume a lot of time doing it manually for every computer.
Dynamic Addressing
In this type of addressing scheme, the IP addresses are assigned automatically by the use of Dynamic Host Configuration
Protocol (DHCP) to all the computers in the network. This results in much less burden on the network administrator and faster
configuration of the network. This type of addressing needs a DHCP server, to which a range of IP addresses is allotted. The
DHCP server automatically assigns any address from the range of IP addresses defined to the workstations on the network.
APIPA
Automatic private IP addressing (APIPA) is a feature of Windows XP TCP/IP that configures a unique IP address for each
computer on a network when the TCP/IP protocol is configured for dynamic addressing and a DHCP server is not available or
offline. The key function of APIPA is to allow resources to be available even if the DHCP server is offline. APIPA addresses are
always in the range of 169.254.0.1 and 169.254.255.254 and use a subnet mask of 255.255.0.0.
When a user configures a TCP/IP connection to obtain an IP address automatically, by default the computer tries to find a DHCP
server for obtaining the address. The user obtains the address if the computer finds the DHCP server. If it does not find the
DHCP server, the computer uses APIPA to configure a unique IP address for the computers of a network. Since APIPA does not
offer a gateway address, it can never be used on the Internet, and the clients using APIPA cannot access resources outside the
local subnet.
TCP/UDP Ports
The default TCP/UDP ports associated with TCP/IP protocol or applications are as under:
Protocol Port
HTTP 80
HTTPS 443
POP3 110
FTP 20
FTP 21
IMAP4 143
SMTP 25
NNTP 119
NTP 123
DNS 53
TFTP 69
Telnet 23
SSH 22
What are cluster configurations?
Server clusters using the Cluster service can be set up as one of the following three different cluster configurations:
1. Single Node server clusters: They can be configured with or without external cluster storage devices. For
Single Node server clusters without an external cluster storage device, the local disk is configured as the cluster
storage device.
2. Single Quorum Device server clusters: They can have two or more nodes and are so configured as to attach
every node to one or more shared storage devices, such as an external array of Small Computer System Interface
(SCSI) disks. The cluster configuration data is stored on a single cluster storage device, also known as the quorum
disk.
3. Majority Node Set server clusters: They can have two or more nodes, but nodes might not be attached to one
or more cluster storage devices. The cluster configuration data is stored on multiple disks across the cluster, and
the Cluster service guarantees that this data is kept consistent across the disks.
However, server clusters using the Cluster service are set up depending on the specific needs for failovers, in which application
services are moved to another node in the cluster.
What is N+I Hot Standby Server?
N+I Hot Standby Server is one of the failover models. It is commonly referred to as an Active/Passive mode. In
an active/passive mode, the active nodes handle all client requests, whereas the passive nodes monitor the
active nodes. In N+I Hot Standby Server, N denotes the number of active nodes, and I refers to the number of
passive nodes. This model has a drawback that the server resources remain idle for a long time and are
utilized only when another server fails. However, it is the most scalable and reliable model.
What is failover?
Failover is a term associated with cluster services. It refers to the ability of a server to immediately start servicing the
requests if a primary server fails. If the application services in a cluster-node fail, the Cluster Service generally tries to
restart them on the same node. If the services do not start, then it moves the services to another node in the cluster
and restarts them on that node.
Windows Server 2003 Active Directory and Network Infrastructure
Windows Server 2003 Active Directory is a centralized database that stores the collection of information about all the
resources available on the Windows Server 2003 domain. It is a hierarchical representation of all the objects and their
attributes available on the network. It enables administrators to manage the network resources, i.e., computers, users,
printers, shared folders, etc., in an easy way. The logical structure represented by Active Directory consists of forests,
trees, domains, organizational units, and individual objects. This structure is completely independent from the physical
structure of the network, and allows administrators to manage domains according to the organizational needs without
bothering about the physical network structure.
Following is the description of all logical components of the Active Directory structure:
1. Forest: A forest is the outermost boundary of an Active Directory structure. It is a group of multiple domain trees
that share a common schema but do not form a contiguous namespace. It is created when the first Active
Directory-based computer is installed on a network. There is at least one forest on a network. The first domain in a
forest is called a root domain. It controls the schema and domain naming for the entire forest. It can be separately
removed from the forest. Administrators can create multiple forests and then create trust relationships between
specific domains in those forests, depending upon the organizational needs.
2. Trees: A hierarchical structure of multiple domains organized in the Active Directory forest is referred to as a tree.
It consists of a root domain and several child domains. The first domain created in a tree becomes the root
domain. Any domain added to the root domain becomes its child, and the root domain becomes its parent. The
parent-child hierarchy continues until the terminal node is reached. All domains in a tree share a common
schema, which is defined at the forest level. Depending upon the organizational needs, multiple domain trees can
be included in a forest.
3. Domains: A domain is the basic organizational structure of a Windows Server 2003 networking model. It logically
organizes the resources on a network and defines a security boundary in Active Directory. The directory may
contain more than one domain, and each domain follows its own security policy and trust relationships with other
domains. Almost all the organizations having a large network use domain type of networking model to enhance
network security and enable administrators to efficiently manage the entire network.
4. Objects: Active Directory stores all network resources in the form of objects in a hierarchical structure of
containers and subcontainers, thereby making them easily accessible and manageable. Each object class consists
of several attributes. Whenever a new object is created for a particular class, it automatically inherits all attributes
from its member class. Although the Windows Server 2003 Active Directory defines its default set of objects,
administrators can modify it according to the organizational needs.
5. Organizational Unit (OU): It is the least abstract component of the Windows Server 2003 Active Directory. It
works as a container into which resources of a domain can be placed. Its logical structure is similar to an
organization's functional structure. It allows creating administrative boundaries in a domain by delegating
separate administrative tasks to the administrators on the domain. Administrators can create multiple
Organizational Units in the network. They can also create nesting of OUs, which means that other OUs can be
created within an OU.
In a large complex network, the Active Directory service provides a single point of management for the administrators by
placing all the network resources at a single place. It allows administrators to effectively delegate administrative tasks as well
as facilitate fast searching of network resources. It is easily scalable, i.e., administrators can add a large number of resources to
it without having additional administrative burden. It is accomplished by partitioning the directory database, distributing it
across other domains, and establishing trust relationships, thereby providing users with benefits of decentralization, and at the
same time, maintaining the centralized administration.
The physical network infrastructure of Active Directory is far too simple as compared to its logical structure. The physical
components are domain controllers and sites.
1. Domain Controller: A Windows 2003 server on which Active Directory services are installed and run is called a
domain controller. A domain controller locally resolves queries for information about objects in its domain. A
domain can have multiple domain controllers. Each domain controller in a domain follows the multimaster model
by having a complete replica of the domain's directory partition. In this model, every domain controller holds a
master copy of its directory partition. Administrators can use any of the domain controllers to modify the Active
Directory database. The changes performed by the administrators are automatically replicated to other domain
controllers in the domain.
However, there are some operations that do not follow the multimaster model. Active Directory handles these
operations and assigns them to a single domain controller to be accomplished. Such a domain controller is
referred to as operations master. The operations master performs several roles, which can be forest-wide as well
as domain-wide.
o Forest-wide roles: There are two types of forest-wide roles:
Schema Master and Domain Naming Master. The Schema Master is responsible for maintaining the
schema and distributing it to the entire forest. The Domain Naming Master is responsible for
maintaining the integrity of the forest by recording additions of domains to and deletions of domains
from the forest. When new domains are to be added to a forest, the Domain Naming Master role is
queried. In the absence of this role, new domains cannot be added.
o Domain-wide roles: There are three types of domain-wide roles: RID Master, PDC Emulator, and
Infrastructure Master.
Domain controllers can also be assigned the role of a Global Catalog server. A Global Catalog is a
special Active Directory database that stores a full replica of the directory for its host domain and the
partial replica of the directories of other domains in a forest. It is created by default on the initial
domain controller in the forest. It performs the following primary functions regarding logon capabilities
and queries within Active Directory:
1. It enables network logon by providing universal group membership information to a domain
controller when a logon request is initiated.
2. It enables finding directory information about all the domains in an Active Directory forest.
A Global Catalog is required to log on to a network within a multidomain environment. By providing
universal group membership information, it greatly improves the response time for queries. In its
absence, a user will be allowed to log on only to his local domain if his user account is external to the
local domain.
2. Site: A site is a group of domain controllers that exist on different IP subnets and are connected via a fast and
reliable network connection. A network may contain multiple sites connected by a WAN link. Sites are used to
control replication traffic, which may occur within a site or between sites. Replication within a site is referred to as
intrasite replication, and that between sites is referred to as intersite replication. Since all domain controllers
within a site are generally connected by a fast LAN connection, the intrasite replication is always in uncompressed
form. Any changes made in the domain are quickly replicated to the other domain controllers. Since sites are
connected to each other via a WAN connection, the intersite replication always occurs in compressed form.
Therefore, it is slower than the intrasite replication.
What are domain functional levels?
The domain functional levels are the various states of a domain, which enable domain-wide Active Directory features
within a network environment. Domain levels are the same as domain modes in Windows 2000. Windows supports four
types of functional levels:
1. Windows 2000 Mixed: This is the default domain functional level. When a first domain controller is installed or
upgraded to Windows 2003, the domain controller is configured to run in the Windows 2000 mixed functional
level. In this mode, domain controllers running the following operating systems are supported:
o Windows NT Server 4.0
o Windows 2000 Server
o Windows Server 2003
2. Windows 2000 Native: In this level, domain controllers running Windows 2000 and Windows 2003 can interact
with each other. No domain controller running a pre-Windows 2000 version is supported in this functional level of
the domain.
3. Windows Server 2003 Interim: This functional level allows a Windows Server 2003 domain controller to
interact with domain controllers in the domain running Windows NT 4.0 or Windows Server 2003. This functional
level is used to upgrade the first Windows NT domain to a new forest.
Note: Windows Server 2003 interim functional level does not support domain controllers running Windows 2000.
4. Windows Server 2003:This functional level of domain allows a Windows Server 2003 domain controller to
interact only with the domain controllers running Windows 2003 in the domain. A domain level can be raised to
Windows Server 2003 only when all the domain controllers in the domain are running Windows Server 2003
What is site?
A site is a collection of one or more well-connected (usually a local area network) TCP/IP subnets. The network between
the subnets must be highly reliable and fast (512 Kbps and higher). Although the sites are defined on the basis of
location, they can be spanned over more than one location. A site structure corresponds to the physical environment,
whereas a domain is the logical environment of the network. A site can contain single or multiple domains, and a
domain can contain single or multiple sites.
Sites are created to physically group the computers and resources for optimizing the network traffic. Administrators can
configure Active Directory access and replication technology to take advantage of the physical network by configuring
sites. When a user logs on to a network, the authentication request searches for the domain controllers in the same site
where the user is located. A site prevents the network traffic from traveling on wide area network (WAN) links that are
slow.
What is DCDIAG tool? AD Trubleshooting tool.
Domain Controller Diagnostic (DCDIAG) is a diagnostic tool that is used to analyze the domain controllers in a forest to
report problems or issues. The scope of this tool covers the functions of the domain controllers and interactions across
an entire enterprise. The DCDIAG tool is used to diagnose the domain controller status for the following issues:
· Connectivity
· Replication
· Integrity of topology
· Permissions on directory partition heads
· Permissions of users
· Functionality of the domain controller locator
· Consistency among domain controllers in the site
· Verification of trusts
· Diagnosis of replication latencies
· Replication of trust objects
· Verification of File Replication service
· Verification of critical services
Note: DCDIAG is an analyzing tool, which is mostly used for the reporting purposes. Although this tool allows specific tests to be
run individually, it is not intended as a general toolbox of commands for performing specific tasks.
What is NETDOM?
NETDOM is a command-line tool that allows management of Windows domains and trust relationships. It is used for
batch management of trusts, joining computers to domains, verifying trusts, and secure channels
Windows 2003 system services?
Windows Server 2003 comes with many system services that have different functionalities in the operating system.
When Windows Server 2003 is first installed, the default system services are created and are configured to run when
the system starts
Example :
Following are some important system services of Windows Server 2003:
Alerter
Automatic Updates
Cluster Service
DHCP
Distributed File System
DNS Client service
DNS Server service
Event Log service
Remote Installation
Remote Procedure Call (RPC)
Routing and Remote Access
What is a paging file?
A paging file is a hidden file on the hard disk used by Windows operating systems to hold parts of programs and data
that do not fit in the computer's memory. The paging file and the physical memory, or random access memory (RAM),
comprise the virtual memory. Windows operating systems move data from the paging file to the memory as required
and move data from the memory to the paging file to make room for new data. A paging file is also known as a swap
file.
What are authoritative and non-authoritative Active Directory restores?
There are two general methods of restoring Active Directory from the backup media: authoritative and nonauthoritative.
Authoritative restore makes the computer authoritative over other domain controllers. Data restored authoritatively in
a computer takes precedence over other domain controllers' data, despite the fact that the restored data is older than
the current replicas. Authoritative restore is typically used to restore a system to a previously known state. The
NTDSUTIL command-line tool allows authoritatively restoring the entire directory, a subtree, or individual objects,
provided they are leaf objects.
A non-authoritative restore results in the restored data (which may be outdated) becoming synchronized with the data
on other domain controllers through replication.
What is ADPREP tool?
The ADPREP tool is used to prepare Windows 2000 domains and forests for an upgrade to Windows Server 2003. It
extends the schema, updates default security descriptors of selected objects, and adds new directory objects as
required by some applications.
Syntax:
ADPREP {/forestprep | /domainprep}
Parameter Description
/forestprep Prepares a Windows 2000 forest for an upgrade to a Windows Server 2003 forest.
/domainprep Prepares a Windows 2000 domain for an upgrade to a Windows Server 2003 domain.
/? Displays help for the command.
To run ADPREP /forestprep, the administrator must be a member of the Enterprise Admins group and the Schema
Admins group in Active Directory. The ADPREP /forestprep command must be run on the schema master.
To run ADPREP /domainprep, the administrator must be a member of the Domain Admins group or the Enterprise
Admins group in Active Directory. The ADPREP /domainprep command must be run on each infrastructure master.
Which files are included in the System State data?
Following are the files included in the System State data:
· Boot files, including the system files and all files protected by Windows File Protection (WFP)
· Active Directory (on domain controller only)
· SYSVOL (on domain controller only)
· Certificate Services (on certification authority only)
· Cluster database (on cluster node only)
· Registry
· IIS metabase
· Performance counter configuration information
· Component Services Class registration database
What is RENDOM utility?
RENDOM is a Windows 2003 utility used to rename and restructure a domain in the forest. It can perform the following
tasks:
· Change the DNS and NetBIOS names of the forest-root domain.
· Change the DNS and NetBIOS names of any tree-root domain.
· Change the DNS and NetBIOS names of the parent and child domains.
· Restructure a domain's position in the forest.
The utility is supplied by Microsoft and is placed in the ValueaddMsftMgmtDomren directory on the Windows Server 2003 CDROM.
Note: Renaming a domain is a thorough multi-step process that requires a detailed understanding of the operation. It affects
every domain controller in the forest.
What is volume shadow copy?
The Windows Backup provides a feature of taking a backup of files that are opened by a user or system. This feature is
known as volume shadow copy. Volume shadow copy makes a duplicate copy of all files at the start of the backup process.
In this way, files that have changed during the backup process are copied correctly. Volume shadow copy ensures the
following:
· Applications continue to write data to the volume during a backup
· Backups are scheduled at any time without locking out users.
What is Performance Logs and Alerts?
Performance Logs and Alerts is an MMC snap-in that is used to establish performance baselines, diagnose system
problems, and anticipate increased system resource demands. It is used to obtain useful data for detecting system
bottlenecks and changes in system performance. The alerting functionality of this tool is extremely useful for
troubleshooting intermittent and difficult-to-reproduce problems. It uses the same performance counters as the System
Monitor for capturing information to log files over a period of time. The prime benefit of this tool is the ability to capture
performance counter information for further analysis. Performance Logs and Alerts runs as a service and loads during
computer startup. It does not require a user to log on to a computer.
Network Interface Card
A network interface card (NIC) is a computer circuit board or card installed in a computer. It provides a physical
connection between a computer and the network. Network interface cards provide a dedicated, full-time connection to
a network. Each network Interface card has a unique Media Access Control (MAC) address.
Media Access Control (MAC) address is a numerical identifier that is unique for each network interface card (NIC). MAC
addresses are 48-bit values expressed as twelve hexadecimal digits, usually divided into hyphen-separated pairs, for
example, FF-00-F8-32-13-19. MAC addresses are also referred to as hardware addresses, Ethernet addresses, and
universally administered addresses (UAAs).
Hub
A hub is a device used to link computers in a network. It connects computers that have a common architecture, such as
Ethernet, ARCnet, FDDI, or Token Ring. All hub-computer connections for a particular network use the same type of
cable, which can be twisted-pair, coaxial, or fiber-optic. Hubs are generally used in star topology networks. Token Ring
hubs are also known as Multistation Access Units (MSAUs). A hub works on the physical layer of the OSI model. Two
types of hubs are available as follows:
1. Active hub is a central device used to connect computers in a star network. It regenerates and retransmits
deteriorated signals on the network.
2. Passive hub is a central device used to connect computers in a star network. It receives information through one
of its ports and sends it to the computers connected to every other port. Therefore, although the information is
broadcasted to the network, only the destination computer reads it. A passive hub does not regenerate signals.
Repeater
A repeater is a basic LAN connection device. It allows a network cabling system to extend beyond its
maximum allowed length and reduces distortion by amplifying or regenerating network signals. Repeaters can also be used to
connect network segments composed of different media, such as connecting a twisted pair cable segment to a fiber-optic
cable segment. A repeater works at the physical layer of the OSI model.
Switch
A switch is a network connectivity device that brings media segments together in a central location. It reads the destination's
MAC address or hardware address from each incoming data packet and forwards the data packet to its destination. This
reduces the network traffic. Switches operate at the data-link layer of the OSI model.
Router
A router is a device that routes data packets between computers in different networks. It is used to connect multiple networks,
and it determines the path to be taken by each data packet to its destination computer. A router maintains a routing table of
the available routes and their conditions. By using this information, along with distance and cost algorithms, the router
determines the best path to be taken by the data packets to the destination computer. A router can connect dissimilar
networks, such as Ethernet, FDDI, and Token Ring, and route data packets among them. Routers operate at the network layer
(layer 3) of the Open Systems Interconnection (OSI) model.
Brouter
A brouter is a combination of a bridge and a router. It is used to connect dissimilar network segments, and it routes only a
specific transport protocol such as TCP/IP. A brouter also works as a bridge for all types of packets, passing them on as long as
they are not local to the LAN segment from which they have originated.
Bridge
A bridge is an interconnectivity device that connects two local area networks (LANs) or two segments of the same LAN using
the same communication protocols and provides address filtering between them. Users can use this device to divide busy
networks into segments and reduce network traffic. A bridge broadcasts data packets to all the possible destinations within a
specific segment. Bridges operate at the data-link layer of the OSI model.
Gateway
A gateway is a network interconnectivity device that translates different communication protocols and is used to connect
dissimilar network technologies. It provides greater functionality than a router or bridge because a gateway functions both as
a translator and a router. Gateways are slower than bridges and routers. A gateway is an application layer device.
Modem
Modem stands for Modulator-Demodulator. It is a device that enables a computer to transmit information over standard
telephone lines. Since a computer stores information digitally and a telephone line is analog, a modem converts digital signals
to analog and vice versa. The conversion of a digital signal to analog is known as modulation and that of an analog signal to
digital is known as demodulation.
Normal Backups
When an administrator chooses to use a normal backup, all selected files and folders are backed up and the archive
attribute of all files are cleared. A normal backup does not use the archive attribute to determine which files to back up.
A normal backup is used as the first step of any backup plan. It is used with the combination of other backup types for
planning a backup strategy of an organization. Normal backups are the most time-consuming and are resource hungry.
Restoration from a normal backup is more efficient than other types of backups.
Incremental Backups
An incremental backup backs up files that are created or changed since the last normal or incremental backup. It takes
the backup of files of which the archive attribute is set. After taking a backup, it clears the archive attribute of files. An
incremental backup is the fastest backup process. Restoring data from an incremental backup requires the last normal
backup and all subsequent incremental backups. Incremental backups must be restored in the same order as they were
created.
Note: If any media in the incremental backup set is damaged or data becomes corrupt, the data backed up after
corruption cannot be restored.
Differential Backups
Differential backup backs up files that are created or changed since the last normal backup. It does not clear the
archive attribute of files after taking a backup. The restoration of files from a differential backup is more efficient than
an incremental backup.
Copy Backups
A copy backup copies all selected files and folders. It neither uses nor clears the archive attribute of the files. It is
generally not a part of a planned scheduled backup.
Daily Backups
A daily backup backs up all selected files and folders that have changed during the day. It backs up data by using the
modified date of the files. It neither uses nor clears the archive attribute of the files.
Combining backup types
The easiest backup plan is to take a normal backup every night. A normal backup every night ensures that the data is
restored from a single job the next day. Although the restoration of data from a normal backup is easy, taking a backup
is time consuming. Hence, an administrator is required to make an optimal backup plan. An administrator must
consider the following points before creating a backup plan:
· The time involved in taking the backup.
· The size of the backup job.
· The time required to restore a system in the event of a system failure.
The most common solutions for the needs of different organizations include the combination of normal, differential, and
incremental backups.
Combination of Normal and Differential Backups
An administrator can use a combination of a normal backup and a differential backup to save time in taking a backup as well as
for a restoration of data. In this plan, a normal backup can be taken on Sunday, and differential backups can be taken on
Monday through Friday every night. If data becomes corrupt at any time, only a normal and last differential backup are required
to be restored. Although this combination is easier and takes lesser time for restoration, it takes more time to take backup if
data changes frequently.
Combination of Normal and Incremental Backups
A combination of normal and incremental backups can be used to save more time for taking backups. In this plan, a normal
backup is taken on Sunday and incremental backups on Monday through Friday every night. If data becomes corrupt at any
time, a normal and all incremental backups till date are required to be restored.
Backing up a System State Data
System State Data
System State data contains critical elements of the Windows 2000 and Windows Server 2003 operating systems. Following are
the files included in the System State data:
· Boot files, including the system files and all files protected by Windows File Protection (WFP)
· Active Directory (on domain controller only)
· SYSVOL (on domain controller only)
· Certificate Services (on certification authority only)
· Cluster database (on cluster node only)
· Registry
· IIS metabase
· Performance counter configuration information
· Component Services Class registration database
What is Internet Security and Acceleration (ISA) Server 2000?
Internet Security and Acceleration Server 2000 is a Microsoft product that is used to provide powerful security
and network acceleration while accessing the Internet. It works as a firewall as well as a Web cache server. It
integrates with the Microsoft Windows 2000 operating system for policy-based security, acceleration, and
management of internetworking.
Features of ISA Server
· It provides an additional level of security.
· It offers industry-leading Web cache performance.
· It integrates with Microsoft Windows 2000.
· It enables administrators to use bandwidth efficiently.
· It provides increased manageability.
· It provides enhanced usability.
· It provides integrated services.
· It provides increased extensibility.
· It provides improved interoperability.
· It provides enhanced scalability.
Site and Replication
What is a Site?
A site is a collection of one or more well-connected (usually a local area network) TCP/IP subnets. The network
between the subnets must be highly reliable and fast (512 Kbps and higher). Although the sites are generally
defined on the basis of location, they can be spanned over more than one location. A site structure
corresponds to the physical environment, whereas a domain is the logical environment of the network. A site
can contain single or multiple domains, and a domain can contain single or multiple sites.
The sites are created to physically group the computers and resources to optimize network traffic.
Administrators can configure Active Directory access and replication technology to take advantage of the
physical network by configuring sites. When a user logs on to the network, the authentication request
searches for the domain controllers in the same site as the user. A site prevents the network traffic from
traveling on slow wide area network (WAN) links.
What are Directory Tree, Directory Partition, and Replica?
Directory tree is a hierarchy of objects and containers of Active Directory, which represents all the objects in
the forest. Each domain controller stores a copy of a specific part of the directory tree, called a directory
partition (sometimes called naming context). The copy of the directory partition is called a replica. A replica
contains all attributes for each directory partition object. Each domain controller in the forest stores a replica.
What is replication?
Replication is a process through which the changes made to a replica on one domain controller are
synchronized to replicas on all the other domain controllers in the network. Each domain controller stores
three types of replicas:
· Schema partition: This partition stores definitions and attributes of objects that can be created in the
forest. The changes made in this partition are replicated to all the domain controllers in all the domains in
the forest.
· Configuration partition: This partition stores the logical structure of the forest deployment. It includes
the domain structure and the replication topology. The changes made in this partition are replicated to all
the domain controllers in all the domains in the forest.
· Domain partition: This partition stores all the objects in a domain. Changes made in this partition are
replicated to all the domain controllers within the domain.
Note: Windows Server 2003 supports a new type of directory partition named Application directory partition. This
partition is available only to Windows 2003 domain controllers. The applications and services use this partition to store
application-specific data.
Creating, modifying, moving, and deleting an object trigger a replication between domain controllers. Replications are
of two types:
· Intrasite: An intrasite (within a site) replication mostly uses LAN connections. As intrasite replication
does not compress data, it saves a computer's CPU time. In an intrasite replication, the replication
partners poll each other periodically and notify each other when changes need to be replicated, and then
pull the information for processing. Active Directory uses a remote procedure call (RPC) transport protocol
for intrasite replication.
· Intersite: As an intersite (between sites) replication uses WAN connections, a large amount of data is
compressed to save WAN bandwidth. For the same reason, the replication partners do not notify each
other when changes need to be replicated. Instead, administrators configure the replication schedule to
update the information. Active Directory uses an IP or SMTP protocol for intersite replication.
For intrasite replication to take place, connection objects are required. The Active Directory automatically creates and
deletes connection objects as and when required. Connection objects can be created manually to force replication.
What are Site Links?
Site links are logical, transitive connections between two or more sites. For intersite replication to take place, site links
are required to be configured. Once a site link has been configured, the knowledge consistency checker (KCC) then
automatically generates the replication topology by creating the appropriate connection objects. Site links are used to
determine the paths between two sites. They must be created manually.
Site links are transitive in nature. For example, if Site 1 is linked with Site 2 and Site 2 is linked with Site 3, then Site 1
and Site 3 are linked transitively. The administrators can control transitivity of the site link. By default, transitivity is
enabled. Site link transitivity can be enabled or disabled through a bridge.
What is Site Link Bridge?
A site link bridge is created to build a transitive and logical link between two sites that do not have an explicit site link.
The site link bridge is created only when the transitivity of the site link is disabled.
What is Site Link Cost?
Site link cost is an attribute of a site link. Each site link has been assigned a default cost of 100. The knowledge
consistency checker (KCC) uses the site link cost to determine which site links should be preferred for replication. It
should be remembered that the lower the site link cost, the more preferred is the link.
For example, an administrator has to configure the site link cost of links between Site 1 and Site 2. There are two site
links available as shown in the image below:
S1S2 is a T1 site link that uses T1 lines for replication, whereas S1S2DU uses a dial-up connection for replication. If the
administrator requires that the KCC should prefer the S1S2 site link to the S1S2DU site link for replication, he will have
to configure the SIS2 link with a lower cost than that of the S1S2DU link. Any site link configured with the site link cost
of one (1) will always get preference over the other site links with a higher cost.
What is Bridgehead Server?
A bridgehead server is a domain controller in each site, which is used as a contact point to receive and replicate data
between sites. For intersite replication, KCC designates one of the domain controllers as a bridgehead server. In case
the server is down, KCC designates another one from the domain controller. When a bridgehead server receives
replication updates from another site, it replicates the data to the other domain controllers within its site.
What is Preferred Bridgehead Server?
A preferred bridgehead server is a domain controller in a site, specified by an administrator, to act as a bridgehead
server. Administrators can specify more than one preferred bridgehead server, but only one server is active at a time in
a site. A preferred bridgehead server is designated to take advantage of a certain domain controller having the
appropriate bandwidth to transmit and receive information
What is Performance Logs and Alerts?
Performance Logs and Alerts is an MMC snap-in that is used to establish performance baselines, diagnose
system problems, and anticipate increased system resource demands. It is used to obtain useful data for
detecting system bottlenecks and changes in system performance. The alerting functionality of this tool is
extremely useful for troubleshooting intermittent and difficult-to-reproduce problems. It uses the same
performance counters as the System Monitor for capturing information to log files over a period of time. The
prime benefit of this tool is the ability to capture performance counter information for further analysis.
Performance Logs and Alerts runs as a service and loads during computer startup. It does not require a user to
log on to a computer
What is WLBS.EXE?
WLBS.EXE is a command-line tool, which is used as a Network Load Balancing control program. WLBS.EXE is
used to start, stop, and administer Network Load Balancing, as well as to enable and disable ports and to
query cluster status.
Note: WLBS.EXE cannot be used to change the registry parameters of Network Load Balancing.
What is buffer overflow?
Buffer overflow is a condition in which an application receives more data than it is configured to accept. This
usually occurs due to programming errors in the application. Buffer overflow can terminate or crash the
application
What is DMZ?
Demilitarized zone (DMZ) or perimeter network is a small network that lies in between the Internet and a
private network. It is the boundary between the Internet and an internal network, usually a combination of
firewalls and bastion hosts that are gateways between inside networks and outside networks. DMZ provides a
large enterprise network or corporate network the ability to use the Internet while still maintaining its security
What is Kerberos v5?
Kerberos v5 is an authentication method used by Windows operating systems to authenticate users and
network services. Windows 2000/2003 and XP clients and servers use Kerberos v5 as the default
authentication method. Kerberos has replaced the NT LAN Manager (NTLM) authentication method, which was
less secure. Kerberos uses mutual authentication to verify both the identity of the user and network services.
The Kerberos authentication process is transparent to the users.
Note: Kerberos v5 is not supported on Windows XP Home clients or on any clients that are not members of an
Active Directory domain.
What is Software Update Services (SUS)?
Software Update Services (SUS) is a tool used to acquire and distribute critical Windows patches to computers
running Windows operating systems. Administrators use SUS to download and test the patches, and then
deploy the patches to the appropriate computers running the Automatic Updates clients. SUS consists of three
components:
1. Software Update Services (SUS) that runs on the server.
2. Automatic Updates (AU) that runs on client computers.
3. Group Policy settings that control AU clients from Active Directory.
SUS does not support Microsoft Office or Microsoft BackOffice products. It updates the operating systems (except
Windows NT or Windows 9x), Microsoft IIS, and Microsoft Internet Explorer (IE) only.
Which installation modes are available with ISA Server?
The following modes are available as a part of the ISA Server setup process:
· Firewall: In Firewall mode, network configuration can be secured by configuring rules that control
communication between a corporate network and the Internet. In this mode, internal servers can also be
published to share data with Internet users.
· Cache: In Cache mode, network performance can be improved and bandwidth can be saved by storing
commonly accessed Internet objects locally. Requests can be routed from the Internet users to an
appropriate internal Web server.
· Integrated: Integrated mode is a combination of Firewall and Cache modes. It supports all the features
available in Firewall and Cache modes of ISA Server

Read More

To increase Performance of system

Increase your computer speed instantly!!

Top 12 Ways to Speed Up Your Computer
After a while, all computers start to slow down. The pop-ups start to invade your desktop. The programs take longer to load and the computer sounds like a freight train every time you start it up.

Thankfully, there are a number of things you can do before calling in the repair man. Here are 12 of the top ways to speed up your computer.

1. Defragment your computer. The defrag tool can be found by going to the start menu, to programs, accessories, system tools and then clicking on the defragementation tool. It may take several hours to properly defrag a hard drive, especially if you haven't done it in a while. However, the resulting gain in speed will be well worth it.

Quick Note: For best performance, you should delete any unnecessary files before defragmenting your drives.

2. Add additional memory. Adding RAM to your system is one of the best ways to speed up your computer.

3. Clear your Internet cache for faster browsing speeds.

4. Delete your cookies.

5. Use keyboard shortcuts.

6. Empty your Temp Directory on a regular basis.

The temp directory can quickly fill up with hundreds of temp files over time, which can slow things down immensely.

To delete Temp files, you must first make sure that NO programs are open. If you are using Windows 95, 98 or Me, go to C:WindowsTemp and delete everything inside the Temp folder.

If you are using Windows 2000 or XP, the process is a bit trickier. First, you must make sure that you can see hidden folders. To do this, double-click on My Computer. Click on the Tools pull-down menu, and then click on Folder Options. Click on the View tab. Scroll down and click on Show Hidden Files and Folders. click Ok. Now you can go to C:Documents and SettingsUSERLocal SettingsTemp folder and delete everything there.

7. Install and run a good Antivirus program on a regular basis. One of the best Antivirus programs is a free piece of software called AVG Antivirus.

8. Get rid of Spyware. Lots of computers have Spyware and don't even know it. Some of the warning signs include:

* computer running extremely slow for no apparent reason

* computer has been taking you to websites that you don't want to go to

* you get pop-ups even when you're not on the Internet.

If your computer has any of these symptoms, you are likely to be infected with Spyware. Some of the best solutions include AdAware SE and SpyBot Search and Destroy.

9. Streamline MSCONFIG. Many computers have unnecessary programs running in the background which can cause a huge decrease in performance. You can use a utility called MSCONFIG to turn off unnecessary Start Up items that are
robbing your system of memory and processing power.

To run MSCONFIG, click on the Start Menu and click on run. In the run dialog box, type msconfig and press "ok".

10. Uninstall unused programs and files. Let's face it. After a while, there are a number of programs and files that we simply don't use anymore. Give your computer a
performance boost and get rid of them.

To delete programs completely, you must first go to the Control Panel and click on Add or Remove Programs.

11. Run disk clean up. Disk clean up can be found by going to the start menu, to programs, accessories, system tools and then clicking on Disk Cleanup.

12. If you're running Windows XP, turning off the fancy graphics will cause a noticeable increase in the speed up your computer. First, go to Start, Settings, Control Panel, System. You may need to "Switch to Classic Mode" to see the System applet.

Next, go to the Advanced tab. Click on the Settings button under Performance.

Select the "Adjust for best performance" radio button. Click Apply and then OK. The graphics aren't as slick, but your computer will run a lot faster.

Read More

Microsoft® SQL Server™ 2000 Backup Schedules

Introduction
The need to back up databases on a regular basis is a major component of managing any production system.
Backups may be used to provide a means of recovery from a disaster situation. Microsoft® SQL Server™ 2000 provides several kinds of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the disaster recovery requirements.
SQL Server 2000 enhances some aspects of the backup and restore functionality that was provided in SQL Server 7. There is also additional functionality that helps individual organizations take full advantage of commands in SQL Server 2000.
It is highly recommended that all SQL Server databases be backed up periodically. This provides the best chance of successfully recovering a production environment in the quickest amount of time in case there is a disaster situation.
This paper discusses the various kinds of backups that are available in SQL Server 2000 and how this functionality may be used in a disaster recovery plan. The paper also discusses some general recommendations on how to improve backup and restore throughput. Finally, we examine two case studies where we implement the knowledge from previous sections in real-world scenarios.
Top Of Page
Common Terms
Data page
An SQL Server database’s basic data storage structure is 8 KB and is known as a data page. An SQL database may contain thousands of pages.
Disaster recovery planning
The process of formulating, documenting, and testing the procedures that would be performed if production data in one or more SQL Server databases were to be lost or modified in an unforeseen disaster or malicious attack.
Minimally logged operations (bulk load operations)
Data movement operations that require minimal logging in the transaction log. These operations include bcp, certain Data Transformation Services (DTS) operations, and SELECT INTO. Depending on the recovery model for a database, any of these operations might either be fully logged or minimally logged.
Filegroup
A logical grouping of SQL Server database files. By default, a new SQL Server database contains the Primary filegroup.
Log sequence number (LSN)
The unique number that each operation is stamped with when it is written to the transaction log. A single SQL Server transaction may contain several LSNs.
Logical file names
The names that are used by SQL Server to identify files within an SQL Server database.
Physical file names
The name used by the operating system to identify specific files. All SQL Server database files have both a physical and a logical file name.
Extent
A collection of 8 data pages. Since a data page is 8 KB, an extent is 64 KB.
SQL Query Analyzer
A graphical tool provided with SQL Server client utilities to query SQL Server databases using the Transact-SQL commands.
Transaction
A set of modifications that are performed as a single unit of work. A transaction follows the ACID guidelines. For more information on the ACID standard, see “Transactions” in SQL Server Books Online.
Transaction log
A record of modifications performed to a database. The amount of information logged in the transaction log depends on the recovery model for a database. For more information on recovery models, see “Recovery Models” in the next section of this paper.
Tail of transaction log
The transactions that have been committed but not backed up since the previous complete or differential database backup or transaction log backup.
Transaction undo file
File containing information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed. A transaction undo file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.
Virtual log file (VLF)
A logical section within an SQL Server database’s transaction log. When performing a truncate of the transaction log, an entire VLF is cleaned out.
Top Of Page
New Backup and Restore Features in SQL Server 2000
Top Of Page
Recovery Models
SQL Server 2000 introduces the concept of recovery models for databases. Recovery models are designed to simplify the administration of SQL Server 2000 databases. There are three recovery models in SQL Server 2000—Full, Bulk-Logged, and Simple. System databases (including master, MSDB, and tempdb) are set to the Simple Recovery model. All user databases, by default, are created with the Full Recovery model (it should be noted that the Full Recovery model takes affect once a complete database backup is performed). The recovery model may be changed once the database is created.
The recovery model for a database incorporates the two most often used settings—Truncate Log on Checkpoint and Select Into/Bulkcopy.
Truncate Log on Checkpoint. In previous versions of SQL Server, this setting was selected to automatically truncate the transaction log every time CHECKPOINT is activated for the database.
Select Into/Bulkcopy. This setting was used in previous versions of SQL Server to perform non-logged operations.
Following are the settings and their relation to the three recovery models.
Recovery Model Select Into / BulkCopy Truncate Log on Checkpoint
Full False False
Bulk-Logged True False
Simple True/False True
Top Of Page
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making the transaction log virtually maintenance free. There are limitations placed on the recoverability of a database if this recovery model is used.
Top Of Page
Bulk-Logged Recovery Model
A database in this recovery model will have minimum logging for bulk import operations. Space allocation and deallocation is only logged for bulk import operations. Point-in-time and point-of-failure recovery may be possible when a database is in Bulk-Logged Recovery model.
Top Of Page
Full Recovery Model
SQL Server performs full transaction logging for any bulk load operations if a database is in Full Recovery model. Transaction log backups should be performed at regular intervals for maximum recoverability. This model provides the safest mode of operation for production systems.
The following table summarizes the recovery models and backup types available with each recovery model.
Recovery Model/ Backup Complete Differential Transaction Log File / Filegroup
Simple Required Allowed Not Allowed Not Allowed
Bulk-Logged Required Allowed Required Allowed
Full Required Allowed Required Allowed
Top Of Page
Partial Database Restore
New functionality in SQL Server 2000 provides commands to restore a database backup partially. If a database contains several filegroups, a single filegroup may be recovered using this new functionality. Partial database restore operations provide a means to restore only certain parts of the database, as needed.
Top Of Page
Enhancements to Differential Backups
Differential backups have been enhanced in SQL Server 2000. A bitmap of modified extents has been added to the database structure. This bitmap contains a bit for each extent that has been modified since the previous complete backup. This bitmap is referenced when a differential backup is initiated and only modified extents are referenced and backed up. This significantly improves the performance of differential backups in SQL Server 2000.
Top Of Page
Password-Protected Backup Files
New functionality has been added to backup functionality to password-protect the backup files. This is in line with the security initiative and helps protect against unauthorized access.
Top Of Page
Named Log Marks
SQL Server 2000 introduces the concept of named transactions. Named log marks allow a transaction log backup to be restored up to a particular named transaction. This further enhances the point-in-time restore functionality on the transaction log backups.
Top Of Page
Continuous Log Chain
SQL Server 2000 improves the transaction log backups. It now includes database file management functionality such as the addition and removal of database file(s) as logged operations. This improves the manageability of databases in a log shipping environment.
Recommended Reading
See the following SQL Server Books Online topics:
What’s new in SQL Server 2000
Selecting a Recovery Model
Using Recovery Models
Top Of Page
Additional Backup and Restore Information
SQL Server provides functionality to back up and restore SQL Server databases to disk or tape medium.
Top Of Page
Types of Backups
SQL Server provides several different kinds of backups. A combination of these backups may be used to formulate a robust disaster recovery strategy.
Backup Type Description
Complete Backs up the entire database.
Differential Backs up only modified extents since the previous complete backup.
Transaction Log Backs up the active portion and truncates the inactive portion of the transaction log.
File / Filegroup Backs up individual files and filegroups within a database.
File differential Combines differential backups and file or filegroup backups.
Top Of Page
Backup Media
SQL Server databases may be backed up to either a disk or tape media. Backup may be performed through SQL Server Enterprise Manager or a Transact-SQL command.
Top Of Page
Disk Backups
A database may be backed up to disk file or a disk backup device.
Any database can be backed up to a random disk file at any time. The file may either be initialized or the backup may be appended to an existing backup file.
Top Of Page
Tape Backups
A database may be backed up to a local tape drive. SQL Server formats the tape backups using Microsoft Tape Format (MTF). This means that a tape may hold other backups formatted using MTF in conjunction with SQL Server backups.
Tape backups provide certain features that are not available when using disk backups.
Continuation media
If the tape to which the backup is being written fills up, SQL Server Enterprise Manager pops a dialog box and prompts for the next tape (if using the Transact-SQL command, a message is logged to the SQL Server error log to mount the next tape and a retry attempt is made roughly every five minutes to see if a new tape was mounted). This is in contrast to disk backups, where inadequate disk space terminates the backup operation.
Restart option
If there is a power failure or the server shuts down unexpectedly while the backup/restore is being performed, the operation may be restarted from the point at which it was interrupted.
Top Of Page
Backup Devices
A backup device may be created through SQL Enterprise Manager or by using Transact-SQL commands.
To create the device through Enterprise Manager
1. Open Enterprise Manager and connect to the server where the backup device needs to be created.
2. Expand the Management folder and right-click Backup to display the shortcut menu shown in Figure 1.

Figure 1: Backup Device through SQL Enterprise Manager
3. Select New Backup Device... to display the dialog box in Figure 2.

Figure 2: New Backup Device Properties
Enter a name and location for the backup device.
Notice that the Tape option is disabled in the dialog box shown in Figure 2. This is because the computer used in the example does not have a tape drive installed.
4. Click OK when done. This will create the backup device.
Once the backup device has been created, it appears under the Management -> Backup tree within SQL Enterprise Manager.
5. To view the properties for the backup device, right-click the device and select Properties. The dialog box shown in Figure 3 appears.

Figure 3: Backup Device Properties
6. If you want to view all backups that have been performed to this device, click View Contents.
A backup device may also be created by using a Transact-SQL command through SQL Query Analyzer. Figure 4 illustrates the use of a Transact-SQL command to create a backup device similar to that shown in the previous steps.

Figure 4: Creating a backup device using Transact-SQL
Top Of Page
Adding Backup History from Backup Files to MSDB
This section discusses the procedure that may be used to restore the backup history from a single or a set of backup files in case this information is lost or does not exist in MSDB tables.
For the purposes of this section, we will assume that we have a complete database backup of a database called TESTDB for which the restore history is to be retrieved and saved to MSDB tables.
To restore backup history from backup files
1. Copy the backup files that are to be restored to the relevant SQL Server machine.
2. Open Enterprise Manager and connect to the server where the backups are to be restored.
3. Right-click any database and select All Tasks, then select Restore Database... The Restore Database dialog box shown in Figure 5 is displayed.

Figure 5: Restore backup set information
4. Select the From Device radio button.
5. Select the Read backup set information and add to backup history option in the Parameters section.
6. Click the Select Devices... button to add the backup file(s) to the list. The Choose Restore Devices dialog box (Figure 6) appears.

Figure 6: Choose Restore Devices dialog box
7. Click the Add button and select the backup file(s) for which the backup history is to be loaded.
8. Once the files have been selected, click the OK button.
9. Click the OK button in the dialog box shown in Figure 5. This will initiate the restore of the backup history to the MSDB tables.
Note It is important to note that this operation does not physically restore the database for which the files are selected in the above step.
10. Once the backup history is completely loaded, the dialog box shown in Figure 7 is displayed. Click OK to complete the history restore operation.

Figure 7: Backup history restore confirmation dialog box
This operation results in the information for TESTDB appearing if a further attempt is made at restoring the database from these backups.
Top Of Page
Media Sets and Families
Media sets comprise several individual media. All media in a media set should be of the same type. For example, a 200-GB database might span 3 tapes. The 3 tapes are considered to be a media set.
A media family refers to the collection of media used by an individual backup device. For example, if a 2-TB database is backed up using 4 tape drives (with 5 tapes in each drive), each set of 5 tapes is considered to be a media family. Collectively the 20 tapes would be considered a media set.

Figure 8: Media set and family
Top Of Page
Virtual Device Interface
Virtual Device Interface (VDI) is the programming interface for the Backup Restore API. It provides function calls to manipulate the backup and restore functionality in SQL Server.
VDI is used by several third-party tools that provide capabilities to back up SQL Server databases. For more information on this topic, see “Virtual Backup Device Samples” in SQL Server Books Online.
Top Of Page
Permissions Required for Backup and Restore
Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:

Server Role : sysadmin
DB role : db_backupoperator, dbo_owner
Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner

Recommended Reading
See the following SQL Server Books Online topics:
Backup Devices
Using Multiple Media or Devices
Using Media Sets and Families
Backup Restore Architecture
Top Of Page
Complete Database Backup
A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored.
A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged.
It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases (including master and MSDB) if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS packages or scheduled jobs, adding and removing linked servers, etc.
Top Of Page
Performing Complete Database Backups
A complete database backup may be performed either through SQL Server Enterprise Manager or by using Transact-SQL commands. Complete backups may also be scheduled to be performed at regular intervals. Scheduling may be done through either SQL Server Enterprise Manager or using Transact-SQL commands.
To perform a complete database backup through SQL Server Enterprise Manager
1. Open Enterprise Manager and connect to the server.
2. Expand the Databases folder, then right-click the database that you want to back up.

Figure 9: Backup database through Enterprise Manager
3. Select All Tasks, then select Backup Database... as shown in Figure 9. The dialog box shown in Figure 10 is displayed.

Figure 10: Backup Database dialog box
4. Provide a name for the backup in the Name text box. Leave the Database – complete radio button selected since we are performing a complete database backup.
5. Select the Overwrite existing media check box to initialize the destination file or device or select the Append to media check box to append the current backup to existing file or device.
6. To select a destination for the backup, click the Add button. The dialog box shown in Figure 11 is displayed.

Figure 11: Select Backup Destination
7. Select an existing file or enter a new file name. Click OK after selecting a file.
8. Click the Options tab. The options shown in Figure 12 are presented.

Figure 12: SQL Server Backup Options
9. Select the Verify backup upon completion check box to verify the backup upon completion. (Please see SQL Server Books Online for more information regarding the RESTORE VERIFYONLY command.)
Description of other fields
Remove inactive entries from transaction log – truncates the transaction log while performing the backup. If this setting is not checked, SQL Server uses the NO_TRUNCATE option for the backup. This option is available only while performing transaction log backup.
Check media set name and backup set expiration - verifies the selected media for the provided media set name to prevent accidental overwrites.
Eject tape after backup – ejects the tape from the drive when the backup completes.
Backup set will expire – specifies when the backup expires and is no longer restorable.
Initialize and label media – erases and labels media sets. Although this option is available for all tape backups, it is most useful when there are multiple tapes forming a media set.
10. Once all the necessary options are selected, either click the OK button to start performing the backup, or check the Schedule check box to schedule this operation for periodic execution.
If the backup is performed immediately, the Backup Progress dialog box (Figure 13) is displayed while the backup is being performed. If the backup operation should be scheduled, see Modifying the SQL Server Job Schedule later in this paper for more information on how to modify the default schedule.

Figure 13: Backup Progress dialog box
Upon successful completion of the backup, the informational dialog box shown in Figure 14 is displayed.

Figure 14: Backup complete confirmation
The above functionality can be accomplished through Transact-SQL commands executed from SQL Query Analyzer. An example of such a command is illustrated below.

BACKUP DATABASE northwind
TO DISK = 'd:\backups\northwind\nwind.bak'

For more information on the Transact-SQL commands, see BACKUP (T-SQL) in SQL Server Books Online.
Top Of Page
Restoring Complete Database Backups
A complete database backup may be restored to the same or a new or different database on the same (or a different) server. The restore operation may be initiated either through either SQL Server Enterprise Manager or the Transact-SQL command window.
It is highly advisable to restore complete database backups at regular intervals, as this is the only means that is currently available to verify the “restorability” of an SQL Server backup.
Top Of Page
Restoring a Complete Backup to the Same Database
To restore a complete database backup to the same database
1. Open SQL Server Enterprise Manager and connect to the server where the backup is to be restored.

Figure 15: Restore database through Enterprise Manager
2. Right-click the database and select All Tasks, then select Restore database (as shown in Figure 15). The Restore Database dialog box (Figure 16) is displayed.

Figure 16: Restore Database dialog box
3. From the list of databases, select the database for which the backup has to be restored.
A list of all backups performed for the selected database is displayed in the Parameters section of the Restore Database dialog box. This information is collected from the history tables in the MSDB database.
4. From the list of backups, select the backup to restore, then click the Properties button to display the Backup Set Properties dialog box shown in Figure 17.

Figure 17: Backup Set Properties dialog box
The dialog box displays backup properties including backup type, size (in KB), start and finish dates, server name, and media description.
5. In the Backup Set Properties dialog box, click OK.
6. In the Restore Database dialog box (Figure 18), click the Options tab.

Figure 18: Restore Database Options
The Options tab in the Restore Database dialog box provides options to select the final restore state, change the file name, and set tape options for the restore operation.
7. Select the appropriate settings in this dialog box.
Description of fields
Eject tapes after restoring each backup – ejects the tape when the restore operation completes if a tape restore is being performed.
Prompt before restoring each backup – displays a dialog box after each backup is restored successfully. This option may be used when restoring multiple backups.
Force restore over existing database – forces the files for the existing database to be initialized. This option should be used with caution as it erases the data that exists in the selected database before starting the restore operation.
Restore As – shows the original and target physical file names for the database that is being restored.
Recovery completion state – determines the final state of the restored database.
Undo file – a file required by SQL Server to track incomplete transactions if the backup is restored in standby state.
8. To start the restore operation, click OK. The Restore Progress dialog box (Figure 19) is displayed while the restore operation executes.

Figure 19: Restore Progress dialog box
9. When the restore operation completes, the dialog box shown in Figure 20 is displayed. To close it, click OK.

Figure 20: Restore database complete confirmation
Top Of Page
Restoring a Complete Backup to a New Database on the Same Server
A new database may be created while restoring a complete database backup. The new database may be created on the same server where the original database was located, or on a different server. In either case, there are several restrictions placed on the database if it is created in this manner:
• Physical file structure may not be modified while restoring
• Logical file names may not be changed
• Physical files created must be the same size as when the backup was performed
If you intend to create a new database, as part of the restore operation, on the same server where the original database still resides, follow this procedure.
To restore a complete backup to a new database on the same server
1. Open SQL Server Enterprise Manager and connect to the server where the backup is to be restored.
2. Right-click any database and select All Tasks, then select Restore Database to open the dialog box shown in Figure 21.

Figure 21: “Restore Database As” dialog box
3. In the Restore as database: text box, enter the name of the new database that you wish to create.
4. Under Parameters, select the database for which you intend to restore the backups (in the Show backups of database list box).
A list of backups for the database are displayed in window.
5. Select the backup that you would like to restore and click Properties to verify the properties for that backup. See the Properties dialog box in Figure 17 for an illustration.
6. Click the Options tab.

Figure 22: “Restore Database As” Options dialog box
7. If necessary, modify the file name and location of the database files listed in the Restore As list. The first part of the file name is used as the logical name for the respective file. In the example in Figure 22, the first file will have the physical file name of nwind_new_log.ldf and the logical name of nwind_new_log.
8. Select all the other options from this dialog box as necessary. For an explanation of these options, see Figure 18.
9. To start the restore operation, click OK. A progress dialog box is displayed and when the restore operation is finished, a confirmation dialog box is displayed. A new database called nwind_new is added to the Enterprise Manager window.
This functionality can also be accomplished by using Transact-SQL commands. Assuming that the database backup file name is c:\backups\northwind\nwind.bak, the following command will restore the database to nwind_new:

RESTORE DATABASE nwind_new FROM DISK = 'c:\backups\northwind\nwind.bak'
WITH
MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL
Server\Data\nwind_new.mdf'
MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL
Server\Data\nwind_new_log.ldf'

Top Of Page
Restoring a Complete Backup to a New Server
SQL Server databases may be backed up on one server and restored to another server. If the target database does not exist on the second server, it may be created before the restore is initiated. However a new database can be created while restoring the backup.
If a new database is created while restoring the backup, all the restrictions mentioned in Restoring a Complete Backup to a New Database on the Same Server apply.
The steps in this section demonstrate one of the many ways that someone may use to restore the database onto a different server. Another means of achieving this same functionality would be to restore the backup history using the procedure explained in Adding Backup History from Backup Files to MSDB and then using the procedure listed in Restoring a Complete Backup to a New Database on the Same Server using the SQL Enterprise Manager GUI.
To restore a database backup to a different server to a new database
1. Copy the database backup file to the target machine or share it on the network so that it is accessible from the server.
2. Open Enterprise Manager and connect to the server to which the backup is to be restored.
3. Right-click any database and select All Tasks, then select Restore Database to display the Restore Database dialog box shown in Figure 23.

Figure 23: “Restore from device” dialog box
4. Enter the name of the new database that the backup process will create and restore the backup to, in the Restore as database : list box.
5. Select the From device radio button.
6. Click the Select Devices... button.

Figure 24 : Choose Restore Devices dialog box
7. In the Choose Restore Devices dialog box, select either the Disk or the Tape radio button.
In the dialog box in Figure 24, the Tape option is dimmed (unavailable) because the backup device does not have a tape drive configured. For the purposes of this example we will assume that we are restoring the backup from a disk device.
8. Click the Add button to display the dialog box shown in Figure 25.

Figure 25: Choose Restore Destination dialog box
9. Enter the file name of the backup file that is to be restored. If the file exists on a network share, provide the UNC share name in the File name text box.
Note It is recommended that the backup be copied to the server and a local path be used to restore the database.
10. Click the OK button. The Choose Restore Device dialog box, displayed in Figure 24, is now updated with the selected file.
11. Click the OK button to return to the Restore Database dialog box. The Devices list is now updated with the file selected in the previous step.
12. Click the Options tab and select appropriate options for the restore operation. The file names of the physical files may be changed at this time. For more information on the fields in this dialog box, see steps 6 and 7 in Restoring a Complete Backup to a New Database on the Same Server.
13. Click OK in the Restore Database dialog box. This starts the restore process.
Progress and confirmation dialog boxes are displayed as SQL Server processes and completes the restore of the database.
14. Click OK in the confirmation dialog box.
This restore operation may also be accomplished through Transact-SQL commands. The command would be similar to the one demonstrated in Restoring a Complete Backup to a New Database on the Same Server.
Top Of Page
Performing a Partial Database Restore
Partial Database Restore is newly added functionality in SQL Server 2000. It allows for individual filegroups to be restored to new databases. This functionality is implemented in SQL Server using new options for the RESTORE Transact-SQL command.
For a better understanding of filegroups, see “File/Filegroup Backup” later in this paper.
A filegroup may be restored partially only from a complete backup. A file/filegroup backup does not contain transaction log backup and hence cannot be used to restore the particular filegroup partially. All other filegroups in the database (except Primary) are marked OFFLINE when this restore mechanism is used. It should be noted that all files in the Primary filegroup are always restored in any Partial Database Restore situation.
Performing a Partial Restore
For the purposes of this demonstration we will assume that:
• We have a database called files that has two filegroups—fg1 and fg2 (see the script in Appendix A).
• Database files has a complete backup stored in file called d:\backup\files.bak
We need to restore the contents of filegroup fg2 to a new database called files_fg2.
Open SQL Query Analyzer and perform the query shown in Figure 26 to get the names of files that constitute the complete backup.

Figure 26: RESTORE FILELISTONLY Results
We will need to restore files belonging to the Primary and fg2 filegroups. Execute the command shown in Figure 27 from the SQL Query Analyzer window.

Figure 27: Partial database restore results
Apply any further Transaction log or differential backups that might be available.
Once the database is recovered, it may be accessed in a normal manner. All other filegroups in the database are marked OFFLINE. Any attempts to perform queries on objects that reside on other filegroups in the database generate the error shown in Figure 28.

Figure 28: Error generated when querying an OFFLINE filegroup
Recommended Reading
See the following SQL Server Books Online topics:
RESTORE
RESTORE FILELISTONLY
Partial Database Restore Operations
Database Backups
See the following Microsoft Knowledge Base article:
Q221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q221465
Top Of Page
Transaction Log Backup
An SQL Server database consists of two components—data file(s) and transaction logs. A transaction log captures the modifications made to the database. An SQL Server database must have at least one transaction log file.
A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number. Log records that belong to the same transaction are linked together through the log sequence number.
If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery at startup. Entries in the transaction log are also used if transactional replication is configured for the specific database.
A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server.
A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.
Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error:
Server: Msg 4305, Level 16, State 1, Line 1
This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log.
If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message. There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:
• The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated.
• Another transaction log backup was performed between the previous successfully restored backup and the one generating this message.
• The transaction log was manually truncated between the two backups.
• The database was in Bulk-Logged recovery model and non-logged operations were performed.
• Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database.
• Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore.
Top Of Page
Performing Transaction Log Backups through Enterprise Manager
To perform a transaction log backup
1. Open SQL Enterprise Manager. Connect to the server and expand Databases.
2. Right-click the database for which a transaction log backup has to be performed and select All Tasks, then select Backup database...
The dialog box shown in Figure 29 is displayed.

Figure 29: Transaction Log Backup
3. You can enter a name for the backup in the Name text box and a description for this backup in the Description text box. These two fields are optional and may be left blank.
4. To perform a transaction log backup, select the Transaction log radio button.
5. Select a destination for the backup. This could be either an SQL Server backup device, tape, or a disk file.
For the purposes of this example, we have selected a disk file called d:\backups\nwind\nwind_log.bak. For more information, see the procedure in Performing Complete Database Backups in this paper.
6. To remove any previous files with this name and create a new file, select the Overwrite Existing Media radio button.
7. Check the Schedule check box if this operation has to be scheduled for periodic operation. For more information on how to modify the schedule for an operation, see Modifying the SQL Server Job Schedule in this paper.
8. To view and, if necessary, modify the transaction log backup options, click the Options tab.

Figure 30: Transaction Log Backup Options
For more information on the fields in the Options tab, see Figure 9.
9. Once all the options have been selected, click the OK button to start the backup.
A progress dialog box is displayed while the backup is being performed. When the backup is finished, a confirmation dialog box is displayed.
10. Click OK in the confirmation dialog box to complete the backup operation.
Top Of Page
Restoring Transaction Log Backups to the Same Server
As mentioned previously, to restore a transaction log backup, a complete backup must be restored in an unrecovered state. For the purposes of this example, we will continue with the example Restoring a Complete Backup to a New Database on the Same Server where a new database called Nwind_New was created from a backup of Northwind database.
For this example we would have to select the Leave Database non-operational but able to apply further transaction log backups option under the Leave Database Operational setting when restoring the complete backup. This option is displayed in Figure 18.
To restore transaction log backups to the same server
1. Open Enterprise Manager, connect to the server, and expand Databases.
2. Right-click Nwind_New database and select All Tasks, then select Restore Database... The dialog box shown in Figure 31 is displayed.

Figure 31: Restore log to new database
3. In the Show backups of database list, select Northwind. This updates the information displayed in the window.
It may be noticed that the transaction log backup corresponding to the complete backup that was loaded initially to create the NWIND_NEW database is selected by default.
4. You can view properties for the log backup by selecting the Northwind Log Backup and clicking the Properties button.
5. To view and, if necessary, modify any option relating to the restore operation, click the Options tab. For a detailed description of the options available on this tab, see the descriptions provided for Figure 18.
6. To start the restore of the transaction log, click OK.
A progress dialog box is displayed while the backup is being restored. When the restore finishes successfully, a confirmation dialog box is displayed.
7. To complete the restore operation, click OK in the confirmation dialog box.
Top Of Page
Restoring Transaction Log Backups to a Different Server
Since a transaction log backup may only be applied if a database is in an unrecovered state, if the restore operation is being performed on a different server, the backup history does not exist. In this case, either Enterprise Manager or Transact-SQL commands could be used to perform the restore of the complete database backup. Alternatively, the backup history could be loaded into the new server’s MSDB database using the procedure described in Adding Backup History from Backup Files to MSDB later in this paper.
For the purposes of this paper, we will consider this procedure as performed through SQL Enterprise Manager. We will continue this example from the example provided in Restoring a Complete Backup to a New Server later in this paper.
To restore transaction log backups to a different server
1. Open SQL Server Enterprise Manager and connect to the server.
2. Expand Databases. Right-click the database for which the transaction log backup is to be restored and select All Tasks, then select Restore Database...
The dialog box shown in Figure 32 is displayed.

Figure 32: Restore transaction log backup to new server
3. To select the backup file(s) containing transaction log backup(s) to be restored, click the Select Devices... button. For more information on this step, see the procedure explained for Figure 18.
4. Select the Transaction log radio button under Restore backup set.
5. Click the Options tab and select appropriate options for the restore. If this is the final transaction log backup, select the Leave database operational. No additional transaction logs can be restored option. If there are further backups to be applied after this one, select one of the other two options for the Recovery completion state.
6. To start the restore operation, click OK.
Progress and confirmation dialog boxes are displayed while the backup is being restored and when the backup is restored.
7. To complete the restore, click OK in the confirmation dialog box.
Top Of Page
Restoring Transaction Log Backups to a Point in Time
Transaction log backups may be restored to a point-in-time. This functionality is available only through the use of transaction log backups. There are certain restrictions placed upon using point-in-time recovery as follows:
• Point-in-time recovery may not be used in conjunction with file/filegroup restore. The tail of transaction log has to be applied in full when recovering file/filegroups from file/filegroup backups. For more information, see “File/Filegroup Backups” later in this paper.
• Point-in-time recovery is only available as the last step in a recovery situation. This means that no further backups may be restored if point-in-time recovery is used. The database is recovered immediately even if the NORECOVERY or STANDBY option is specified.
Point-in-time recovery is implemented using the STOPAT option with the RESTORE LOG Transact-SQL statement. The SQL Enterprise Manager dialog box shown in Figure 33 is used to perform a point-in-time restore.
This dialog box is displayed when the Point in time restore check box is selected in the dialog box shown in Figure 28.

Figure 33: Point In Time Restore dialog box
Select the appropriate date and time to stop the transaction log restore operation through this dialog box. Once the date time is selected, click OK to continue with the restore operation.
Note Since this procedure results in a different ending transaction after the restore is complete, a full database backup should be performed before any further transaction log backups.
Top Of Page
Restoring Transaction Log Backups to a Named Transaction
SQL Server 2000 provides functionality to restore a transaction log backup up to and including or excluding a certain transaction. This functionality has been implemented with new options for the RESTORE LOG command—STOPATMARK and STOPBEFOREMARK.
Restoring a transaction log to a named transaction is bound by the same restrictions as point-in-time restore. Additionally, recovery to a named transaction is available only if the specific transaction was started with a name. The following code demonstrates how to create a simple named transaction that updates the Customers table in the Northwind database.

BEGIN TRAN Demo
UPDATE Customers
SET CITY = ‘Hamburg’
WHERE NAME = ‘Tony Munitz’
COMMIT TRAN Demo

If this transaction is the offending transaction and a customer would like to perform recovery on the database, excluding this transaction and any other transactions that were performed after this transaction, we could use the following command:

RESTORE LOG NorthWind_Test
FROM DISK = 'd:\backups\TLOG_23.BAK'
WITH STOPBEFOREMARK = 'Demo'

Note STOPBEFOREMARK and STOPATMARK options may be used with the AFTER clause to stop recovery after a given datetime for a named transaction.
Top Of Page
Restoring to a Point of Failure
Under certain circumstances it may be possible to recover the database to a point where it failed. Recovery to a point of failure is available only when the database is in the Full or Bulk-Logged Recovery models. Restoring a database to the point of failure requires the following backup components:
1. A backup of the tail of transaction log.
2. A complete database or file/filegroup backup. Optionally we may also need differential or file differential backups.
3. A sequence of all transaction log backups from the oldest complete (or file/filegroup backup) or the latest differential (or file/filegroup differential) backup restored.
Step 1: Backup of the tail of transaction log
To perform a backup of the tail of transaction log if database files are damaged, see the procedure in article Q253817 in the Microsoft Knowledge Base. As an example, the following command may be used to back up the log when database files are damaged:

BACKUP LOG
TO DISK = 'd:\backup\Tail_TLOG.bak'
WITH NO_TRUNCATE

Step 2: Restore complete database or file/filegroup backup
If the damaged files were backed up as part of a file/filegroup backup, locate the latest file/filegroup backups. If no filegroup backups have been performed or if they are not available, locate the most recent complete backup.
If files from only a certain filegroup are damaged, and filegroup backups along with transaction log backups exist for the damaged filegroup, restore the filegroup backup.
If complete database is damaged or the filegroup backups do not exist for the damaged filegroup(s), restore the most recent complete backup, as explained in “Complete Database Backup” earlier in this paper.
Restore any further differential backups that may have been performed after either the complete backup or the file/filegroup backup.
Step 3: Apply all transaction log backups
If in the previous step a complete backup was restored, restore all transaction log backups performed after the respective complete backup and apply the tail of transaction log performed in step 1.
If file/filegroup backup was restored in the previous step, apply all transaction log backups following the latest file/filegroup or differential file/filegroup backup.
Complete the restore process by applying the tail of transaction log backup in step 1.
Recommended Reading
See the following SQL Server Books Online topic:
Recovering to a Named Transaction
Top Of Page
Differential Backup
A differential backup backs up only modified extents since the last complete backup. By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup.
Differential backup functionality has been enhanced in SQL Server 2000. SQL Server 2000 uses a bitmap that contains one bit for each extent in a database. This bit is set to 1 if any pages within that particular extent have been modified since the previous complete backup. When a differential backup command is issued, SQL Server examines this bitmap. It accesses only the extents that are flagged as having been modified through this bitmap and writes them to a backup file or device. This functionality greatly improves the performance of differential backups.
Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases.
Differential backups have several limitations:
• They do not provide point-in-time restore capabilities.
• Differential backups may not be restored by themselves. They may only be restored after a complete database backup is restored.
• You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database. Differential database backups may not be performed on the master database. If an attempt is made to perform a differential backup on the master database, the following error is raised:
Server: Msg 3024, Level 16, State 1, Line 1
• Differential backups are not allowed for the master database since they require that complete backups be applied without recovery as the first step. When restoring the master database, restore without recovery is not allowed.
It should be noted that although differential backups may provide fast backup times in some situations, using them with highly active OLTP databases may result in backup times that may be comparable to complete database backups. As mentioned before, differential backups back up only extents for which any pages have been modified. If the database activity is such that 80-90% of extents have been modified, the time that a differential database backup may take may be very close to the complete backup.
Top Of Page
Performing Differential Backup
To perform a differential backup
1. Connect to the server and expand Databases.
2. Right-click the database and select All Tasks, then select Backup Database... The dialog box shown in Figure 34 is displayed.

Figure 34: Differential database backup settings
3. In the Database list box, select the database name for which to perform a differential backup. Optionally, enter a name in the Name text box and/or description for the backup in the Description text box.
4. To perform the differential backup, select the Database – differential option.
5. Enter a backup file name following the steps outlined in Performing Complete Database Backups.
6. Click the Options tab and select appropriate options for the backup operation.
7. To start the backup operation, click the OK button.
This operation may also be scheduled for periodic execution by checking the Schedule check box. For more information on modifying the default schedule for a job, see Modifying the SQL Server Job Schedule.
While the backup operation is progressing, a progress dialog box, similar to the one in Figure 13, is displayed. Once the backup completes successfully, a confirmation dialog box, similar to the one shown in Figure 14, is displayed.
8. To complete the backup operation, click OK in the confirmation dialog box.
This task may also be achieved through Transact-SQL commands. The following command would perform the same operation as in the previous procedure:

BACKUP DATABASE Northwind
TO DISK = 'D:\backups\nwind\nwind_diff.bak'
WITH DIFFERENTIAL

Top Of Page
Restore to the Same Server
For the purposes of this section, we will continue from the complete database restore performed in Restoring a Complete Backup to the Same Database. The only difference would be that we would need to select the Leave database non-operational but able to restore additional transaction log backups option in the Recovery completion section in the Options tab on the dialog box displayed in Figure 18.
To restore to the same server
1. Open Enterprise Manager and connect to the server.
2. Right-click the database for which the differential backup is to be restored and select All Tasks, then select Restore Database... to display the dialog box shown in Figure 35.

Figure 35: Differential database restore settings
3. In the Parameters section, select Northwind database in the Show backups for database list box, since this is the source database for which the backups are to be applied.
4. Select the time when the backup was performed in the First backup to restore list box.
The window below this list is now refreshed with all the backups that have been performed from the selected entry in Step 4. In this particular case, there is a single complete backup and a single differential backup.
5. Since the complete backup has already been restored, select only the differential backup in the list. Database properties may be viewed by selecting the backup and clicking the Properties button.
6. Click the Options tab and select appropriate options. For more information on these options, see the descriptions listed for Figure 18.
For the purposes of this demo, we will assume that the database needs to be recovered upon successfully applying the differential backup.
7. To start the restore operation, click the OK button.
A progress dialog box similar to the one shown in Figure 19 is displayed while the restore operation is progressing. Upon successful completion of the restore operation, a confirmation dialog box, similar to the one displayed in Figure 20, is displayed.
8. To complete the restore operation, click the OK button.
The above operation may also be carried out by using the following Transact-SQL command:

RESTORE DATABASE Nwind_New
FROM DISK = 'd:\backups\nwind\nwind_diff.bak'
WITH RECOVERY

Top Of Page
Restore to a Different Server to a New Database
The procedure to restore a differential backup to a different server differs from the steps explained in Restore to the Same Server in the “Differential Backup” section in this paper because the backup history does not exist.
Another means to have the database restored to a different server would be to load the backup history for this database using the procedure described in Adding Backup History from Backup Files to MSDB and then use the SQL Enterprise Manager GUI to restore the backup using the procedure explained in Restoring a Complete Backup to a New Database on the Same Server.
For the purposes of this example, we will assume that a complete backup has been restored without recovery. See the procedure explained in Restoring a Complete Backup to a New Database on the Same Server; however, select the Leave Database Non-operational but able to restore additional transaction logs radio button shown in the dialog box in Figure 22.
To restore to a different server to a new database
1. Open Enterprise Manager and connect to the server.
2. Expand databases. Right-click the database to which the differential backup has to be applied, and select All Tasks, then select Restore Database...
The dialog box shown in Figure 36 is displayed.

Figure 36: Restore differential backup to new database settings
3. In the window that is displayed, select the From device option in the Restore section.
4. Add the backup device in the Devices list following the procedure explained for Figure 24.
5. Select Database – differential under Restore backup set.
6. Click the Option tab and select appropriate options for the restore operation.
7. To start the restore operation, click the OK button.
A progress dialog box is displayed while the restore is being performed. When the restore completes, a confirmation dialog box is displayed.
8. To complete the restore operation, click OK in the confirmation dialog box.
Top Of Page
File/Filegroup Backup
SQL Server organizes a database in several files. By default, a new database is created with one data file and one log file. Other data files may be added either while creating the database or once the database is operational. The database files for SQL Server typically will have .mdf or .ndf file extensions. Ideally, only the first database file that SQL Server creates should have the .mdf file extension. This is a special database file since it contains a database header and all the system tables. All other database files should ideally be given an extension of .ndf.
A filegroup is a logical grouping of SQL Server database files. By default, SQL Server creates a filegroup called Primary. SQL Server adds the first database file (with the extension of .mdf) to the Primary filegroup. Other filegroups may be created at the time the database is created or after the database has already been created. Database files should be added to the filegroup at the time they are created. Once a file has been created and added to a database, the filegroup that it belongs to may not be modified.
SQL Server provides the functionality to back up individual files/filegroups within a database.
Note A file/filegroup backup does not back up the transaction log portion of the database. This is a significant difference between file/filegroup backup and complete backup. Hence a filegroup backup cannot be used as the first step in a recovery situation.
File/filegroup backups may be used to restore the individual file/filegroup if some or all of the files within the respective filegroup are damaged. The following restrictions are enforced when using file/filegroup backups:
• A file/filegroup backup may only be restored to the same database it was backed up from.
• All transaction log backups (including the tail) for the database should be available and restorable. It is implied that this would not be possible to do on a database that is in Simple Recovery model.
• Point-in-time recovery is not permitted when restoring file/filegroup backups. All transaction log backups have to be restored in their entirety.
Top Of Page
Performing File/Filegroup Backup (Enterprise Manager)
To create the database used in the examples in this section, use the script provided in Appendix A.
To perform a file/filegroup backup
1. Open Enterprise Manager and connect to the server where the database resides.
2. Right-click the database and select All Tasks, then select Backup database. The dialog box shown in Figure 37 is displayed.

Figure 37: File/filegroup backup general settings
3. Select the File and filegroup radio button in the Backup section of the dialog box. Click the ellipses button next to this option. This opens the dialog box shown in Figure 38.

Figure 38: Specify file/filegroup to back up
4. In the Specify Filegroups and Files dialog box, select the files/filegroups that you would like to back up. For the purposes of this example, we will back up filegroup fg1. Click the OK button once the files/filegroups have been selected.
5. Click the Add button in the SQL Server Backup dialog box. To add the destination file for the backup, follow the steps for Figure 11.
6. Click the Options tab in the SQL Server Backup dialog box. Select options as necessary. For more information on the options in this dialog box, see the explanation for Figure 12.
7. When you are ready to perform the backup, click the OK button.
A progress dialog box is displayed while SQL Server performs the backup. When the backup finishes successfully, a confirmation dialog box is displayed.
8. Click the OK button in the confirmation dialog box.
Filegroup backups may also be accomplished with Transact-SQL commands. The above functionality could also have been achieved by using the following Transact-SQL command:

BACKUP DATABASE files
FILEGROUP = 'fg1 '
TO DISK = 'd:\backups\files\fg1.bak' WITH INIT

Top Of Page
Restoring Filegroup Backups if Filegroup Files are Damaged
For this section we will assume that the disk where the database files for filegroup fg1 were stored was damaged and we lost both files for this filegroup.
To restore filegroup backups if files are damaged
1. Open an SQL Query Analyzer window and connect to the server where the files database resides.
2. Select master database from the list box and enter the command shown in Figure 39. (If this step is not possible due to damage to the transaction log, the entire database must be restored from the complete backup, and then all transaction log backups should be restored to bring the database up to the point of the last successful log backup.)

Figure 39: Perform backup of tail of transaction log
3. Right-click the files database and select All Tasks, then select Restore Database...

Figure 40: Restore file/filegroup backup
4. Select the Filegroups or files radio button.
5. Make sure that the files database is selected in the Show backups of database: list box.
6. Select the Select a subset of backup sets check box. The Filter Backup Sets dialog box shown in Figure 41 is displayed.

Figure 41: Filter Backup Sets dialog box
7. In the Filter Backup Sets dialog box, select Only backup sets of the following filegroups and files:
8. Check the fg1 filegroup in the list of files/filegroups that is displayed. Notice that if a filegroup is selected, all the files belonging to that filegroup are selected automatically.
9. Click the OK button in the Filter Backup Sets dialog box.
The list of backups for this database, displayed in Figure 40, is now updated to meet the selected criteria. The latest backup should now be selected in the displayed list.

Figure 42: Restore file/filegroup dialog with selected backups
10. Click the Options tab and select options as necessary. For more information on the options available while restoring, see the explanation for Figure 18.
11. To start the restore operation for the filegroup, click OK.
12. Once the filegroup is restored successfully, repeat the process and restore all the transaction log backups, selecting the tail that we backed up in Step 2 as the last log that is restored.
This completes the entire process of restoring the filegroup fg1 that was lost in a disaster.
Top Of Page
File/Filegroup Differential Backups
Differential backups may be combined with file/filegroup backups to back up only the modified extents within an SQL Server database file or filegroup.
These backups may decrease the recovery time in disaster situations where only certain file/filegroups are lost. The latest file/filegroup differential backup contains all changes from all previous file/filegroup differential backups, since they are cumulative in nature. Transaction log backups performed after the latest file/filegroup differential backups are only required in this situation.
File differential backups have the following restrictions:
• They are allowed only in conjunction with file/filegroup backups.
• They are not allowed for databases that use the Simple Recovery model.
• They require all transaction log backups (just like file/filegroup backups), including the tail of transaction log.
It is not recommended that file differential backups be used with differential backups, as explained in the previous section of this paper.
Note It is highly recommended that customers familiarize themselves with file/filegroups and the backup options associated with file/filegroups before planning or implementing filegroups in production databases.
Performing a filegroup differential backup
As previously mentioned, file/filegroup differential backups are available only in conjunction with file/filegroup backups. Consider a database, called FilegroupsDB that has a filegroup called fg2, for which we have performed a backup of filegroup fg2. The following commands demonstrate how we can perform a filegroup and a filegroup differential backup for such a database.

BACKUP DATABASE FileGroupsDB
FILEGROUP = 'fg2'
TO DISK = 'd:\backups\filegroupsdb\fg2\fg2_complete_1.bak'
BACKUP DATABASE FileGroupsDB
FILEGROUP = 'fg2'
TO DISK = 'd:\backups\filegroupsdb\fg2\fg2_complete_1.bak'
WITH DIFFERENTIAL

Restoring filegroup differential backups
The procedure is similar to restoring the file/filegroup backups. The only difference is that now we apply the file/filegroup differential backup following the file/filegroup backup. The steps in a typical recovery situation where a particular filegroup is lost would include:
1. Back up the tail of the transaction log.
2. Restore the filegroup backup and recreate the lost files.
3. Apply the latest filegroup differential backup for the specific filegroup.
4. Apply all transaction log backups following the filegroup differential backup.
5. Apply the tail of the transaction log performed in step 1.
Recommended Reading
See the following SQL Server Books Online topics:
Using File Backups
File Differential backups
See the following Microsoft Knowledge Base articles:
Q253817 INF: How to Back Up Last Transaction Log When Files are Damaged
Q281122 INF: Restore File and Filegroup Backups in SQL Server
Top Of Page
Backup and Restore of Full-Text Catalogs
Full-text searching in SQL Server is implemented using the Microsoft Search service. Full-text indexes are stored outside of SQL Server database files. Full-text indexes cannot be backed up by using the Transact-SQL commands or Enterprise Manager.
Recommended Reading
The following Knowledge Base article discusses the procedure to back up and restore full-text catalogs used by SQL Server.
Q240867 INF:How to Move, Copy, and Back Up Full-Text Catalog Folders and Files
The following white paper discusses the details regarding disaster recovery options for full-text indexes in SQL Server.
SQL Server 2000 Full-Text Search Deployment White Paper.
See the following SQL Server Books Online topics:
Database Backups
Full-Text Indexing Support
Full-Text Indexes
Full-Text Catalogs and Indexes
Full-Text Search Recommendations
Top Of Page
Backup and Restore of Replicated Databases
This section covers the backup and restore implications and requirements for SQL Server databases that participate in replication. It is not in the scope of this section to discuss the replication topologies. Please see the recommended reading at the end of this section to gain more knowledge regarding these topologies.
It is highly recommended that for any recovery strategy involving replication, a script should be generated for the replication topology at the time the replication environment is set up and each time it is changed. This script may be generated by using SQL Enterprise Manager. The script should be stored with other backup files.
To script replication
1. Open SQL Enterprise Manager and connect to the Publisher server.
2. Right-click the Replication folder and select Generate SQL Script.
It should be noted that when restoring a replicated database from backup to a new server name or a new database name, all replication settings are lost. Use the KEEP_REPLICATION flag to preserve the replication settings during the restore process. For more information on this setting, see “RESTORE” in SQL Server Books Online.
3. Generally it is considered a good practice to let the replication agents finish the current replication workload, so that Publisher and all Subscribers are in sync. Once the Subscribers are in sync, perform the following steps:
1. Stop the distribution/merge agent(s).
2. Back up the Publisher, Distributor, Subscriber(s), and other databases.
Another consideration when designing a backup strategy for replicated databases should be the distribution retention period. The distribution retention period refers to the time that elapses before a transaction is discarded at the Distributor. This setting is relevant in scenarios where transactional replication is being used. This setting can be configured through SQL Server Enterprise Manager or by using the stored procedure sp_changedistributiondb. Ideally this retention period should be configured to a number greater than the frequency of backups of the distribution database. Configuration in this manner would ensure that none of the transactions expire before a backup is performed.
Top Of Page
Snapshot Replication
Snapshot replication consists of a replication where the entire publication is replicated to all Subscribers as a single snapshot. This is best used for data that changes infrequently and where publications are relatively small sized.
Publication
Back up the published database(s) only when there are changes to the existing publications or new publications are added. A complete backup is preferred.
Distribution
Back up the distribution database at the same time as publication. Perform the distribution cleanup task before performing backup. Refrain from adding new snapshot publications or subscriptions at the time the distribution database is being backed up.
MSDB database
Perform backup whenever any of the changes listed below occur on the Publisher, Subscriber, or Distributor server(s):
• A subscription is dropped or added.
• A replication agent is modified.
• A new Publisher is added to the Distributor.
Master database
Perform a backup of master on the Publisher and Distributor each time a new Subscriber or Publisher is added.
Top Of Page
Transactional Replication
Transactional replication provides a mechanism to propagate changes made to a published database through the transaction log over to the Subscribers. Log Reader Agent reads the transaction log of the published database and transfers the changes to the distribution database. Distribution Agent propagates the transactions to the Subscribers.
In order to re-establish transactional replication in a disaster recovery situation, it is vital that the publication and distribution databases be restored to a consistent point in time. SQL Server 2000 handles this task automatically. SQL Server provides an option “sync with backup” which should be set to TRUE on publication and distribution databases. If this option is set to TRUE, all publication and distribution databases should be backed up at frequent intervals, since the frequency of backups determines the latency with which the changes are propagated to the Subscribers.
Publication database(s)
If the “sync with backup” option is turned on for the publication database, Log Reader Agent does not propagate the transactions until they have been backed up at the Publisher. This means that the publication database with the “sync with backup” setting turned on would have to be backed up at frequent intervals. Any type of backup (including transaction log, differential etc) can be performed for the publication database.
This mechanism helps if a backup has to be restored for the Publisher in a disaster situation. Since Log Reader Agent does not propagate any transactions that have not been backed up to the distribution database, there is no possibility of the distribution database having transactions that are not present on the Publisher, in case the publication database has to be restored from backups. There is no further synchronization required if the “sync with backup” setting was turned on before performing the backup.
If the “sync with backup” is turned off, there is no way to guarantee the consistency of data between Publisher, Distributor, and Subscribers. From a backup/restore disaster recovery perspective, it is recommended that the “sync with backup” setting be set on.
If the “sync with backup” setting is turned off, Log Reader Agent may transfer data faster than it is being backed up. If the Publisher fails and is restored from a backup, the distribution database may already have transactions that are yet to be performed after the Publisher is restored from backup.
Distribution Database
If the “sync with backup” option is used, back up the distribution database as often as possible. This will insure that the transaction log for the publication database is being truncated regularly. Frequent backups of the distribution database however, do not affect the replication latency when the “sync with backup” setting is turned on. In case of a disaster, restoring the latest backup of the distribution database will ensure trouble-free replication.
If the “sync with backup” setting is not used, there is no way to guarantee the transactional consistency between the publication, distribution, and subscription databases. For quick recovery, it is recommended that this setting be used on any distribution database involved in transactional replication.
MSDB database
MSDB databases on the Distributor and Subscribers should be backed up whenever a subscription is added or removed or a change is made to any of the replication agents. If replication involves DTS packages to transform the data (in the case of transformable subscriptions), the MSDB database on the Distributor and Subscribers should also be backed up any time:
• There is a change to any of the DTS packages used for replication.
• A new subscription is added or an existing subscription is dropped.
Master database
Back up the master database at the Publisher and Distributor when a new Publisher and/or subscription is added.
Subscription database
Subscription databases should be backed up at least once every interval set as the minimum transaction retention period for the Distributor. This ensures that in case of a disaster where a subscription database is lost, it may be restored without loss of any transactions.
Top Of Page
Merge Replication
Publisher
A publication database should be backed up each time there is a change to the following:
• The replicated object’s schema definition
• A Publication Property
For a detailed list of actions requiring an updated backup, see “Strategies for Backing up and Restoring Merge Replication” in SQL Server Books Online.
Distributor
A distribution database’s role in merge replication topology is limited to synchronization history and error tracking store. If a distribution database is associated only with merge publications, it is not always necessary to restore a distribution database at the same time as the Publisher. A distribution database may be backed up as often as possible; however, this is not a requirement. In the case where a distribution database fails, the most recent backup may be restored. If the backup is not up-to-date, the information that is lost will relate to the history and error tracking for the merge publications and subscriptions.
Subscriber(s)
It is recommended that a Subscriber be synchronized with the Publisher before backup. It is also recommended that all subscription databases be backed up at least as often as the retention period on the Publisher. If these two recommendations are followed, any subscription database may be restored in case of disaster without any further actions. If a Subscriber subscribes to more than one publication having different retention periods, the backup for that subscriber should be performed as often as the shortest retention period out of all publications.
MSDB database
Back up the MSDB database at the Distributor and Subscribers at regular intervals. It is recommended that these backups be performed regularly or whenever any of the following have been modified:
• Any maintenance activity involving a change to replication agent settings on the Publisher, Distributor, or Subscribers.
For a detailed list of changes that would prompt an MSDB backup, see “Strategies for Backing up and Restoring Merge Replication” in SQL Server Books Online.
Master database
The master database for all components (Publisher, Distributor, and Subscribers) should be backed up periodically. Specifically, a backup should be performed after any change to the replication configuration.
Recommended Reading
See the following SQL Server Books Online topics:
Replication Overview
Replication Architecture
Backing up and Restoring Replication Databases
See the following SQL Server 2000 white paper on the Microsoft Developer Network (MSDN):
Transactional Replication Performance Tuning and Optimization
Top Of Page
Set Up Backup Schedules (Maintenance Plan Wizard)
The Maintenance Plan Wizard utility allows for a backup to be scheduled through the Graphical User Interface. This wizard may be invoked through SQL Enterprise Manager.
To schedule a backup using the Maintenance Plan Wizard
1. Right-click the database and select All Tasks, then select Maintenance Plan... as shown in Figure 43.

Figure 43: Start Maintenance Plan Wizard
The dialog box shown in Figure 44 is displayed.

Figure 44: Maintenance Plan welcome dialog box
2. Click Next> to proceed to the next screen in the wizard. The dialog box shown in Figure 45 is displayed.

Figure 45: Select Databases dialog box
3. Verify that Northwind database is selected. If the maintenance plan should include other databases, check them in the list.
4. Click Next> to proceed to the next screen, shown in Figure 46.

Figure 46: Specify data optimization settings
5. The dialog box in Figure 46 provides data optimization options. All operations in this dialog box are carried out at the same time. The default schedule indicates that it will be executed every Sunday at 1:00 A.M. If this is incorrect, modify the schedule by clicking the Change... button.
Description of other fields
Reorganize data and index pages – causes the indexes in the database to be rebuilt with either the default or provided fill factor value.
Update Statistics used by query optimizer – causes the statistics for each index on user tables to be resampled. Sampling size is based on the percentage value supplied. This option is unavailable when Reorganize data and index pages is selected because statistics are recalculated automatically when the indexes are rebuilt.
Remove unused space from database files – causes the database to be shrunk when this maintenance plan job executes. This operation will be carried out if the database file size grows beyond the supplied value. Amount of free space to remain after shrink signifies the percentage of original database size that should be left empty after the shrink operation.
6. Click Next> after selecting fields as necessary. The dialog box shown in Figure 47 is displayed.

Figure 47: Specify database integrity check settings
7. This dialog box presents the options available to perform a database integrity check. All operations in this dialog box are carried out at the same time. The default schedule indicates that it will be executed every Sunday at 12:00 A.M. If this is incorrect, modify the schedule by clicking the Change... button.
Description of other fields
Include Indexes – scans indexes on all tables in the selected database(s) for consistency and allocation errors. The Attempt to repair minor problems setting causes SQL Server to use REPAIR_FAST option to try and correct any minor problems that are found in the scan. The use of this option causes the maintenance plan to place the database in single-user mode for the repair option.
Perform these checks before doing backups – causes SQL Server to perform this check before a backup is performed for the database.
8. Click the Next> button and the dialog box shown in Figure 48 is displayed.

Figure 48: Specify database backup plan
9. This dialog box presents all options for a complete database backup. The complete database backup operation for all selected databases is carried out at the same time. The default schedule indicates that it will be executed every Sunday at 2:00 A.M. If this is incorrect, modify the schedule by clicking the Change... button.
Description of other fields
Verify the integrity of the backup when complete – causes SQL Server to perform a verification of the backup once it completes. SQL Server executes the RESTORE VERIFYONLY command to perform the verification.
Either a tape or a disk device may be selected as the target.
10. Click the Next> button to proceed to the dialog box shown in Figure 49.

Figure 49: Specify database backup directory
11. The dialog box shown in Figure 49 is where the target path for a complete database backup is specified. This dialog box is only displayed if Complete database backup is selected in the previous dialog box.
Description of other fields
Directory in which to store the backup file – target directory for complete database backups. Default directory resides within the SQL installation path under the BACKUP folder.
Create subdirectory for each database – causes a subfolder to be created for each database selected in the maintenance plan.
Remove files older than – removes any backup files older than the configured age when the maintenance plan job is executed.
Backup file extension – file extension used for the complete database backup files. By default, this is .bak.
12. Click Next> and the dialog box shown in Figure 50 is displayed.

Figure 50: Specify Transaction log backup plan
13. The dialog box shown in Figure 50 presents the option to back up the transaction log for all the selected databases. Transaction log backup operations for all selected databases are carried out at the same time. The default schedule indicates that it will be executed Monday – Saturday at 12:00 A.M. If this is incorrect, modify the schedule by clicking the Change... button
Description of other fields
Verify integrity of the backup when complete – causes SQL Server to execute RESTORE VERIFYONLY to verify the backup once it is complete.
Location to store the backup file – either a tape or a disk device may be used to perform transaction log backups.
14. Once all the appropriate selections are made, click Next > to view the next dialog box in the wizard as shown in Figure 51.

Figure 51: Specify Transaction log backup directory
15. The dialog box shown in Figure 51 displays configuration settings for the transaction log backup files. This dialog box is displayed only if Transaction log backup is selected and the target is a disk.
Description of other fields
Use the default backup directory – causes SQL Server to perform the transaction log backup to the default backup folder. This folder is under the SQL Server installation path.
Use this directory – any folder can be specified if using the default folder is not wanted.
Create a subdirectory for each database – causes SQL Server to create a subfolder for each database that is configured for this maintenance plan.
Remove files older than – backup files older than the age specified are removed from the backup folder.
Backup file extension – the extension that is given to the transaction log backup files. The default extension is .trn.
16. Click Next> to continue to the dialog box shown in Figure 52.

Figure 52: Specify report generation settings
17. This step is optional. It prompts users to save an output file from the maintenance plan. Although this step is optional, it is highly recommended that a report be generated and inspected on a regular basis to spot any failures that might have occurred.
Description of other fields
Delete text report files older than – deletes any files older than the age specified.
Send email report to operator – generates an e-mail message and sends it to the nominated operator. This setting appears dimmed (unavailable) in the illustration in Figure 52 because there were no operators defined on this server.
18. Once all the options are selected, click Next> to continue the Maintenance Plan Wizard. The dialog box shown in Figure 53 is displayed.

Figure 53: Specify Maintenance plan history settings
19. This dialog box presents the option to log history records for the execution of this maintenance plan. Logging history records helps in troubleshooting.
Description of other fields
Limit rows in the table to - the number of rows that are kept in the history table. If the number of rows exceeds this number, the oldest records are deleted.
Remote server - History records may be logged to a remote server’s MSDB database. If the Write history to the server: check box is selected, another server may be selected as the keeper of the job history for this maintenance plan.
20. Click the Next> button to continue. The dialog box shown in Figure 54 is displayed.

Figure 54: Maintenance plan summary dialog box
21. This is the final dialog box in the wizard. It displays the summary of all options selected and allows you to enter a name for this maintenance plan. After verifying the options and entering a name, click the Finish button to create the maintenance plan.
Top Of Page
Modifying the SQL Server Job Schedule
If any of the task schedules have to be changed, the dialog box shown in Figure 55 is displayed. This section explains the fields in this dialog box.

Figure 55: Recurring schedule modification dialog box
This dialog box allows a job schedule to be changed to meet the requirements for the individual job.
1. A job may be scheduled to run daily, weekly, or monthly. Select the appropriate radio button in the Occurs section. The option labeled Weekly in the dialog box changes when the Daily or Monthly option is selected. Figure 56 shows the changed window.

Figure 56: Modify default schedule dialog

Figure 57: Modify default schedule dialog
2. Select the appropriate frequency in the dialog box displayed in Figure 55. A job may be scheduled up to a future date/time or else indefinitely. To schedule a job to end on a specific date, select the End date radio button in the Duration section and provide the date.
3. Finally, by default, a schedule is enabled. If this schedule needs to be disabled and re-enabled at a later date, uncheck the Enable schedule check box.
4. To save the schedule, click the OK button.
Top Of Page
Backup Techniques for Large Databases
SQL Server 2000 includes enhancements to improve the backup and restore performance of large databases. A variety of techniques may be used to improve the throughput of backup/restore operations.
Speeding up backups
Backups are one of the most important maintenance tasks that need to be performed at regular intervals. However, under certain circumstances database administrators tend to neglect this task. These circumstances may include the size of the database, usability, and uptime requirements. Although SQL Server does not require that a database be taken offline for the purpose of performing backups, certain operations cannot be performed while backups are running. These operations include file operations (including expansion, shrinking of database and transaction log files, and ALTER DATABASE statements with ADD or REMOVE FILE operations). Because of this, it might be beneficial to have the backup operation complete in the least amount of time possible.
Various techniques may be used to speed up backup performance. They include both hardware and software solutions.
Top Of Page
Backup Infrastructure and System Architecture
Performing backups of large databases (in the order of GBs and TBs) will benefit from having a “backup friendly” machine architecture. Some of the key aspects to consider are covered in this section.
Disk backups
Perform backups to local disks running off separate disk controllers rather than the one that contains the databases.
Format the drives as a lower level of RAID for speed (i.e. RAID 0 or RAID 1). However, this will reduce the fault tolerant capabilities of the disk.
Tape backups
Use multiple tape devices in a media family configuration for maximum throughput.
Run the tape devices off a different controller than the one that holds SQL database files.
Use local tape drives instead of network drives.
Network backups
This mode of backup is not recommended. When performing network backups, several factors are added to the performance equation. These include the speed of the network cards/cables, network architecture and reliability, etc.
If it is imperative that network backups be performed, a private network for backup/restore purposes is preferable. This network should not contain any routers between the source and destination machines. It should also be realized that if at any point network connectivity is lost between source and destination machines, the backup may not complete and it might fail with an operating system error.
Snapshot backups
Snapshot backup involves setting up a three-way mirror for drives that contain SQL Server databases. At the time the backup is performed, one of the mirrors is broken momentarily while a copy of all files on that mirror drive is made to a separate location. The broken mirror is then reestablished back and changes are regenerated from the information on the second mirror drive. Snapshot backups are performed in a very short amount of time and provide the quickest recovery path.
Snapshot backup is developed in conjunction with several hardware vendors. Snapshot backup is applicable only on hardware that supports this functionality. Please see your hardware vendor for information regarding this functionality.
Top Of Page
Software Configuration and Database Architecture
Differential backups
Investigate the timings for differential backups. This kind of backup is extremely versatile and quicker than complete backup. The fact that this backup is also cumulative means that only the most recent backup needs to be maintained. For databases that are updated at regular intervals but do not experience heavy update activity, differential backups may provide an ideal solution.
Database architecture
For databases that experience heavy update activity, differential backups may not be the right choice. In this case the better option would be to include transaction log backups at short intervals. The reason for this is that if enough updates are performed on a database, a differential backup might be as large as the complete backup.
If only certain table(s) are updated heavily and the rest of the database is static, it might be a good idea to create separate filegroups for active and inactive portions of the database. If the database is architected such that there are filegroups, filegroup backups may be implemented to reduce the overall time required for backups. As mentioned before, filegroup backups require transaction log backups for recovery. Hence, transaction log backups would be required if filegroup backups are implemented.
Recommended Reading
See the following SQL Server Books Online topic:
Optimizing Backup and Restore Performance
Top Of Page
About Disaster Recovery Planning
One of the most important functions that an SQL Server database administrator has is to design and implement a disaster recovery plan for the production system. This kind of planning involves planning the following aspects:
• Creating a backup plan for all databases.
• Setting up a warm backup server with the databases that are critical to an organization’s operations.
• Testing the backups at regular intervals.
• Circulating backups for off-site storage.
• Documenting the system and training co-workers in disaster recovery procedures.
• Simulating disaster recovery scenarios to test the timing and value of current procedures.
There are various techniques for designing an efficient disaster recovery scenario. Almost all of these techniques have as their first step:
• Understanding the requirements for disaster recovery for your organization.
• Familiarizing yourself with all the options available in SQL Server, so an efficient strategy may be designed.
Understanding the disaster recovery requirements is an extremely critical step as it lays down the foundation for a robust disaster recovery plan for any organization. The following questions are provided as a guide to the kind of information that should be understood by the person in charge of designing this strategy:
• How critical is the data stored on the database? If any data is lost, is it re-creatable?
• How much information can the organization afford to lose in case of a disaster? What is the maximum downtime that a company can take in case of disaster?
• How much data is modified in the database?
• Is this is a 24x7 (24 hours a day, 7 days a week) system or is there a maintenance/downtime window available on a regular basis?
• What resources are available to design a strategy? This would include resources like a redundant machine, off-site storage facility, etc.
There are several techniques that may be used to design a disaster recovery strategy. Some of these techniques are not discussed since they relate to the core operating system and are outside the scope of this paper. Depending on the kind of disaster a company might be dealing with, backup/restore may serve as the most robust disaster recovery mechanism available.
Since there are several kinds of backups to choose from, a combination usually leads to the quickest recovery path. See the case study in this paper.
Recommended Reading
See the following SQL Server Books Online topics:
Designing a backup restore strategy
Backup and Restore Operations
See the following Microsoft Knowledge Base article:
Q307775 INF: Disaster Recovery Articles for Microsoft SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307775
Top Of Page
Case Studies
Mid-size business – OLTP
This is a mid-size business with 150 employees throughout the United States. SQL Server is used for Inventory, Customer Information, Sales, HR/Payroll, and the development environment. The company uses several SQL Server 2000 servers. The company develops applications in-house. The company uses a total of around 6 GB of database space. The space usage patterns indicate a growth rate of about 100 MB per month across all databases. Three main databases are Inventory (1.5 GB), Sales/Customer Information (2.5 GB), and HR/Payroll (2 GB). There are several development databases that are used for testing. These are placed on the development server and are less than 500 MB in size.
All SQL Server computers are managed by two database administrators (DBAs). One is in San Francisco and one in New York City.
The following table shows more information about each production database.
Database /
Parameter Inventory Sales/Customer HR/Payroll
Size 1.5 GB 3.5 GB 1 GB
Usage Track items sold by company. Track customer orders and shipments Used for employee payroll information
Activity Pattern Most heavily used at the end of each month when an inventory reconciliation is performed and new inventory items are added. Most heavily used during weekdays. Customer orders are added during business hours. Reports are prepared at nights. Moderate use database. Four staff members in HR use this database. Moderate amount of modifications and reports prepared during working days.
Disaster Recovery Requirements Critical information stored in the database. Information may be recreated with some effort. Company operations may be affected severely if information is not recreated within 1 business day. Any new information added at the end of month should not be lost. Information added during the month may be recreated. High usage and visibility database. Critical to company operations. Require point-of-failure recovery. This system should be operational within 20-30 minutes if outage happens during working hours. No data loss is acceptable. Information maybe recreated with some effort. Information is important to company’s operation. Outage during weekdays will not affect core business. The database will need to be operational within 1-2 business days. Loss of 1-2 days worth of information may be acceptable.
The disaster recovery requirements highlight the need to use several techniques to implement the disaster recovery plan for these production databases. Following are some suggestions on how to design the recovery strategy for each database.
Minimum disaster recovery plan for Inventory database
Database may be Simple Recovery model. This provides minimum operational overhead. Transaction log is maintenance free.
Set up a complete database backup after the end-of-month reconciliation is performed. This is critical as one of the requirements is that end-of-month changes should not be lost.
Set up a differential database backup to be performed each night. This will ensure that all changes are backed up without the overhead of maintaining all previous differential backups. Refresh the differential backups each time a complete backup is performed.
In case of a disaster, restore the previous month’s complete backup and then apply the latest differential backup. Any data lost between the last differential backup and disaster time may be recreated.
Database backups and differential backups should be tested at regular intervals to ascertain recoverability and verify that disaster recovery is possible within the allocated time window.
Added recommendations for Inventory database
Perform a complete backup more often than once a month. If disk space is not a factor, a complete backup may be performed each week, with differential backups being performed every night.
If disaster recovery requirements change, the recovery model may be switched to Bulk-Logged or Full and transaction log backups may be performed at regular intervals between successive differential backups.
Since the data is critical to company operations, it might be a good idea to implement warm backup using Log Shipping. However if this solution is to be implemented, the recovery model for the database should be switched to Full or Bulk-Logged. For more information on implementing Log Shipping, see the following section on the Sales database.
Minimum disaster recovery plan for Sales/Customer database
Database should be Full Recovery model. This provides maximum recoverability in case of disaster.
Perform a complete database backup each night (at 10 P.M.).
Perform differential backups twice a day (at 11 A.M. and 4 P.M.).
Perform transaction log backups every 10-15 minutes.
In case of a disaster, restore the previous night’s complete backup, apply the latest differential backup, and apply any further transaction log backups performed after the latest differential backups.
Database, differential, and transaction log backups should be tested at regular intervals to ascertain recoverability and verify that disaster recovery is possible within the allocated time window.
Added recommendations for Inventory database
Since this database is extremely critical for the company’s operation, it is highly recommended that Log Shipping be used to set up a warm backup server. Log Shipping provides complete environment redundancy and may be beneficial in getting the systems functional before the 20-30 minute time window available to get the system functional again.
Setting up Log Shipping may also provide the benefit of a redundant report server. Report generation may be load-balanced between the two servers during non-working hours.
Due to the existing geographical location of company offices, it might be beneficial to set up the log shipping server.
Minimum disaster recovery plan for HR/Payroll database
All the recovery requirements are roughly the same as the Inventory database, so a similar plan could be implemented for this database as well.
All backups should be tested at regular intervals to ascertain recoverability and verify that disaster recovery is possible within the allocated time window.
Added recommendations for HR/Payroll database
Have the database use Full or Bulk-Logged Recovery model.
Perform a complete database backup once every week.
Perform a transaction log backup each night. Refresh the transaction log backups after the complete database backup is successful.
These steps will reduce the recovery time window as well as minimize the data loss in case of disaster.
Large Organization, 24x7 (24 hours a day, 7 days a week) System
This e-commerce organization uses SQL Server for their “.com” business. The company sells books, magazines, and music CDs through their Web site. SQL Server databases are used for the OLTP system for the e-commerce site. The database experiences a high volume of transactions at any given time. Usually the transactional activity is lowest during night hours.
The organization uses DTS packages to download data into another database each night. This data is then used for reporting purposes. The DTS package selects the data to move into the report server database. The data in the report server database is refreshed each night.
The operations for this organization are divided up in four separate databases: OnlineContent, Inventory, Sales, and Customers. All databases experience a high level of transactions (OnlineContent is the most active database). The total database size is about 300 GB. These databases work together to form the entire e-commerce platform.
Disaster recovery requirements for this organization include the following:
• Downtime of less than a minute for all databases in case of total machine failure. If the downtime is more than a minute, the losses are estimated at $200 per minute.
• No data loss acceptable for any of the databases.
Following is some background information on the hardware environment:
• Backup media is an Ultra Wide SCSI tape library.
• Databases are placed on a fiber-optic SAN drive with 2 TB of total drive space.
• Microsoft Windows® 2000 Clustering in Active/Active configuration is used to protect against single machine failure.
Disaster recovery planning
It is important to understand the major requirement for disaster recovery is that the company starts losing money if downtime is more than a minute.
It is also a major requirement that the company should not lose any data in case of hardware failure. This requirement means that the disaster recovery plan should include complete machine redundancy using Log Shipping.
Recommendations
To provide a quick backup/restore mechanism, snapshot backups should be implemented. Snapshot backups perform extremely fast complete database backups and equally fast recovery if needed.
Along with snapshot backups, a tape backup of the .mdf/.ldf files taken would be beneficial for off-site storage.
Log Shipping could be implemented as a solution to set up a standby server in case a complete machine failure takes down all nodes of the cluster or the shared drives.
Databases should be in Full Recovery model and transaction log backups should be performed every few minutes. A 3-5 minute interval may be used to start off and if this interval seems too small, it may be increased to 5-7 minutes.
Along with the four production databases, MSDB and master should be backed up, because they contain information relating to the scheduled jobs and logons.

Read More