Sqlserver database backup several ways
In the actual database Sqlserver operation and maintenance process, many times we need to do the data backup operation, you can do a regular backup, you can also do manual database backup. The following is a detailed description of these backup methods.
(1) database backup
This method of backup database is the most complete, you can backup all the objects in the database to generate a . The operation of backup in this method is directly in the Sqlserver database management software, select the database, right-click and select Tasks, and then select Backup, as shown below
Then enter the backup interface, select the path where the backup will be stored and set the backup file name, and then click OK to perform the backup, as shown below
(2) Generate scripts for single-table backup
Sometimes our business system can be backed up by a single table, and if there is a problem, we can restore the database directly through the .bak file.
Sometimes the update of our business system may involve only one table or several tables, at this time you can operate through a single table backup for each data table, only the backup of these data tables involved in the change operation. The method of operation is to select the database in the Sqlserver database management software, right-click and select Tasks, and then select Generate Script, refer to the first figure in Method 1. Then enter the Generate Script interface, select Next to enter the Settings interface:
After entering the Settings interface, select a specific database object, and then select the data table you need to back up from it, as follows:
And then click Next to enter the Storage Path and Export Settings interface, Save to File can be selected to save as a single file or a file for each object. The key point here is to remember to select the Advanced button, set the data type of the script to be written in it, otherwise the default rules exported to the file contains only the table architecture statements (i.e., the SQL statement to create the table), does not contain any table data. As shown below
Select the Advanced button and go to Advanced Settings, scroll down to the end of the drop-down menu to see a setting called Type of data to script. Set this to either Architecture and Data or Data Only. Only the files exported in these two ways contain data from the table, and the architecture-only export is just the statement that creates the table.
After setting the above information and clicking Finish, the backup file is automatically created to the specified storage path, and the generated file is a database SQL file with a .sql extension.
(3) Quickly back up a table to a temporary new table with the SelectInto statement.
This backup method is to quickly write the found data into the specified new table of backup and create the table automatically.
For example, if you quickly back up all the data of the Proct table in the A database to the Proct_0228 table in the ABackUp database, you can use the following statement to operate, which can be executed directly in the New Query window.
Executing this statement requires that the table behind the into does not exist prior to the execution of the existence of the above statement can not be executed. The Proct_0228 table does not need to be processed manually, the above statement automatically creates a structure consistent with the Proct table when it is executed.
Sqlserver database backup in several ways
Tags: a little bit of new image to save the O&M appears to be technically handled right click
About SQL database automatic backup (sql how to automatic backup)
A simple and practical backup method:
Tools in Enterprise Manager, DatabaseMaintenancePlanner, you can set up a regular automatic backup plan for the database. And by starting SqlserverAgent to automatically run the backup plan. The specific steps are as follows:1. Open Enterprise Manager, and in the console root directory, click MicrosoftSQLServer in turn –
Modify the backup plan: Open Enterprise Manager, and in the console root directory, click MicrosoftSQLServer in turn –
How to Backup and Restore MSSQL Database
See some time ago, some users reflect the error when restoring MSSQL database, don’t know how to restore. In fact, recovering MSSQL database is still relatively simple. The following is a brief introduction to the backup method and the corresponding recovery method, I hope to help some webmasters. First, backup database open MicrosoftSQLServer Enterprise Manager (English version is EnterpriseMa
See some time ago, some users reflect the recovery of MSSQL database error, do not know how to recover. In fact, recovering MSSQL database is still relatively simple.
Below is a brief introduction to the backup method and the corresponding recovery method, I hope to help some webmasters.
One, backup database
Open MicrosoftSQLServer Enterprise Manager (English version is EnterpriseManager), expand the server group, expand the database, as follows:
Right-click on the database to be backed up, All Tasks = “Backup Database”. BackupDatabase), the following chart:
Click Backup Database, you can see the following chart:
Database: select the name of the database to be backed up;
Name: fill in the name of the backup file to be backed up;
Backup: here you have to choose Database-complete” (Database-complete);
Destination: you can save the backup file in the current default path; if you want to change the path, please delete the current path, click “Add”, select the backup file. If you want to change the path, please delete the current path first, and then click “Add” to select the path for the backup file.
Overwrite: Select “Overwriteexistingmedia” if you want to save the backup file to the current default path, or select “Appendtomedia” if you want to change the path. Appendtomedia” or “Overwriteexistingmedia” if you want to change the path.
Then click “Option”, as shown:
After setting, click “OK” to start backup:
After completing the backup, as shown:
Click “OK” to complete the backup. “OK” to complete the database backup.
Second, restore the database
The database backed up in the above way is without any suffix, as shown in the figure:
Open MicrosoftSQLServer’s Enterprise Manager (English version is EnterpriseManager), expand the server group, and create a new database:
Fill in the new database name:
Click “Datafile” to view the directory where the datafile is located and record it:
Click “Transactionlog” to view the directory where the log file is located and record it:
Click “Transactionlog” to view the directory where the log file is located. Click “Transactionlog” to see the directory where the log file is located and record it:
Click “OK” to successfully create a new database.
Right click on the new database, select All Tasks = “Restored Database”:
The general settings for database recovery are as follows:
Click on “Options”:
Set up the settings, and click on “OK” to start the database recovery. “OK” to start restoring the database:
Recover the database, open the database table, you can see:
To this point, the backup and recovery of MSSQL databases is briefly introduced.
MSSQL database backup and restore several methods of graphic tutorials
I, through the Enterprise Manager for a single database backup. Open SQLSERVER Enterprise Manager, expand the SQLSERVER group LOCAL under the database, right-click on the database you want to back up, in the pop-up menu, select Backup Database under All Tasks, the Backup Database dialog box pops up:
Click the Add button, fill in the path and file name of the backup file, click OK to add the backup file, click the Backup on the dialog box to start the backup.
Second, through the Enterprise Manager multi-data timed library backup. Open SQLSERVER Enterprise Manager, expand the Administration node under the SQLSERVER group, right-click Database Maintenance Plan, and select New Maintenance Plan. Select Next in the Welcome dialog box that pops up, and then the following dialog box is displayed:
Select the databases you want to back up, Next. In the next step, we default all options, Next. In the current dialog box, you can choose whether to check the integrity of the database, here we can default to the next step on the line. The dialog box is shown in the figure below:
Check Back up the database as part of the maintenance plan, click the Change button to modify the time to backup the database, click Next, into the figure below:
Here, we can choose to “Use this directory” to specify the path for the backup, check the “Delete the database earlier than this time” option. “Delete files earlier than this time” to specify the retention time of the backup file.
Three, backup database files. Open SQLSERVER Enterprise Manager, expand the SQLSERVER group LOCAL under the database, right-click on the database you want to backup, select Properties. Select Data Files in the pop-up dialog box, where the path to the data files is displayed, as follows:
Open SQLSERVER Service Manager and stop SQLSERVER. Then open My Computer, find the path to the data file and copy it out for backup (note that the .LDF file is backed up at the same time).
Finally, turn on the SQLSERVER service
Four, reply to the database backup file (applicable to the database backed up with Enterprise Manager). Open SQLSERVER Enterprise Manager, expand the SQLSERVER group LOCAL under the database, right-click on the database you want to restore, select Restore Database under All Tasks, the following dialog box pops up:
Select the slave device, click to select the device, add the path to the backup file for the restoration, and then make sure to restore.
V. Restore database backup file. Right-click on the Database node in Enterprise Manager and select Attach Database under All Tasks to bring up the Attach Database dialog box. The following figure:
Select a data file that you have previously copied, and then click OK to attach successfully (Note: If the database already exists, please delete the database after attaching it).
You may be interested in the article:sqlserver backup restore database function encapsulation share SQLSERVER database backup can not be restored after the solution to simplify the SQLServer backup and restore to the cloud principle of work and the operation method of the MySQL database backup and restore of the commonly used commands summary solution SQL2005 backup database .dat or bak restore the structure of the wrong solution sqlserver2005 database backup restore graphic tutorial
How to fully backup database SQL
1, click on the start menu and open sql enterprise manager.
2. Expand the sqlserver root directory in turn and open the database.
3, select the name of the database to be backed up, right mouse button all tasks → backup database.
4, here you need to pay attention to, if there is a previous backup, be sure to delete the previous backup records, do not worry about just delete records rather than delete the backup file.
5, delete the record and then click Add.
6, select the backup purpose, is to let you choose the backup location and backup file naming. In front of the path behind the file name, this file name is customized, you can name it according to the time or other, if you want to save as you can also click Save As, and then OK.
7, add the save path continue to determine, and then there will be a backup progress prompts until the completion of ok.
8, according to the path to check whether the backup file.
sql2008r2 how to backup database
Backup database can maximize the reduction of losses caused by unforeseen circumstances, to be able to restore in a timely manner in the event of a problem, as long as the database function can go through the database files to backup to any location, the details of the steps are shown below.
sql2008r2 backup database tutorial
1, first of all, go to open the “SqlServermanagermentstudio”,
2, enter the account password and click on the “Connect! “
3, right-click on the database to be backed up, select “Tasks”, click “Backup”,
4, click on the location of the figure “… ” icon,
5, and then go to click “Add”,
6, select the location of the backup, and go to change the name of the backup file, click “OK”,
7, wait for the database backup is complete.