Auto-configuration database_console version Restore database, Database Console

Source: Internet
Author: User
Tags microsoft sql server management studio

Auto-configuration database_console version Restore database, Database Console

Error message:


Cause: the database name "5" is not the actual logical name of the database backup file named "Sdzn"

View the actual logical name of the database backup file named "Sdzn:

From the above we can see that "5" should be changed to "SdznSmartCloudEdu_Empty"


There are three names in SQL Server: Database Name, logical file name, and physical file name. SdznSmartCloudEdu_Empty and SdznSmartCloudEdu_Empty_log are logical file names. SdznSmartCloudEdu_Empty_mdf and SdznSmartCloudEdu_Empty.ldf are physical file names. Generally, logical file names are commonly used in backup, restore, and other statements.


How can I automatically back up and restore SQL databases?

, Backup database
1. Open SQL Enterprise Manager and click Microsoft SQL Server in the root directory of the console.
2. SQL Server group --> double-click to open your Server --> double-click to open the database directory
3. Select Your Database Name (for example, Forum database Forum) --> click the tool in the menu above --> select backup database
4. Select full backup as the backup option. If there is a path or name for the backup in the target project, click Delete and then add. If there is no path or name, select Add directly, specify the path and file name, click OK to return to the backup window, and click OK to back up

2. Restore the database
1. Open SQL Enterprise Manager and click Microsoft SQL Server in the root directory of the console.
2. SQL Server group --> double-click to open your Server --> click the new database icon in the icon bar to retrieve the name of the new database.
3. Click the name of the newly created database --> click the tool in the menu above --> select recover Database
4. In the displayed window, select "restore from device"> "select device"> "add"> "select your backup file name"> "add" and click "OK" to return, at this time, the device column should display the database backup file name you just selected. The default backup number is 1. (If you have backed up the same file multiple times, click View next to the backup number, select the latest backup in the check box and click OK) --> then click the option button next to the general button
5. In the displayed window, select force restore on the existing database, and select the option to enable the database to continue running but not to restore other transaction logs in the recovery completion status. To restore the database file in the middle of the window, you need to set it according to your SQL installation (you can also specify your own directory). The logical file name does not need to be changed, the file name to be moved to the physical Server must be changed based on the recovered machine. For example, if your SQL database is installed in D: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data, then modify the directory of the recovered machine according to the change, and the final file name should be changed to your current database name (for example, zw0001.mdf, and zw0002, change it to zw0002.mdf). The log and data files must be modified in this way (the log file name is. ldf). You can set the recovery directory as needed, provided that the directory must exist (for example, you can specify d: \ sqldata \ zw0002.mdf or d: \ sqldata \ zw0002.ldf ), otherwise, an error is returned.
6. After the modification is complete, click "OK" below to restore. a progress bar is displayed, prompting the recovery progress. After the restoration is complete, the system will automatically prompt "success". If an error is reported in the middle, please record the relevant error content and ask people familiar with SQL operations. The common error is nothing more than a directory error, repeated file names, incorrect file names, insufficient space, or database in use errors, you can close all SQL windows and re-open them to restore the database. If an error is prompted, stop the SQL Service and restart it, as for the above other errors, you can change the content of the errors to restore them.

3. Set daily automatic Database Backup
1. Open the Enterprise Manager and click Microsoft SQL Server> SQL Server group in the root directory of the console. Double-click to open your Server.
2. Click Tools in the menu above --> select Database Maintenance Scheduler
3. Next, select the data to be automatically backed up --> next, update the data optimization information. You do not need to select here --> next, check data integrity.
4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "backup every day". Click "OK ".
5. Next ...... the remaining full text>

How to restore a database

All databases must be restored to the same tag to ensure that the transaction statuses of each database are consistent. For more information, see marked transactions, full backups, and log backups. If the target system has only one server, make sure that all log backup sets are restored (except the latest log backup sets ). For more information, see view history of restored backups. If you have not restored all log backup sets and have not run the restoration job, run the restoration job (if necessary, run it manually ). If there are uncompleted backup sets that can be restored, the job will process them until all the backup sets are restored. If the target system has multiple servers, all servers must be restored to the same backup set. You must view the restoration history of each server to ensure that the latest log backup sets restored on all servers are the same. If they are different, you must manually run the restore job on each server on which you want to restore the latest log backup set. After all servers are restored to the same backup set, the final backup set can be restored manually. The adm_BackupHistory table is the core of recording the log Transfer Process history of the source system. All the performed backup work is recorded in this table. All servers in the target system read information from this table to obtain the information required to perform the restoration. Note: If you restore the BAM-dominated database from the backup, you should also use the backup before the BAM-dominated database backup to restore the BAM archive database, the BAM star schema database, and the BAM analysis database. For more information, see back up and restore BAM.
Note: If you have removed the full backup or log backup of the source database from the backup BizTalk Server job where they are stored, you should update the relevant rows of the database in the bts_LogShippingDatabases table on the target system, set LogFileLocation or DBFileLocation to a new location from which the target system should read the complete/log backup file. This table is filled in when you run the bts_ConfigureBtsLogShipping stored procedure. By default, these columns are set to null, indicating that the target system should read these backup files from the locations stored in the adm_BackupHistory table.
Important keep a copy of the backup file at a safe location. Even if you have performed log backup, the database cannot be restored without backup files.
To perform this operation, you must log on with the account of the SQL Server sysadmin fixed Server role member. Restore a database (SQL Server 2008 R2/SP1) on a computer identified as the target system, click Start, all programs, Microsoft SQL Server 2008 R2, and SQL Server Management Studio ". In the "connect to Server" dialog box, specify the name of the SQL Server on the target system, and click "Connect" to connect to the corresponding SQL Server. In Microsoft SQL Server Management Studio, double-click the corresponding Server, SQL Server proxy, and job ". In the details pane, right-click "BTS log shipper-obtain backup history" and click "Disable ". In the "disable job" dialog box, the status changes to "successful ". In the details pane, right-click "BTS log shipper-Restore database" and click "Disable ". In the "disable job" dialog box, the status changes to "successful ". In the details pane, right-click "BTS log shipper-restore to tag", and then click "job start step ...". When the start <servername> job dialog box appears, click step ID 1 (selected by default) and then click Start ". The START <servername> job dialog box is closed, and the start job-<servername & g ...... remaining full text>

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.