First, backup database
1. Open the SQL Enterprise Manager, and click Microsoft SQL Server in the Contents Root Catalog.
2, SQL Server Group -> Double click to open your server -> Double click to open the database directory
3, choose your database name (such as the forum database forum) -> then click Tools in the Menu -> Select Backup Database
4, the backup options Select full backup, backup in the destination to select the name point delete if the original path and name, then add it, if there is no path and name, then select Add, then specify the path and file name, specified after the specified point Determine return backup window, then click OK to make a backup
Second, restore the database
1. Open the SQL Enterprise Manager, and click Microsoft SQL Server in the Contents Root Catalog.
2, SQL Server Group -> Double click to open your server -> Click the new database icon for the point icon, the name of the new database
3. Click the newly entered database name (such as the forum database forum) -> then click Tools in the Menu -> Select Recovery Database
4. Select from the device -> point selection device -> and select your backup file name -> Add the backup file name -> Add the backup point OK, then the equipment bar should There is a database backup file name you just selected. The backup number is 1 (if you have multiple backups to the same file, you can click on the view next to the backup number, select the latest backup in the check box. ) -> Then click on the option button next to the normal side.
5. Select to force restore on the existing database in the window, and select Options that make the database can continue to run but cannot restore other transaction logs in the recovery completion state. Restoring the database file for the intermediate part of the window To set up the installation of your SQL installation (you can also specify your own directory), the logical file name does not need to be changed, move to the physical file name to do according to your recovery machine situation Change, such as your SQL database in D: / Program Files / Microsoft SQL Server / MSSQL / DATA, then follow the directory of your recovery machine to change, and the last file name is best to change your current database name (If it is bbs_data.mdf, now the database is forum, change to forum_data.mdf), log and data files must be related to the related changes in this way (the file name of the log is * _log.ldf), Here you can freely, provided that this directory must exist (if you can specify D: /SQLDATA/BBS_DATA.MDF or D: /SQLDATA/bbs_log.ldf), otherwise restore will report an error
6. After the modification is complete, click on the determination to recover, then a progress bar, prompt recovery, the system will automatically prompt after the recovery is completed, such as intermediate prompt error, please record the relevant error content and ask questions SQL operations are more familiar, general errors are nothing more than directory error or file name duplicate or file name error or space is not enough or the database is in use, you can try to close all SQL windows and then reopen Recovery operation, if you also prompt the error that is being used, you can restart it and then restart. As for other errors, it can be restored after the error content is changed accordingly.
Third, shrink database
In general, the shrinkage of the SQL database does not greatly reduce the size of the database. Its main role is to shrink the log size, and this will be performed regularly to prevent the database log.
1. Setting the database mode as simple mode: Open SQL Enterprise Manager, click on the Microsoft SQL Server -> SQL Server Group -> Double click to open your server -> Double click to open your database -> Double click to open the Database directory - > Choose your database name (such as forum database forum) -> then right click to select Properties -> Select Options -> Select "Simple" in the fault restore mode, then press OK Save 2, point on the current database Right-click, see the contraction database in all tasks, generally the default settings inside are not adjusted, direct point determination
3,
After the shrink database is complete, it is recommended to reset your database properties to standard mode, and the operation method is the first point, because the log is often an important basis for restoring the database in some abnormalities.
Fourth, set the daily automatic backup database
It is highly recommended that there is a conditional user!
1. Open Enterprise Manager, click on the Microsoft SQL Server in the Control Bengen -> SQL Server Group -> Double click to open your server
2, then point the tools in the menu above -> Select the database maintenance planner
3. Next Select data to be automatically backed up -> Next Update Data Optimization Information, here usually don't do the choice -> Next check data integrity, it is generally not selected
4, the next step specifies the database maintenance plan, the default is 1 week backup, click Change to select the point after backup every day.
5. Next Specify the backup disk directory, select the specified directory, such as you can create a directory in the D: / DataBak, then choose to use this directory, if your database is more best to choose to choose for each The database establishes a sub-directory, then selects to delete how many days ago, generally set 4-7 days, see your specific backup requirements, the backup file extension is generally Bak uses the default
6, Next Specify the transaction log backup plan, see your needs to do the selection -> The next step to generate the report, generally do not choose -> Next Maintenance plan history, it is best to use the default option -> Step completion
7. After the completion, the system is likely to prompt the SQL Server Agent service that is not started, first determine the completion plan setting, then find the SQL green icon in the rightmost status bar in the desktop, double-click Open, select SQL Server Agent in the service, then Click to run the arrow, select the automatic start service when you start the OS
8. This time the database plan has been successfully run, he will automatically back up the above settings.
Modify plan:
1. Open Enterprise Manager, click on Microsoft SQL Server in the Contents of the Console -> SQL Server Group -> Double click to open your server -> Management -> Database Maintenance Plan -> After opening To your set plan, you can modify or delete actions
V. Data transfer (new database or transfer server)
In general, it is preferable to perform transfer data using backup and restore operations. In special cases, it can be transferred in a special way. This is an imported export method, and an imported export mode transfer data is that one can be used in a shrink database. Under the case where the size of the database is used to reduce the size of the database, this operation is default that you have a certain understanding of the operation of SQL. If you don't understand some of them, you can consult the relevant personnel or inquiry online information.
1. Export all tables, stored procedures, stored procedures into a SQL file, pay attention to the option to write index scripts and write primary keys, foreign keys, default and check constraints options in the options.
2. New database, perform the SQL file established in the first step in the new database
3. Import the new database into all table contents in the original database with SQL imported by SQL (Source: Mobile Network)
