Backup of the SQL Server database and also

Ext.: http://blog.csdn.net/zwj7612356/article/details/8188025

in a SQL Server database, backup and restore can only be performed on the server, the backed up data files are on the server, the restored data files are only available on the server, and when the SQL Server client is started on a non-server machine, the database can also be backed up and restored , but this operation is essentially done 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. However, the names and stored paths of the. mdf and. ldf files for the original database have been recorded inside the backup file. 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:

The first is to restore the . bak file, and when you have identified the backed up database, you can use Enterprise Manager to restore it. The restored new database name can be arbitrary, and the option option allows you to modify the path based on the path of SQL Server 2005 installed in the current machine. You can even modify the name of the. mdf and. ldf files, the names are just identifiers, and any modifications are possible, and the compiler will associate the. mdf and. ldf files with the new database name. When this is restored, the. mdf and. ldf files are generated, and the original backup data files 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. Recommendation: After the restore or the original database name to restore the database name, option options do not change the name, only when necessary to modify the path.

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 they can be used to remotely back up and restore the database by calling these commands from ADO.

(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, in addition, before and after the execution of the two alter statements, the selected database must be master,

Sometimes an alter 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 to execute these commands or commands composed of stored procedures, implementation of application control backup restore database, these applications can be written as a service, 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 (125),--Backup file name

@devicelog_name varchar,--log backup device name

@filelog_name varchar (130)--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 (80),

@devicelog_name varchar (85)

)

As

Begin

Restore database TestDB from @device_name

Restore Log TestDB @devicelog_name

End

Backup of the SQL Server database and also