MS SQL Server database backup, compression and SQL database data processing methods _ database Other

One, BACKUP database

1. Open SQL Enterprise Manager, click to open Microsoft SQL Server in the console root directory
2, SQL Server group--> double hit Open your server--> double-click to open the database directory
3, select your database name (such as Forum Database Forum)--> then click the Tools in the above menu--> Select Backup Database
4, Backup options Select Full Backup, the purpose of the backup to the original if there is a path and name then select the name Point Delete, then click Add, if there is no path and name is directly selected to add, and then specify the path and filename, specify the back-point of the Backup window, and then click OK

Second, restore the database

1. Open SQL Enterprise Manager, click to open Microsoft SQL Server in the console root directory
2, SQL Server group--> double hit Open your server--> point icon Bar of the new database icon, the name of the new database to take
3, click on the new database name (such as Forum Database Forum)--> then click the Tools in the above menu--> select Restore Database
4, in the pop-up window of the Restore option select from the device--> point Select Device--> Point add--> and then select your backup file name--> Add after the point of return, this time the device bar should appear you just select the database backup file name, The backup number defaults to 1 (if you have multiple backups of the same file, you can click on the view next to the backup number, select the latest backup point in the check box)--> then click the option button next to the general above
5. Select the Force restore on the existing database in the window that appears, and select the option to keep the database running but not to restore other transaction logs in the recovery completion state. In the middle of the window to restore the database file to here to follow the installation of your SQL settings (you can also specify your own directory), the logical file name does not need to change, to move to the physical file name to change according to the machine condition you restored, such as your SQL database installed in D:\Program files\ Microsoft SQL Server\MSSQL\Data, then follow the changes that you have made to the directory where you restored the machine, and the last file name is best changed to your current database name (such as the original Bbs_ Data.mdf, now the database is forum, changed to Forum_data.mdf), log and data files are to do in this way related changes (log file name is *_ Log.ldf end of), here's the recovery directory you can set freely, provided that the directory must exist (such as you can specify D:\sqldata\bbs_data.mdf or d:\sqldata\bbs_log.ldf), otherwise recovery will be an error
6, after the completion of the modification, click on the determination of the following recovery, there will be a progress bar, prompt recovery progress, the system will automatically prompt success after recovery, such as the middle of the prompt error, please record the relevant errors and ask about the SQL operation more familiar with the personnel, The general error is simply a directory error or file name duplication or file name error or lack of space or database is in use error, database is using the error you can try to close all about the SQL window and then reopen for recovery operations, If you also hint that the error you are using can stop the SQL service and then restart it, as the above other errors can generally be changed according to the wrong content to restore

Third, shrinking the database

In general, the contraction of the SQL database does not greatly reduce the size of the database, its primary role is to shrink the log size, you should do this periodically to avoid the database log too large
1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as Forum Database Forum)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save
2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine
3. After the database is finished, it is recommended that you reset your database properties to standard mode, with the 1th, because the log is often an important basis for restoring the database in some unusual cases.

Iv. setting up a daily automatic backup database strongly recommends that the user who has the conditions to do this!
1, open Enterprise Manager, in the console root directory in turn open Microsoft SQL Server-->sql Server group--> double hit Open your server
2, then click the Tools in the above menu--> Select Database Maintenance Planner
3, next select the data to be automatically backed up--> next update data optimization information, here generally do not have to choose--> Next Check data integrity, also generally do not choose
4, the next step to specify the database maintenance plan, the default is 1 weeks backup, click on the change to select a daily backup point to determine
5, next specify the backup disk directory, select the specified directory, such as you can create a new directory in D disk such as: D:\databak, and then choose to use this directory here, if your database is more than best choose to create subdirectories for each database, and then choose to delete the backup earlier than how many days ago, Generally set 4-7 days, this depends on your specific backup requirements, backup file extensions are generally bak with the default
6, next specify the transaction log backup plan, see your needs to make a choice--> next to generate reports, generally do not choose--> Next maintenance Plan history, preferably with the default option--> next step to complete
7, after the completion of the system is likely to prompt the SQL Server Agent service is not started, the first point to determine the completion plan, and then find the desktop rightmost status bar in the SQL Green icon, double-click Point Open, select SQL Server Agent in the service, and then click the run arrow, Start the service automatically when you start the OS
8, this time the database plan has been successfully run, he will follow the settings above you to automatically back up

Modification Plan:
1, open Enterprise Manager, in the console root directory in order to open Microsoft SQL Server-->sql Server group--> double hit Open your server--> admin--> Database Maintenance Plan--> open to see the plan you set , you can modify or delete an operation

V. Transfer of data (new database or transfer server)

Under normal circumstances, it is best to use Backup and restore operations to transfer data, in special cases, you can use the Import export method to transfer, here is the Import Export mode, import Export mode transfer data one function is to reduce (shrink) the size of the database in the case of shrinking the database. This operation by default for you have a certain understanding of the operation of SQL, if some of these operations do not understand, you can consult the relevant personnel or network information
1, the original database of all the tables, stored procedures into a SQL file, export the time note in the option to write index script and write primary key, foreign key, default value and check Constraint script options
2. Create a new database, execute the SQL file created in the first step of the new database
3, with SQL import and export way, to the new database to import all the table contents in the original database

Add a way to restore SQL database backup:

1, the original backup of the C:\Program Files\Microsoft SQL Server\MSSQL\Data directory under the two files *_sql_data.mdf and *_sql_log.ldf copy to your SQL Server 2000 of the installation directory, such as: C:\Program Files\Microsoft SQL Server\MSSQL\Data

2, open "Enterprise Manager"-> "machine name"-> "Database", right click "All Tasks"-> "additional Database",
Then choose just copy of the file *_sql_data.mdf, click Attach, Success!

3, do other settings, such as login identity, password and so on.