12-06-2012, 05:16 PM
SQL admin
Sql Admin(Page1).docx (Size: 30.17 KB / Downloads: 25)
introduction
Microsoft SQL Server 2005 provides the Microsoft Windows Server System integrated server software with a database platform for the next generation of connected, scalable, and reliable enterprise applications. The breadth and depth of innovation in this version is in response to the needs of customers. This white paper is targeted to database administrators, to give you an understanding of the new features in and capabilities of SQL Server 2005. From many enhancements of existing features, to an entirely new security model, database administration is now more productive and in tune with the needs of the administrator.
The world of the DBA is changing. DBAs now increasingly are asked by the organizations they work for to manage more systems with larger amounts of data, while at the same time organizations are trying to keep IT costs the same or even to lower costs. Simultaneously, DBAs are being asked to give more of their time to help corporate developers to understand data models and the best data access paradigms for building reliable applications that perform to the scale that the corporation requires. With these tensions, DBAs need more from their database technologies in terms of enterprise manageability, scalability, and availability. SQL Server 2005 provides the bedrock foundation that DBAs can bet on to build their next generation IT infrastructure.
At the core of SQL Server 2005 are new infrastructure application capabilities. SQL Service Broker is a distributed application framework that provides a new form of scalability and reliability for asynchronous message delivery. Though not new, Microsoft SQL Server Notification Services, Reporting Services, and SQL Server Mobile Edition (formerly called SQL Server CE) are all greatly enhanced in SQL Server 2005.
SQL Server 2005 offers a plethora of new features and enhancements that improve productivity, efficiency, availability, and administrative ease. In this project, I discuss four of the more advanced features of the SQL Server 2005 Database Engine.
backups
Microsoft, in SQL Server Books Online, defines backups as:
“A copy of data that is used to restore and recover data after a system failure.”
SQL Backups can be created a number of ways and can incorporate all or some of the data, as well as some part of the transaction log. While this article is focused on 2005 syntax, most of the concepts are applicable to 2000. After reading this, you should be able to set up a reasonable set of backups for your system.
Recovery Models
In order to begin working on backups, the business needs define a database recovery model. In essence, a recovery model defines what you're going to do with the transaction log data.
There are three recovery models: Full, Simple and Bulk Logged. These are pretty easy to define:
Simple – in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.
Full – in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.
Bulk Logged – in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.
The two most commonly used modes are Simple and Full. Don't necessarily assume that, of course, we always need to use Full recovery to protect our data. It is a business decision. The business is going to tell us if we need to recover to a point in time or if we simply need the last full backup. It's going to define if our data is recoverable by other means, such as manual entry, or if we have to protect as much as possible as it comes across the wire. We use Simple recovery if we can afford to lose the data stored since the last full or differential backup and/or we just don't need recovery to a point in time. In Simple mode, we must restore all secondary read/write file groups when we restore the primary. We use Simple mostly on secondary databases that are not an absolute vital part of the enterprise or reporting systems, with read only access so there isn't a transaction log to worry about anyway. We use Full if every bit of the data is vital, we need to recover to a point in time or, usually in the case of very large databases (VLDB), we need to restore individual files and file groups independently of other files and file groups.
With both Simple and full recovery models, we can now run a Copy-Only backup which allows we to copy the database to a backup file, but doesn't affect the log, differential backup schedules or impact recovery to a point in time. I'll try to drill down on as many of these topics as possible through the article, but not the files and filegroups.
Working with Simple Recovery
Enough talk. Let's get down to running backups. Let's assume that we're in Simple recovery on a small to mid-sized database. I'm going to use AdventureWorks for all the sample scripts. To set it to simple recovery:
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
Your simplest backup strategy is to run, at regular intervals, the following SQL Server backup command, which will perform a full backup of the database:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.BAK'
The above command will precipitate a basic backup to disk. Most DBAs backup to file and then scrape the files onto a tape or some other media. This is because files on disk are simple and quick to recover, whereas media can sometimes be a bit of a pain. For example, we generally have two to three days worth of backups on our file systems for immediate recovery. We only go to the tape systems if we need to run restores for older backups.
What did that command do? It made a copy of all the committed data in the database. It also copied uncommitted log entries. These are used during recovery to either commit or rollback changes that occurred to the data during the backup process.
Copy-only backups
Normally, backing up a database affects other backup and restore processes. For example after running the previous command, any differential backups (a backup that only copies data changed since the last backup) would be using this as the starting point for data changes, not the backup we ran last night. As noted earlier, SQL 2005 introduces a new concept to backups, COPY_ONLY backups, which allow us to keep from interrupting the cycle:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH COPY_ONLY;
Differential backups
Let's assume for a moment, that we're still in simple recovery, but we're dealing with a larger database, say something above 100 GB in size. Full backups can actually start to slow down the process a bit. Instead, after consultation with the business, we've decided to do a weekly full backup and daily differential backups. Differential backups only backup the data pages that have changed since the last full backup. Following is the SQL backup command to perform a differential backup:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\backups\AdventureWorks.bak'
WITH DIFFERENTIAL;
Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\backups\AdventureWorks.bak'
WITH INIT;
There are a number of other backup options that I won't be detailing here. Read the books online to see details on BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NAME, STATS, and so on.
You can also run a statement that will check the integrity of a database backup. It doesn't check the integrity of the data within a backup, but it does verify that the backup is formatted correctly and accessible.
RESTORE VERIFYONLY
FROM DISK = 'C:\backups\Adventureworks.bak'
Full recovery and log backups
We've primarily been working on a database that was in Simple recovery mode (this used to be called Truncate Log on Checkpoint). In this mode, we do not backup the transaction logs for later recovery. Every backup under this mechanism is a database backup. Log backups are simply not possible.
However, you've only protected the data as of the last good backup, either full or differential. Let's change our assumptions. Now we're dealing with a large, mission critical application and database. We want to be able to recover this database up to the latest minute. This is a very important point. In theory, since the log entries are being stored and backed up, we're protected up to the point of any failure. However, some failures can cause corruption of the log, making recovery to a point in time impossible. So, we have to determine what the reasonable minimum time between log backups will be. In this case we can live with no more than 15 minutes worth of lost data.
So, let's start by putting our database in FULL recovery mode:
ALTER DATABASE AdventureWorks SET RECOVERY FULL
Then, on a scheduled basis, in this case every 15 minutes, we'll run the SQL backup command for the transaction log:
BACKUP LOG AdventureWorks
TO DISK = 'C:\backups\AdventureWorks_Log.bak';
This script will backup committed transactions from the transaction log. It has markers in the file that show the start and stop time. It will truncate the log when it successfully completes, cleaning out from the transaction log the committed transactions that have been written to the backup file. If necessary, you can use the WITH NO_TRUNCATE statement to capture data from the transaction log regardless of the state of the database, assuming it's online and not in an EMERGENCY status.
Note that we are not using the INIT statement in this case, but you can do so if you choose. When doing log backups, you've got options:
Run all the backups to a single file, where they'll stack and all you have to do, on restore is cycle through them.
Name the backups uniquely, probably using date and time in the string.
In that latter case, safety says, use INIT because you're exercising maximum control over what gets backed up where, and you'll be able to know exactly what a backup is, when it was taken and from where based on the name. This is yet another place where operating backups from the command line gives you more control than the GUI. We've used both approaches in our systems for different reasons. You can decide what is best for your technology and business requirements.
Most of the options available to the database backup are included in Log backup, including COPY_ONLY. This would allow you to capture a set of transaction data without affecting the log or the next scheduled log backup. This would be handy for taking production data to another system for troubleshooting etc.
If you have your database set to FULL Recovery, you need to run log backups. Sometimes, people forget and the transaction log grows to the point that it fills up the disk drive. In this case, you can run:
BACKUP LOG AdventureWorks WITH NO_LOG;
Attaching NO_LOG to the log backup, and not specifying a location for the log, causes the inactive part of the log to be removed and it does this without a log entry itself, thus defeating the full disk drive. This is absolutely not recommended because it breaks the log chain, the series of log backups from which you would recover your database to a point in time. Microsoft recommends running a full backup immediately after using this statement. Further, they're warning that this statement may be deprecated in a future release.