Chapter One, backup and restore of SQL Server databases (sp_addumpdevice, backup, restore)

In a SQL Server database, backup and restore are only performed on the server, the backed up data files are on the server, and the restored data files are only available on the server, when the SQL Server client is started on a non-server machine. The client can also be used to back up and restore the database, but this operation is essentially on the server, the backup data file on the server, the restored data file can only be on the server, this principle will not change, just use a client's tool to manipulate the process.

1.1. Backing Up the database

There are two ways to back up a database:

The first is to use the tool to back up the database in Enterprise Manager, which will only have one file that ends in . bak . In this way, when a database is backed up, you can name the backup files and suffixes arbitrarily, and the backup files that have been backed up can be modified by name and suffix without affecting the file recovery, but the original database's . mdf and is already recorded inside the backup file . The name of the LDF file and the path to the store. When backing up, if a file with the same name as the backup file name already exists in the destination folder, there are two options, append and overwrite, and general selection overrides . Recommendation: The original database name is the file name of the backup file, and with . bak as the suffix name .

The second way is to directly find the Data folder of the database and save the. mdf and . ldf files directly.

1.2. Restore the database

There are two ways of restoring a database:

.bak file, once you have identified the backed up database, you can use Enterprise Manager to restore it. The restored new database name can be arbitrary, in option option can be installed according to the current machine sql Server 2005 Path to modify the path, you can even modify .mdf and .ldf file, the name is just an identifier, any modification can be, the compiler will .mdf and .ldf file is associated with the new database name. When this is restored, the .mdf and .ldf files are generated, The original backup data file can be deleted. When restoring, if a file with the same name as the restored file name already exists in the destination folder, you can choose to overwrite the original data file. recommended: Restore or restore the database name with the original database name, option option do not change the name, only when necessary to modify the path can be .

The second is to restore the. mdf and . ldf files, and you can use attach to attach the. mdf and. ldf files to the restore, and then name the new database name, but the two files cannot be deleted or the data source is lost. Recommendation: place the. mdf and . ldf files in the Data folder . The restored database name is the same as the original database name .

1.3. Two important folders related to database backup and restore:

... \microsoft SQL Server\mssql.1\mssql\backup

------- Storing the backed up data file

... \microsoft SQL Server\mssql.1\mssql\data

                                            -------- storing . mdf and . ldf files

1.4. Back up and restore the database by using SQL Server commands

When we usually back up the database, we need to log in to the database server through the SQL Server client to back up and restore, which is inconvenient, in fact, SQL Server comes with commands to back up and restore the database. These commands can be executed directly in the SQL Server client's SQL Script window, or through ADO. NET calls these commands for remote backup and recovery of the database.

(1), back up the command to restore the database

Backup:

Backup database ' back to DISK = ' backup file path ';

-- Note: The database name being backed up must be present or it will go wrong

Restores:

--Put the database on the offline state

ALTER database ' restored databases name 'SET OFFLINE with ROlLBACK IMMEDIATE;

-- Recover database

Restore database ' recovered name ' from DISK = ' restoring file path (source file)';

--Put the database on the online state

ALTER database ' restored databases name 'SET ONLINE with ROlLBACK IMMEDIATE;

- Note: The restored database name must be present, and the database will be overwritten, the restore file path (source file) must be present, and in addition, before and after the execution of the two ALTER statements, the selected database must be the master ,

Sometimes anALTER statement may not be necessary for the sake of insurance.

(2), command application

A, you can execute commands directly in the SQL Server client's SQL form.

B, you can write the stored procedure on the database, and then configure the JOB to call this stored procedure periodically.

C, through the application of ADO. NET to execute these commands or commands consisting of stored procedures that implement the application control backup to restore the database, which can be written as a service and called periodically.

(3),C # case

backing up the database

String sql = "BACKUP database qis2006 to disk = '" + Server.MapPath (""). ToString () +"\ \"

+ bakname+ System.DateTime.Now.DayOfYear.ToString () + System.DateTime.Now.Millisecond.ToString () +". Bak"; backup file name

Access ACC = new Access ();

Acc. Executenonequery (SQL);

Restoring a database

String sql = "Alter Database qis2006 Set Offline with Rollback immediate;";

SQL + = "RESTORE Database qis2006 from disk = '";

SQL + = Server.MapPath (""). ToString () +"\ \";

SQL + = Bakname + "'"; //bakname is the backup file name

SQL + = "Alter Database qis2006 Set OnLine with rollback Immediate;";

Try

{

Connect to the master database;

Execute SQL statement;

Response.Write ("<script language=javascript>alert (' data recovery succeeded! ');</script> ");

}

catch (Exception ex)

{

Response.Write ("<script language=javascript>alert (' data recovery failed! ');</script> ");

This. Label2.Text = ex. ToString ();

}

(4), stored procedure case

backing up the database

Create proc Backup_database

(  

@device_name varchar,-- backup device name

@file_name varchar, -- backup file name

@devicelog_name varchar,-- log backup device name

@filelog_name varchar -- log backup file name

)  

As

Begin

-- Create a backup logical device for the database

exec sp_addumpdevice ' disk ', @device_name , @file_name

-- Create the device logical name

exec sp_addumpdevice ' disk ', @devicelog_name, @filelog_name

Backup Database TestDB to @device_name -- backing up databases

BACKUP log testdb to @devicelog_name -- back up logs

End

Restoring a database

Create proc Restore_database

(  

@device_name varchar,

@devicelog_name varchar(%)

)  

As

Begin

Restore database TestDB from @device_name

Restore log testdb @devicelog_name

End

sp_addumpdevice a backup device is a device that is specially prepared to replace a faulty device in order to prevent the entire system from being paralysed by a critical or vulnerable device during the operation of the device system. Backup devices are sometimes referred to as "standby". backup devices can be divided into "hot spare" and "cold standby". Hot spare means working together with the target device, and when the target device fails or goes down, the heat-standby equipment immediately undertakes the task of the faulty equipment; cold standby means that when the target device fails or stops, the cold standby equipment starts to enter the start-up state by the stop waiting state, and undertakes the work task of the fault equipment. Sp_addumpdevice adds a backup device to the Sys.backup_devices catalog view. The device can then be logically referenced in the BACKUP and RESTORE statements. Sp_addumpdevice does not perform any access to the physical device. The specified device is only accessed after the BACKUP or RESTORE statement is executed. Creating a logical backup device simplifies the backup and RESTORE statements, in which case specifying the device name will instead use the "TAPE =" or "DISK =" clause to specify the device path.

Sp_addumpdevice Syntax:

sp_addumpdevice [@devtype =] ' Device_type '  , [@logicalname =] ' logical_name '  , [@physicalname           =] ' Physical_name ' [, {[@cntrltype =] controller_type | [@devstatus =] ' Device_status '}]

eg.

A backup device named disk is added with a mydiskdump physical name of c:\dump\dump1.bak .

Use master; GO EXEC sp_addumpdevice ' disk ', ' mydiskdump ', ' C:\dump\dump1.bak ';

Chapter One, backup and restore of SQL Server databases (sp_addumpdevice, backup, restore)