9.8.6 Recovery System Database

System databases are as prone to failures as user databases, so ensure that they are sufficiently protected to be heavy
To. In general, there are two options for recovering a system database. You can restore from a backup, or rebuild them from scratch. I
The backup restore method is strongly recommended because rebuilding from scratch means that someone has to work on the volume.
Because system databases are usually small, backing them up does not take much time, and they do not consume after the backup
Too much space. The frequency of changes in the system database structure determines how often these databases need to be backed up to minimize or
After the original work.
1. Recovering the master database
There are two cases of recovering the master database. In the first case, the server can be accessed. and the second case
, SQL Server is inaccessible.
If you can connect to SQL Server, the server instance must be started in single-user mode for restore and recovery
master database.
(1) to start an instance of SQL Server in single-user mode, you need to enter the following command in the command Prompt window:

Sqlservr.exe-m
(2) If the server supports multiple instances of SQL Server, make sure that the correct instance is started. The default CCIC instance of SQL Server is located in the \program Filesxmicrosoft SQL Server\ mssql.l \Mssql\Binn folder. Every
An additional instance has its own MSSQL.x folder, but the sort is based on the order in which they are installed, so these folders may not be in numerical order.
(3)-Once you start the server in single-user mode, you can restore the master database. To do this, you need to start another Command prompt window and use sqlcmd to log on to the instance of SQL Server. The following example shows the
A command that uses Windows Security (-e) to log on to the Aughteight (-s) instance of SQL Server:
C:\>sqlcmd-s AUGHTEIGHT-E
Tips:
For a complete description of the SQLCMD syntax, refer to "Using sqlcmd in SQL Server 2008 Books Online"
Utilities topic.
(4) After successfully logging in to the server, you can use the normal restore syntax to complete the restore of the master database:
1>restore DATABASE MASTER from DISK = ' E:\SQLBackups\MasterFull.BAK * 2>g0
Processed pages for database ' Master1, file ' master1 on file 1.
Processed 2 pages for database ' master*, file *mastlog* on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating the this process.
As shown in the preceding example, when you restore the master database, SQL Server automatically shuts down the instance, so that you can restart the instance with the newly restored master database. A database that can be restored in single-user mode has only a master database. After you restore the master database, you can restart SQL Server and continue to restore other systems or user databases. If the instance of SQL Server is inaccessible due to a failure of the master database or the overall server, then the master database needs to be rebuilt. In previous versions of SQL Server, you could complete this task with a command prompt utility. But Microsoft no longer supports this utility. Therefore, to rebuild the master database, you must reinstall SQL Server. Once the reinstallation is complete, you can use the most recent backup of the master database to restore the server, as you said earlier.
When the master database is successfully restored and the SQL Server instance is restarted, the remaining system databases and user databases are automatically reloaded. If the backup of the master database is not current or does not exist at all, the remaining system and user databases are not automatically reloaded and must be restored or appended. Assuming that the file system is left
Database files are still unchanged, attaching them is faster and easier. Attaching an existing database is the most
The easy way is to use the graphical tools provided in SQL Server Management Studio.
(1) To attach a database, right-click the Databases folder and select the "Attach" command, at which time the additional number
The library window will be displayed.
(2) Click the Add button to browse to the location of the MDF file for the database, and then select the file. Each data
The library's MDF file contains metadata that identifies the location of all the constituent files of the database. As long as no files are "not

Find the message token, attaching the database without any difficulty.
(3) If a data file is missing, the database cannot be attached. But if you just lost the transaction day
Log file, the database can still be attached successfully, just select the missing journal file and click the Delete button
Can Once the log files are removed from the list, you can click the OK button to attach the database. SQL Server will recreate a log file with the original metadata.
2. Orphaned users
After restoring or attaching the master database and all other databases, it is necessary to check whether there are orphaned users in the user database. Orphaned users occur when a SQL Server login is added to the master database and is granted access to the database, but the backup of the master database is executed before the login is created. When you attach or restore a user database, the user database contains the database user, but the login name in the master database does not exist.
To find and fix orphaned users, there are two ways available. The first is to use the ALTER USER command. Assume
User Fredf is orphaned, and the following code can associate a database user with a server login:
Use SMALLW Orks GO
ALTER USER FREDF with LOGIN =FREDF
The second method is to use the deprecated SP change users login stored procedure. Input parameter @ man ratio 011 definition
3 modes of the SP change users login stored procedure. Table 9-3 lists the 3 operations supported by the stored procedure.

9.8.7 Database Restore Summary
As with the backup strategy, it is important to have an original plan. A good plan should cover any possible loss of
and lists the steps to restore the database with minimal data loss in the shortest time possible. I can't be here in detail.
Describe each possible combination, so you need to analyze your own infrastructure and choose the one that best suits your needs.
Backup restore plan for the environment.
9.9 db Snapshot
If the database is completely corrupted, then the database snapshot is not actually used for disaster recovery. But
It can be used to reverse the impact of database modifications, or you can redirect queries from busy transactional databases to other
Place.
What is a database snapshot? A snapshot is a static, read-only view of a database at a point in time. Snapshots can be created instantaneously.
, because this process does not actually replicate the database as the snapshot source. Instead, the data files that are created only package
Contains data pages in the source database that have changed since the snapshot was created. This feature is called Copy on write (Replication on write). When a database snapshot is initially created, almost the same data file is created to hold the snapshot content. Of these data files
The difference is that they have a physical location independent of the source database and consume little disk space at the outset.
The simplest way to understand a database snapshot is to create and use a snapshot. The following script creates a
Small-A snapshot of the Works database:
CREATE DATABASE smallworkssnapshot on (NAME = * smallworksprimary ', FILENAME = ' D:\SQLSnapShotData\SmallWorksPrimary.mdf *), (name = * smallworksdatal*, FILENAME = 1d:\sqlsnapshotdata\smallworksdatal.ndf *), (name = ' SmallWorksdata2 *, filen AME = ' d:\sqlsnapshotdata\smallworksdata2.ndf*) as SNAPSHOT of Smallworks
Observing the file system, you can see that the size of the Smallworks snapshot data file is 10MB, as it was created (your file size may vary due to changes made to the Smallworks database), but in fact it occupies only 256KB of space, where the primary file occupies 128KB , each secondary data file occupies 64KB. SQL Server retains the same amount of disk space that the database is now using, but allocates only enough space to store the metadata for the database structure.
Now take a look at the data in the Smallworks and Smallworkssnapshot databases to understand when the source database
What happens to the snapshot database and data when the changes are made.
(1) First write a poll from the DBO in the Smallworks and Smallworkssnapshot database. Person
The first three rows of the table return some data, as shown in the following example:
Use Smallworks GO
SELECT FirstName, LastName, emailaddress from dbo. Person WHERE PERSONLD < 4

FirstName LastName EmailAddress
Ken Sanchez [email protected] Terri Duffy [email protected] Roberto Tamburello [email protected]
(3 row (s) affected) Use Smallworkssnapshot
GO
SELECT FirstName, LastName, emailaddress from dbo. Person WHERE PERSONLD < 4
FirstName LastName EmailAddress
Ken Scinchez [email protected] Terri Duffy [email protected]reworks.com Roberto Tamburello [email protected]
(3 row<s) affected)
It is noted that two databases return the same results. Actual T, the query to the snapshot database is redirected to the source database because the data page that contains the contact information has not changed since the snapshot was created.
(2) Now, update the data in the source database by changing the last name of everyone in the database. To update them all,
This makes it easier to check for changes to the physical data files that hold the snapshot database.
Use Smallworks GO
UPDATE dbo. Person SET LastName = * Flintstone * (in the row (s) affected)
The Smallworkssnapshot file now occupies 1.2MB of space (your results may differ from this). Updating the 5000 rows in the Smallworks database causes the data pages that contain the rows to be copied to the snapshot, making the snapshot larger in size.
(3) Now check these two databases again to see what the results are. As expected, the source database has changed, reflecting
The update for the LastName column.
Use Smallworks GO
SELECT FirstName, LastName, emailaddress from dbo. Person WHERE PERSONLD < 4
FirstName LastName EmailAddress

Ken Flintstone Terri Flintstone Roberto Flintstone
Ken.s^[email protected] terri.duffyQadventureworks.com [email protected]
(3 row (s) affected)
However, the snapshot database still reflects the state of the data at the time the snapshot was created. This is the database's "static only
Read the copy "means.
Use Smallworkssnapshot
GO
SELECT FirstName, LastName, emailaddress from dbo. Person WHERE PERSONLD < 4
FirstName LastName EmailAddress
Ken S, Anchez [email protected] Terri Duffy [email protected] Roberto Tamburello [email protected]
(3 row (s) affected)
You can create multiple snapshots of a database as needed, but keep in mind that each additional snapshot increases the source database
The overhead. Because each command that updates or deletes data or objects is written to the snapshot database to record the database's previous
A version.
9.9.1 Database Snapshot limits
There are some limitations to database snapshots, and the source database for snapshots has some limitations.
? Database snapshots cannot be backed up. Because the snapshot is the data retrieved from the source database and the data stored internally
Combined, so it is not possible to actually back up snapshots.
? Database snapshots cannot be modified.
? The source database cannot be deleted when a snapshot exists.
? When a snapshot exists, the source database cannot be restored to a point in time before the snapshot was created.
9.9.2 disaster recovery and database snapshots
How do you make a database snapshot work well in disaster recovery? This is a very good question. Snapshots can be used to
Undo updates to the source database because they have the original copy of the data before the modification.
1. Undoing updates
In the previous example, we updated the 5000 rows in the person table with the last name Flintstone. To undo the impact of this update, you can use the following script:

Use Smallworks GO
UPDATE dbo. Person
SET LastName =s.lastname
FROM dbo. Person P joins SmallWorksSnapShot.dbo.Person S on s.personld =p.personld
The source database is now polled to show that all last names are restored to their original values.
2. Undo Delete
Consider the following command, which deletes the dbo. 50 rows in the person table:
DELETE dbo. Person WHERE PERSONLD < 51
If this is a malicious or accidental update, the general pattern of restoring data is to restore the database to a test suit
And then copy the data from the test server to the production database. With a database snapshot, you don't need to use the data
The library has been backed up.
To restore the data, you only need to select the data from the snapshot and insert it back into the source database table.
U$e Smallworks GO
INSERT dbo. Person (personld, FirstName, LastName, EmailAddress) SELECT personld, FirstName, LastName, EmailAddress from SMALLWORKSSN ApShot.dbo.Person WHERE Personld < 51
3. Undo Delete
If you delete a database object from the source database, you can script it and from the snapshot database
Re-create it. If the object is a table, you can repopulate the table with the method previously used to undo the deletion.
--inadvertant deletion of the person table use Smallworks GO
DROP TABLE dbo. Person
--Recreate the person Table
Use Smallworks GO
CREATE TABLE dbo. The person (personld int is not NULL, FirstName varchar () is not NULL, MiddleName varchar () NULL, LastName varchar () is not NULL, EmailAddress nvarchar (+) NULL) on Swuserdatal

--repopulate the table INSERT dbo. Person (personld, FirstName, LastName, EmailAddress) SELECT personld, FirstName, LastName, EmailAddress from SMALLWORKSSN ApShot.dbo.Person
4. Restoring from a Snapshot
If the source database has some unwanted changes, you can designate the snapshot as the source of the restore operation,
The state that the vault was restored to when the snapshot was created. Remember, if multiple snapshots are present, the database cannot be restored to the snapshot
The point in time before the creation. You must first delete those snapshots. The following command shows how to get from a database snapshot or
Original Smallworks Database:
Use MASTER
GO
RESTORE DATABASE smallworks from database_snapshot = 1SmallWorksSnapShot '
9.1 0 Summary
This chapter describes the different ways to back up and restore a database, and also describes the different aspects of disaster recovery, which
Minimizing data loss and protecting your work is important. I hope you understand the importance of making an effective plan. Data
The Library Administrator is responsible for maintaining the integrity and security of the data. To accomplish this important task, we must respond to the disaster.
Plan, and more importantly, plan how to recover from the disaster with minimal data loss and minimal downtime.

9.8.6 Recovery System Database