server|sqlserver| Backup | recovery | data | Database One, BACKUP database
1, open SQL Enterprise Manager, and then click 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 menu above--> Select Backup Database
4, backup option Select Full Backup, the purpose of the backup to if there is a path and name then select the name Point Delete, then click Add, if there is no path and name then directly select Add, then specify the path and file name, specify the backend to return to the Backup window, then click to confirm the backup
second, restore the database
1, open SQL Enterprise Manager, and then click 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 its own
3, click on the new database name (such as Forum Database Forum)--> then click the tools in the menu above--> Select Restore Database
4, in the pop-up window of the Restore option to select from the device--> point Select Device--> Point add--> and then select your backup file name--> add point to determine 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 made multiple backups of the same file, you can click on the view next to the backup number, select the latest backup point in the checkbox)--> then click the option button next to the general section 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 the recovery will be an error
6, after the completion of the modification, click on the determination of the following to restore, at this time will appear a progress bar, prompted the recovery of the progress of the system will automatically prompt success, 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, the other errors mentioned above can be restored
after the error content is changed accordingly.
third, shrink 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, this should be done periodically to avoid the database log too large
1, set the database mode to Simple mode: Open SQL Enterprise Manager, click to open Microsoft SQL Server-->sql Server group in the console Root--> double hit Open your server--> double-click to open the database directory- > select your database name (such as Forum Database Forum)--> 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, directly point to determine
3, when 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 exceptional cases
Four, set up daily automatic backup database
strongly recommends that a conditional user do this!
1, open Enterprise Manager, click to open Microsoft SQL Server-->sql Server group in the console Root--> double hit Open your server
2, then click Tools in the above menu--> Select Database Maintenance Scheduler
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--> the next report to be generated, generally do not choose--> next to maintain the plan history, preferably with the default option--> next step is completed
7, the system is likely to prompt the SQL Server Agent service does not start, first determine the completion plan, and then locate the SQL Green icon in the rightmost status bar of the desktop, double-click to open, select SQL Server Agent in the service, and then click the run arrow, Select the below to start the service automatically when starting the OS
8, this time the database program has been successfully run, he will follow the settings above you to automatically backup
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 the 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 the SQL, if the part of the operation does not understand, you can consult the dynamic network related personnel or inquiries online data
1, export all tables and stored procedures of the original database into a SQL file, and when exporting, note that you choose to write index scripts and write primary keys, foreign keys, default values, and CHECK constraints script options in the options
2, create a new database, perform the SQL file created in the first step of the new database
3, with SQL Import Export method, import the new database all table contents in the original database
