1 / 21

5. SQL Restore tasks

5. SQL Restore tasks. Objectives Learn howto recover databases from disaster Contents View database and transaction log backup files Perform a complete data restoration of a user database Recover a database to a specified point in time Restore a database to standby mode using Practicals

duane
Télécharger la présentation

5. SQL Restore tasks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 5. SQL Restore tasks • Objectives • Learn howto recover databases from disaster • Contents • View database and transaction log backup files • Perform a complete data restoration of a user database • Recover a database to a specified point in time • Restore a database to standby mode using • Practicals • Recover your databases • Summary

  2. Determining the Data Restoration Sequence • msdb database, SQL Server 2000 records the details of the backup and the restore history in the msdb database • which devices or files the backup is stored on, • Who performed the backup, • and at what time. • If the msdb database is damaged, you should restore the msdb database from backup before you restore any user databases. • Check what backups you have in the Management container, right click and select properties or double click and view contents • Use the transact SQL • If you do not have a recent backup of the msdb database or are restoring to another SQL Server 2000 instance. • read each backup file and add the information to the msdb database • Restore msdb history RESTORE LABELONLY FROM gurka RESTORE HEADERONLY FROM gurka RESTORE FILELISTONLY FROM gurka

  3. Retrieving Backup Media Information To retrieve backup media information 1. Ensure that you are logged on to the server as Administrator. 2. In the SQL Server Enterprise Manager console tree, expand the default instance, expand the Management container, and then click Backup. The backup devices for this SQL Server 2000 instance are displayed in the details pane. 3. In the details pane, right-click SSEMDBTLogBackup and then click Properties. The Backup Device Properties – SSEMTLogBackup dialog box appears displaying the filename associated with this backup device. 4. Click the View Contents button. The View Backup Media Contents dialog box appears, displaying the contents of the SSEMTLogBackup device. Details regarding three transaction log backup sets appear. 5. Switch to SQL Query Analyzer. 6. On the toolbar, click the Load SQL Script button. The Open Query File dialog box appears. 7. RESTORE LABELONLY FROM gurka RESTORE HEADERONLY FROM gurka RESTORE FILELISTONLY FROM gurka

  4. Back up the transaction log without truncation • Before you attempt to restore a backup to a database that is still functioning, be sure to restrict user access to it. • Disk is intact, software error/smartboy caused damage • disk containing a data file failed, • SQL Server 2000 installation failed • recover data to an earlier point in time. • If a disk containing a data file fails, SQL Server Enterprise Manager will display the database containing the damaged data file as suspect. • If you discover you have a suspect database, you should immediately back up the transaction log without truncation before you attempt any restoration of your dat

  5. Back up the transaction log without truncation, cont. • If you discover you have a suspect database • Backing up the current transaction log (using the Without Truncation option) allows you to recover up to the point of failure. • Goto the Databases container, • right-click the suspect database in the details pane, • point to All Tasks, • and then click Restore Database. • Next, you should replace the failed disk. If your data is using RAID for fault tolerance, all that you need to do is to simply add the new drive to the RAID system.

  6. Begin restoration suspect Database • Goto the Databases container, • right-click the suspect database in the details pane, • point to All Tasks, • and then click Restore Database. • the First Backup To Restore drop-down list allows you to select which backup to try first • You can make a combination of backup sets that you can use to complete the restoration.

  7. Howto restore suspect Database • Goto the Databases container, • right-click the suspect database in the details pane, • point to All Tasks, • and then click Restore Database. • Select options tab • You can choose to leave the database operational, not possible to restore additional transaction logs. • You can choose to leave the database read-only and able to restore additional transaction log files. • You can leave database read only adn able to restore more transaction logs

  8. Restoring a Data File or Filegroup • This you use to restore from a selectable file to retireve certain data from that backup. • Restoring from a subset of files

  9. Restoring to a Different SQL Server 2000 • When restoring a database to a second SQL Server you must first: • create the database in SQL Server into which you will restore the database • Generally, you should use the same database name and database file paths as the one you are restoring • If you choose a different name, you will have to select the Force Restore Over Existing Database • if you choose to restore to a different physical path, you will need to adjust the restoration path for the database files • After you have created the database in the SQL Server: • begin by right-clicking the database in the console tree for the SQL Server 2000 instance that you want to restore • pointing to All Tasks, and then clicking Restore Database • the General tab of the Restore Database dialog box, • click the From Device option button • Next, you need to click the Select Devices button to select a backup device from which to restore data

  10. Restoring to a Different SQL Server 2000 6. Choose Restore Devices dialog box, 7. click the Add and select a file or tape drive from which to restore data

  11. Restoring to a Different SQL Server 2000 • After you select a file or tape drive from which to restore data, you can choose among several options. • you can restore a specified backup set • recover or leave the database able to restore additional transaction logs • you can choose to read the backup set information from a specified device • add it to the backup history in the msdb database

  12. Recovery of a Database to a Point in Time • This is very useful if you need to rollback to a certain point in time. • Standby mode allows you to view the condition of the database at the end of each restoration before you apply additional transaction log files. • With Transact SQL you can restore to a named mark. • Do not start use the database before carefully verify and checking time restored data

  13. Performing Restorations Using Transact-SQL • You can restrict access to the newly restored database to database administrators as part of the restore process. • You can move a database to a new location using the MOVE option. • You can supply a password. • You can restart an interrupted restore. • You can recover to a named mark. • Cons, they need experience and practise.

  14. Restoring a Complete Database • The RESTORE DATABASE and RESTORE LOG commands can be very simple or very complex. • The preceding example restores a full database backup of the Northwind database from the C:\SQLBackups\Temp.bak file on disk. RESTORE DATABASE Northwind FROM DISK = 'C:\SQLBackups\Temp.bak RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY RESTORE DATABASE Northwind FROM NorthwindDiffBackup WITH FILE = 2, NORECOVERY RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 4, NORECOVERY RESTORE LOG Northwind FROM NorthwindTLogBackup2 WITH FILE = 5

  15. Restoring a Data File or Filegroup • Database Northwind is restored from file backup • Backup set 2 is used • A notification is made after each 25% • Only Admin has access to database after restore • Restore of transaction log is also performed with backup set 2 RESTORE DATABASE Northwind FILE = 'Second_Data_File' FROM File_Backup WITH RESTRICTED_USER, NORECOVERY, STATS = 25 RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 2

  16. Restoration Using the Move Option • This restores the full database backup • The database files is moved to a new disk location • A media password is added • Restore of transaction log is also performed RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY , MEDIANAME = 'NorthwindBackups' , MEDIAPASSWORD = 'my_password' , MOVE = 'Northwind' TO 'D:\SQLDATA\NwindNew.mdf' , MOVE = 'NorthwindLog' TO 'E:\SQLLogs\NwindNewLog.ldf' , RESTORE LOG Northwind FROM NorthwindTLogBackup

  17. Restoring to Standby Mode • This restores the full database backup • Two transaction logs are restored • After restore the database is set in standby mode RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 4 , NORECOVERY RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 5 , STANDBY = TO 'D:\SQL\UNDO.tmp'

  18. Restoring to a Specified Point in Time • This restores the full database backup • Two transaction logs are restored • The restore is stopped when a stopdate is reached. RESTORE DATABASE Northwind FROM NorthwindFullBackup WITH NORECOVERY RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 4 , NORECOVERY RESTORE LOG Northwind FROM NorthwindTLogBackup WITH FILE = 5 , RECOVERY, STOPAT = 'Oct 2, 2000 5:08:32 PM'

  19. Restoring the Master Database • We will take a closer look on • Restore the master database from backup • Rebuild the system databases • If master database is corrupted or destroyed, you will lose you entire SQL Server installation because it uses the databases for configurations • you can restore the master database using the most recent full database backup of the master database • You should also mirror the system databases using RAID 1 where possible.

  20. Restoring the Master Database • To restore the master database, start SQL Server 2000 in single-user mode with the –m option in the Command Prompt window or from the Run dialog box. You must first stop the database. • Next, start SQL Query Analyzer and restore your most recent backup of the master database using the same commands you use to restore any user database. • After the restoration of the master database is complete, the SQL Server running in single-user mode will stop. You can then restart SQL Server 2000. C:\> sqlservr –m RESTORE DATABASE master FROM MasterFullBackup

  21. Rebuilding the System Databases • To rebuild the system databases, use the Rebuildm.exe utility C:\Program Files\Microsoft SQL Server\80\Tools\Binn C:\Program Files\Microsoft SQL Server\mssql\Binn • Remove the Read-only attribute from the original installation files, or the Rebuildm utility will fail. • Click the Rebuild button to begin the process. You receive a warning in a Rebuild Master dialog box that you are about to rebuild and overwrite all of your system databases

More Related