Microsoft SQL Server 2005 and 2008 Backup With SQL Commands
Posted in Microsoft SQL Server by freetaotaoMicrosoft SQL Server 2005 and 2008 Database backup with sql commands could recovery data efficiently in case of a crash of the data server or MS SQL Server so as to guarantee the entirety and security of data in the MS. Database technology has developing to the Microsoft SQL Server, Sybase SQL Server and Oracle from the dbase, Foxbase, Foxpro, Access, the database size and function become more and more large and powerful. this makes backup and recovery DBA becomes very complicated. If not back up SQL data, as the system have problems (mainly the system failure), it can not recovery lost data. How to backup and recovery SQL Server 2005 and 2008 with SQL commands? The following is the steps.
1, the backup location
In SQL Server 2005 or 2008, there are several backup locations to choose from, such as local disks, network disks, remote address, and tapes. All the backup location has its own advantages and disadvantages.
2, logical backup device
In SQL Server 2005 or 2008, you can create a logical backup device to complete the backup. The benefits of using the logical backup device is you do not need to change
the backup script, just need to change the definition of logical backup device when changing the backup address.
Create a logical backup device script as follows:
SQL Commands
EXEC sp_adddumpdevice @ devtype = ‘disk’, @ logicalname = ‘MYBackup’, @ physicalname = ‘D: \ backup \ mydb.bak’
The script to delete the backup device:
SQL Commands
Sp_dropdevice @ logicalname = ‘MYBackup’
The script is to delete the definition of logical backup device, the following script will also delete the backup files:
SQL Commands
Sp_dropdevice @ logicalname = ‘MYBackup’, @ devfile = ‘DELFILE’
The way of Using logical backup device is as follows:
SQL Commands
Backup database mydb to MYBackup
Of course, on the logical backup device can also specify the backup attribute, such as the expiration time:
SQL Commands
Backup database mydb to MYBackup WITH EXPIREDATE = ’13 / 01/2010 ‘
Or:
SQL Commands
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS = 7
3, the backup set and store set
Each backup contains in a backup set, and a backup set contained in a storage set. When back up through the system GUI, SQL Server 2005 or 2008 will automatically set the specified backup set and store, the aim is to simplify management. Using T-SQL Shows specified by the following syntax:
SQL Commands
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS = 7,
NAME = ‘FULL’,
MEDIANAME = ‘ALLBackups’
NAMEs is the backup set name, MEDIANAME is storage set name
4, full backup
No matter which recovery mode used, all the backup must have a full backup, especially in the log and differential backups, if not have all backed up, will not be able to recover.
Simple full backup script as following, you can also specify the full backup through maintenance plan.
SQL Commands
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’
But the need to note that the order is attached to the current database backup file exists, if does not exist then create it, and will not overwrite the original file. The same name to overwrite the backup files, need to specify the INIT parameter.
SQL Commands
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’ WITH INIT
5, Log Backup
Log backup script is as follows:
SQL Commands
BACKUP LOG mydb_log TO DISK = ‘D: \ backup \ mydb.trn’
Need to develop the habit to use. Trn as the extension of log backup.
6, differential backup
When using log backups to restore, the process is undoubtedly very slow, use differential backup can shorten the recovery time. In fact, the differential backup is an option of BACKUP DATABASE, as follows:
SQL Commands
BACKUP DATABASE mydb TO DISK = ‘D: \ backup \ mydb.dif’ WITH DIFFERENTIAL, INIT
Restore the database, first restore the whole database backup, differential backup and then restore the database, and finally to restore the log backup.
7, error detection
The backup process will also verify the data, or verify incomplete pages (torn page), or verify the checksum (checksum) in the process of backup. To use this feature, need to activate the option.
SQL Commands
BACKUP DATABASE mydb TO DISK=’D:\data\mydb.bak’ WITH CHECKSUM
If discovered the error in the process of backup, SQL Server 2005 or 2008 will write an error message on the SUSPECT_PAGE MSDB table inside. By default, at the same time the backup behavior will stop (STOP_ON_ERROR), so that the administrator could troubleshoot the error.
8, security backup
Full backup and log backup statement also supports the use of password attributes, such as:
SQL Commands
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH PASSWORD=’mydb’
9, the band backup
In some cases, a single hard disk can store a full database backup, database backup can be divided into multiple parts are stored in different disks, this backup is called the band backup.
SQL Commands
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’,
DISK=’E:\mydb.bak’ WITH INIT,CHECKSUM,
CONTINUE_ON_ERROR
The D and E disk drive backup is indivisible.
10, mirror backup
Different with band backup, mirror backup keep multiple copies on different disk. The code is as follows:
SQL Commands
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’
MIRROR TO DISK=’E:\mydb.bak’
WITH INIT,CHECKSUM,CONTINUE_ON_ERROR
In practice, it’s more appropriate to use mirror backup to back up the log.
11, COPY-ONLY backup
After the SQL SERVER 2005, SQL SERVER provides an option to copy-only. To use copy-only full backup option would not like to back up the original plan, the code is as follows:
SQL Commands
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’
WITH INIT,CHECKSUM,COPY_ONLY
Third, documents and file group backup
After SQL SERVER 7.0, SQL Server provides a filegroup concept.
Database has three data files, in general, the database requires at least two (. Mdf and. Ldf) or more documents. SQL Server not only allows more than one file exists (. Ldf), also allows multiple file groups exist.
A file group can have multiple files, each file requires careful planning initial size and the increment.
1, the default file group
Created object, if not explicitly specify a file group, then the object will be stored in the default file group. By default, the default file group is primary, but as primary file group can not only contains the user data, and also store a database structure and other technical information, it is generally suggest to add an additional file group, designated it as the default file group. SQL Server 2005 or 2008 only have one default file group.
The code of modify the default file group is as follows:
SQL Commands
ALTER DATABASE mydb MODIFY FILEGROUP mydb DEFAULT；
2, specify the file group for the target
When you create the table or index, whether the user or the system needs to place the table or index on a file group. If you specify file group when creating the table or index, then the table or index file will be stored in the specified file group instead of the default file group.
The specified code is as follows:
SQL Commands
CREATE TABLE test ([id] int, [notes] text) on mydbdata
3, move the object to the specified file group
If you need to change the storage location of the object, the easiest way is through the GUI property pages to modify it, you can visually see the migration process of the object.
Also by T-SQL to modify, for example:
SQL Commands
ALTER TABLE test drop constraint PK_test WITH (MOVE TO DATA)
4, backup data file
Backup data file can also be achieved through the BACKUP DATABASE code. As follows:
SQL Commands
BACKUP DATABASE mydb FILE = ‘D: \ Data \ mydb.ndf’ TO DISK = ‘E: \ Backup \ mydbdata.bak’
The code is equivalent to the entire backup of data file-level, and it’s similar to database-level backup,file-level backup also have differential backup, of course, you need have full file backup.
Differential backup code as follows:
SQL Commands
BACKUP DATABASE mydb FILE = ‘D: \ Data \ mydb.ndf’
WITH DIFFERENTIAL
TO DISK = ‘E: \ Backup \ mydbdata_dif.bak’
5, backup file group
Similar with separate backup file, you can take similar operations to the file backup, there are two ways to backup file groups, one is designated by GUI interface, one is through T-SQL.
T-SQL Commands is as follows:
SQL CommandsBACKUP DATABASE mydb FILEGROUP = ‘PRIMARY’ TO DISK = ‘E: \ Backup \ mydbpri.bak’
6, incomplete backups (partial backup)
In the file group backup, incomplete backup is equivalent with full backup, it can be achieved by specifying the keyword READ_WRITE_FILEGROUPS to finish.
Command as follows:
SQL CommandsBACKUP DATABASE mydb READ_WRITE_FILEGROUPS TO DISK = ‘D: \ mydb.bak’
The above is the entire methods of Microsoft SQL Server 2005 and 2008 backup with sql code, the next article is about Microsoft SQL Server 2005 and 2008 data recovery with sql commands.
.