SQL Server database backup and recovery measures

The most common operations are available for beginners ......

I. Back up 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 --> 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 new database name (such as the Forum database Forum) --> then 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, if it was originally bbs_data.mdf, the current database is forum, change it to forum_data.mdf). The log and data files must be modified in this way (the log file name is * _ log. LDF). You can set the recovery directory as needed, provided that the directory must exist (for example, you can specify D: \ sqldata \ bbs_data.mdf or D: \ sqldata \ bbs_log.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.

Iii. Database shrinking

In general, the contraction of the SQL database does not greatly reduce the size of the database. Its main function is to shrink the log size. This operation should be performed regularly to avoid excessive database logs.
1. Set database mode to simple mode: Open the SQL Enterprise Manager, in the root directory of the console, open Microsoft SQL Server --> SQL Server group --> double-click your server --> double-click to open the database directory --> select your database name (such as the Forum database) forum) --> then right-click and select Properties --> Select Options --> select "simple" in the fault recovery mode, and click OK to save
2. Right-click the current database to view the shrinking database in all tasks. Generally, the default settings in the database do not need to be adjusted. Click OK directly.
3. After shrinking the database, we recommend that you set your database attributes to the standard mode. The operation method is the same as the first one, because logs are often an important basis for restoring the database in case of exceptions.

4. Set daily automatic Database Backup

We strongly recommend that users with conditions perform this operation!
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, specify the backup disk directory and select the specified directory. For example, you can create a directory on disk D, such as D: \ databak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, the backup file extension is generally Bak and the default is used.
6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report, it is best to use the default option --> next step to complete
7. After completion, the system will probably prompt that the SQL Server Agent service is not started. First, click confirm to complete the plan settings, find the SQL green icon in the rightmost status bar of the desktop, and double-click it, select SQL Server Agent from the service list, and click the run arrow to automatically start the service when the OS is started.
8. At this time, the database plan has been successfully run and will be automatically backed up according to the above settings.

Modify plan:
1. Open the Enterprise Manager, in the root directory of the console, choose Microsoft SQL Server> SQL Server group> double-click your server> Manage> database maintenance plan. plan, can be modified or deleted

5. Data Transfer (new database or transfer server)

In general, it is best to use backup and restoration operations to transfer data. In special cases, you can use the Import and Export Method for transfer. Here we will introduce the Import and Export method, the data import/export method can be used to reduce or contract the database size when the database contraction is invalid. By default, this operation provides you with a certain understanding of SQL operations, if you do not understand some of these operations, you can consult the relevant staff of the mobile network or query online materials.
1. Export all tables and stored procedures of the original database into an SQL file, when exporting data, make sure to select the options to compile the index script and write the primary key, foreign key, default value, and check the constraints script.
2. Create a database and execute the SQL File Created in step 1 for the new database.
3. Use SQL to import and export all the table content from the original database to the new database.

6. Restore data to the time point using database logs
Because of the abnormal data loss, and do not want to use backup data restoration, as long as there is a backup and the current log is well preserved, you can try this method, maybe the loss can be recovered ......

1. If a full-Database Backup (or multiple differential or incremental backups exist) exists before the misoperation, the first thing to do is
Perform a log backup (if you set trunc. Log On chkpt to 1 to prevent the log file from becoming larger)
Backup log dbname to disk = 'filename'
2. Recover a full-database backup. Use with norecovery. If there are other differences or incremental backups, recover them one by one.
Replay
Restore database dbname from disk = 'filename' with norecovery
3. Restore the last log backup, that is, the log backup you just created. Specify the recovery time point to the time before the misoperation.
Restore log dbname from disk = 'filename'
With stopat = 'date _ time'

These operations can be completed in the SQL Server Enterprise Manager, which is not difficult...

7. Two Methods for restoring the database without Database Log Files
Accidental deletion of database log files or other causes of Database Log damage

Method 1

1. Create a database with the same name

2. Stop SQL server again (do not detach the database)

3. overwrite the new database with the data file of the original database.

4. Restart SQL Server.

5. When you open the Enterprise Manager, there will be a doubt. Ignore it and execute the following statement (note that you must modify the database name)

6. After completion, You can generally access the data in the database. At this time, the database itself usually needs problems. The solution is to use
The database script creates a new database and imports the data.

Use master
Go

Sp_configure 'Allow updates', 1 reconfigure with override
Go

Update sysdatabases set status = 32768 where name = 'suspicious database name'
Go

Sp_dboption 'questionable database name', 'single user', 'true'
Go

DBCC checkdb ('questionable database name ')
Go

Update sysdatabases set status = 28 where name = 'questionable database name'
Go

Sp_configure 'Allow updates', 0 reconfigure with override
Go

Sp_dboption 'questionable database name', 'single user', 'false'
Go

Method 2

Cause
Yesterday, the system administrator told me that the disk space of an internal application database is insufficient. I noticed that the database Event Log File xxx_data.ldf has grown to 3 GB, so I decided to narrow down the log file. After failing to contract the database, I made the biggest and most stupid mistake since I entered the industry: I accidentally deleted this log file! Later, I saw all the information about database restoration.ArticleThey all said: "In any case, it is critical to ensure the existence of Database Log Files." Microsoft even has a KB article about how to restore the database only by using log files. I really don't know what I thought at that time ?!

This is broken! The database cannot be connected, and the enterprise manager says "(doubt)" next to it )". And the most terrible thing is that this database has never been backed up. The only thing I can find is another database server that was migrated six months ago. The application is usable, but many records, tables, and stored procedures are missing. I really hope this is just a nightmare!

Ineffective recovery steps
Additional database
_ Rambo: when there is no activity log in the deleted log file, you can restore it as follows:

1. to detach a suspicious database, use sp_detach_db.
2. Add a database. You can use sp_attach_single_file_db.

However, it is a pity that after execution, SQL Server will question the inconsistency between the data file and the log file, so it is impossible to attach the database data file.

DTS data export
No. The xxx database cannot be read. DTS wizard reports "An error occurred in the initialization context ".

Emergency Mode
When Yi Hongyi says there is no log for restoration, you can do this:

1. Set the database to emergency mode.

2. Create a New log file.

3. Restart SQL Server.

4. Set the application database to single-user mode.

5. Perform DBCC checkdb

6. If there is no major problem, you can change the database status back. Remember to disable the system table modification option.

 

I tried to remove the data file of the application database, re-create a database xxx with the same name, stop the SQL service, and overwrite the original data file. Then, follow the steps in yihongyi.

However, it is also a pity that, apart from step 1, other steps are very successful. Unfortunately, after restarting SQL server, this application database is still in doubt!

However, I am glad that, after doing so, I can select the data and let me breathe a sigh of relief. However, when the component uses the database, the report says, "an error occurs:-2147467259, failed to run begin transaction in database 'xxx' because the database is in avoidance recovery mode ."

 

All steps for successful recovery
Set the database to emergency mode
Stop the SQL Server service;

Remove the data file xxx_data.mdf of the application database;

Re-create a database xxx with the same name;

Stop the SQL service;

Overwrite the original data file;

Run the following statement to set the database to emergency mode;

Run "use master"

Go

Sp_configure 'Allow updates', 1

Reconfigure with override

Go"

Execution result:

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

The configuration option 'allowupdates' has been changed from 0 to 1. Run the reconfigure statement for installation.

 

Then run "Update sysdatabases set status = 32768 where name = 'xxx '"

Execution result:

(The number of affected rows is 1)

 

Restart the SQL Server service;

Run the following statement to set the application database to single user mode;

Run "sp_dboption 'xxx', 'single user', 'true '"

Execution result:

The command is successfully completed.

 

Ü perform DBCC checkdb;

Run "DBCC checkdb ('xxx ')"

Execution result:

The DBCC result of 'xxx.

The DBCC result of 'sysobjects.

The object 'sysobjects' has 273 rows on 5 pages.

The DBCC result of 'sysindexes.

The object 'sysindexes' has 202 rows, which are on 7 pages.

The DBCC result of 'syscolumns.

.........

 

Ü run the following statement to disable the system table modification option;

Run "sp_resetstatus" XXX"

Go

Sp_configure 'Allow updates', 0

Reconfigure with override

Go"

Execution result:

Before updating the database 'xxx' entries in sysdatabases, the mode is 0, the status is 28 (the status is suspect_bit = 0 ),

No row in sysdatabases is updated because the mode and status are correctly reset. No error. No changes were made. DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. The configuration option 'allowupdates' has been changed from 1 to 0. Run the reconfigure statement for installation.

Re-establish another database XXX. lost;

DTS export wizard
Run the DTs export wizard;

Copy the source to select the emergencymode database XXX and import it to XXX. lost;

Select "copy objects and data between SQL Server databases" and try it multiple times. It seems that it cannot be done. It just copies all the table structures, but there is no data or views and stored procedures, in addition, the DTS wizard finally reports a copy failure;

Therefore, we finally chose "Copy tables and views from the source database", but later we found that we can only copy some table records;

Therefore, select "Use a query to specify the data to be transmitted" and export the missing table records;

Views and stored procedures are added by executing SQL statements.

Of course, if the misoperation involves operations that do not remember logs, such as truncate table and select into, then there is no farli.
Use the above method to restore data...