This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If I leave a backup on a network share, surely nobody can read it?
Unless you encrypt the backup file directly, then yes, that is a readable file. If someone got access to the network share, he or she could read the backup file directly using a text editor, or simply copy the file and run a restore on another instance of SQL Server.
Does a backup contain more than the data?
A backup does contain more than just the data. It contains the entire structure of the database. This includes all the data structures and data, of course, but it also includes all the procedures, views, functions and any other code. It also contains the settings and definitions of the database.
When is a Page Restore Userful?
A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
T-SQL syntax to restore corrupted pages.
|
1 2 3 |
RESTORE DATABASE database_name PAGE='1:57, 1:202, 1:916, 1:1016' FROM 'file_path' WITH NORECOVERY; |
How to find corrupted data pages?
Use suspect_pages table of MSDB database to find information of corrupted data pages.
Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_type column.
Can I perform Online page restore on all the edition of SQL Server?
SQL Server Enterprise edition supports online page restores, though they use offline restore if the database is currently offline. In most cases, a damaged page can be restored while the database remains online.
Meaning of different event type of suspect_pages table.
- 1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).
- 2 = Bad checksum.
- 3 = Torn page.
- 4 = Restored (page was restored after it was marked bad).
- 5 = Repaired (DBCC repaired the page).
- 7 = Deallocated by DBCC.
Why ApexSQL Recovery tool is better point-in-time recovery?
The advantages of ApexSQL tools over recovery to a point in time are that ApexSQL will recover just the tables you specify, while a point-in-time recovery will roll back all the transactions that happened in the meantime.
What is a Snapshot Backups?
A snapshot backup is a specialized backup that is created almost instantaneously by using a split-mirror solution obtained from an independent hardware and software vendor. Snapshot backups minimize or eliminate the use of SQL Server resources to accomplish the backup.
This is especially useful for moderate to very large databases in which availability is very important.
What are the benefits of Snapshot Backups?
- A backup can be created quickly, typically measured in seconds, with little or no effect on the server.
- A restore operation can be accomplished from a disk backup just as quickly.
- Backup to tape can be accomplished by another host without an effect on the production system.
- A copy of a production database can be created instantly for reporting or testing.
How to reduce the size of Log files?
- Perform a full backup of your database.
- Change the backup method of your database to “Simple”.
- Open a query window and enter “checkpoint” and execute.
- Perform another backup of the database.
- Change the backup method of your database back to “Full”.
- Perform a final full backup of the database.
Will differential backups always be smaller than full backups?
For the most part, if you refresh your base backup on a regular schedule, you will find that a differential backup should be smaller in size than a full database backup. However, there are situations where a differential backup could become even larger than its corresponding base backup, for example if the base backup is not refreshed for a long period and during that time a large amount of data has been changed or added.
Have you ever failed to get exclusive access for MSDB?
Yes, If SQL Server Agent service is running, exclusive access cannot be achieved. To get an exclusive access, you should stop the SQL Agent service.
Which are the popular SQL Server Backup Encryption tools?
Can you access the database during a restore operation?
Most SQL Server restores are offline operations; users can’t access the database while it’s being restored.
With the full recovery model, partial restores and restores of nonprimary file groups are online operations by default.
How can we rebuild the system databases?
Can I backup MS SQL Server ‘tempdb’ database?
No. You cannot backup your MS SQL Server ‘tempdb’ database.
In which scenarios Transaction Log is truncated?
When checkpoint runs in SIMPLE recovery model and Transaction Log Backup was generated in Full recovery model. It truncates all committed transactions from the log file.
What are the different states, in which we cannot take backup of database?
- Suspect (only tail log backup is allowed)
- Restoring
- Standby
- Offline
- Transaction Log file was full(Only Log backups are allowed)
