Everything You Were Afraid To Ask About Microsoft SQL Server Backups

During presentations about backups and restoring SQL Server databases, are usually set two types of questions.

• The first set directly from the courtroom during the presentation.

• The second round after, in private conversation.

These broad questions, which are often more interesting and I will try to give answers to the most complex and interesting of them. Instead of writing another article about how you should do backups, or why you should make backups, or even why you should validate your backups (but you really must verify their backups).

Can I deploy backup on the version of SQL Server other than the one on which the backup made? What kind of problems might occur?

You can restore the backup to another version of SQL Server, but only if the version of SQL Server on which you are deploying backup, newer than the one you did. In other words, you can implement backup made SQL Server 2000 to SQL Server 2005, SQL Server 2005 to SQL Server 2008 R2 or SQL Server 2008 to SQL Server by 2012, but will never be able to do this in the opposite direction.

Each version of SQL Server makes changes to the database and the files that store it. Microsoft will not “return to the past” and overwrite previous versions of SQL Server to support these changes. If you need to go to an earlier version of SQL Server, you will need to encrypt the schema and the data itself (for example, here is an article about a similar transition)

To determine which version of SQL Server backup created, you should see the backup file header:

RESTORE HEADERONLY FROM DISK = ‘ d:bumm.bak ‘;

As a result, you will see the Major, Minor and Build version of the instance of SQL Server on which the backup was made (as shown in the screenshot below). These will allow you to determine the appropriate version of SQL Server to restore the backup.

• When you restore a database on a newer version of SQL Server, you may find that it has something incompatible with this release of SQL Server. The safest approach to transition to the new version, SQL Server will run the Microsoft Upgrade Advisor (a free utility available for each version of SQL Server) that you want to transfer. Make sure that she is ready, and then make a backup and restore it on a new instance (but only in that order, and not first try to migrate the backup, and then run the Assistant).

• After the restoration, the DB will be in compatibility mode with the SQL version Server’a, with which the transition. It means that it will be available only the functionality that supported by the version of SQL Server on which the backup created. To get all the advantages of the new version of SQL Server, you must change the compatibility level of the database. It can be done using the GUI, but you can script:

ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110;

Different numbers represent different versions of SQL Server: SQL Server 2005 for 90, 100 for SQL Server 2008 and 2008 R2 and SQL Server 110 2012 (for more details about the versions of SQL Server, you can read here translator’s).

It is worth mentioning that not all transitions are possible. SQL Server will “jump forward” only two versions. For example, you cannot deploy a backup made SQL Server 2000 to SQL Server 2012. First, you will need to implement it to the SQL Server 2008, install the appropriate compatibility level, create a new backup, and then you use SQL Server on 2012.

Can I use the restore operation to create a copy of the database? What could go wrong?

• Yes, you can do it. If you are deploying backup to another server, you need to verify that the new server, you present the same logical drives as on the “old” server, or manually specify the correct path to the database file by using the with move option restore database commands:

RESTORE DATABASE NewDBName
FROM DISK = ‘ c:bumm.bak ‘
With MOVE ‘ OldDB ‘ TO ‘ c:datanew_mm.mdf ‘,
Move ‘ OldDB_Log ‘ TO ‘ c:datanew_mm_log.ldf ‘;

Database files have both logical names and real names of the files. You only need to specify all the logical file names and definitions for each of them a new physical location.

• The biggest problems that you may encounter are errors relating to lack of free space on the disk to which you restore the database, or you can forget to specify a new name for the database, and SQL Server will try to restore over an existing database.

• When you restore a database on the new server, you may encounter a problem «Libraries Users “(orphaned users account, according to a translation on MSDN-Comm. Translator), if a database user associated with the account, is not represented in the new server. You will need to fix this error.

Can I attach as database.mdf file, if I have no transaction log file?

The only option, when appropriate if the transaction log has been lost already after the database has completed. In any case, this is not a good idea. When you attach a database, the transaction log file, as well as the data file, you need the recovery process database (DB recovery here refers not to RESTORE DATABASE operation, and recovery.

The process that occurs whenever SQL Server starts, in which SQL Server “passes” the transaction log and generates data files in a consistent state-Comm. Translator). However, in some cases, it is possible to attach a file of data without a transaction log file, but this feature is only for those cases where a log file operations have been damaged or lost as a result of problems with the equipment and if you have no backups. Of course, the database cannot exist without a transaction log, and when joining a database without a transaction log file, SQL Server just will recreate it.

• Attach data file without an operation log file breaks the log chain and, also, it may be that in DB broken transactional or structural integrity (Depending on the State of the BD at the time of the “loss” of the transaction log). Attach operation this DB can fail regardless of whatever actions were taken.

• Copy the data files and transaction log files are only valid after you run the detach operation (detach) or after the SQL Server process has completed correctly is ensure correct completion of all transactions. Copy/move the database files to another server is a faster way to migrate the DB to another server than creating/expanding a backup, but not so safe (in that case, if you move the database files directly without copies). Just remember that you can attach DB only on the same or a more recent version of SQL Server.

My OBD lies on the SAN. I heard that SAN backups enough. Is it true?

It may be true. The main thing is that your SAN (STORAGE, network/storage system translators) supported SQL Server transaction. If so, then she will know that there are an operation in the database and the availability of these deals could mean that the information in the data files may not be complete. Because the process of recording the data changed in these transactions, the hard disk may not complete at the time of the backup. Those backups, which makes SQL Server itself, of course, consider these points.

• EMC;

• Data Domain;

For example, is a combination of software and SAN, providing support for transactions, as well as products from other vendors, but you still need to check the documentation of your SAN. Note the presence of phrases like “transaction consistency” or “transaction aware,” or something similar. If you don’t find it, I would advise you to check the restore DB before you decide what backups you have enough SAN to fulfill all your requirements for reserves. However, even after you have verified that the backups performed correctly, SAN, this does not mean that native SQL Server backups you no longer need. If you require restoring your database to a point in time, for example, you still need to do backups of the transaction log using SQL Server tools.

Usually, when you create a backup, SAN, with support for SQL Server, uses the VDI interface to SQL Server and “freezes” the DB at the time the backup created. If you run such a mechanism for creating a backup and look in the SQL Server error log, you will see a message that the IO operations were frozen.

If you rely on backups created by SAN, you still need to verify database integrity or to the “live” database, or in copies recovered from backup SAN. Otherwise, you can create a long time backups of the damaged database and not even know about it.

Why can’t I use as backup copies of data files created by Windows?

I don’t need the ability to recover to an arbitrary point in time.

SQL Server is not an ordinary desktop application. It manages your files in such a way as to ensure that all ACID properties (Atomic, Consistency, Isolated, and Durable-a little more depth is approx.. Translator). In short, to ensure the smooth completion of a transaction, SQL Server tries not to give anyone access to your files and modifies them to himself as he needed.

If you only copy the data file, ignoring the locking and transactions that can execute at the moment, this means that when you try to attach this file later, he will be in an inconsistent state, which will lead to errors.

• Only when the database entirely does not change, you can copy the file and attach it later. If there is at least the minimum probability, that when copying a file was opened at least one transaction, you most likely will get an unsuccessful backup. The only safe way to copy data and transaction log files to use as backups is a translation database in offline mode before copying.

• Much safer and easier to use the built-in SQL Server to create backups. This backup will be a full copy of your database, and all the ACID properties are satisfied.

I have a small database. Why can’t I simply “unload” each table to disk for backup?

You can use something like the SQLCMD UTILITY and empty tables in a simple text file, but then, instead of just a single command to restore the database, you will need to perform some teams. Firstly, you will need to create a new, blank database. Then, you will need to set up and upload the file each table. If any table contains an IDENTITY column, you will need to execute SET IDENTITY_INSERT on each of these tables. In the same way, you need to carefully determine the order in which you will load data into tables to ensure integrity.

1. Plus, keep in mind that all of your tables unloaded at disk at different times, so that if the data somehow changed during unloading, you don’t get the DB in a holistic status and you will have to search for errors and fix them manually.

Of course, you have the right to do so. On the other hand, you can only run the command BACKUP DATABASE, and then, when necessary, restore the backup.

Why pay money for utilities, make backups, if SQL Server it knows how to do it?

• There are three main reasons for using third-party programs that create backups: manual, automation and functionality. If you are a DBA or no DBA but forced to serve as a supplement to its substantive work, you may not know about how, where and why to configure backups in SQL Server. Excellent utility (like SQL Backup Pro) can provide you with just this type of guidance you need to ensure the safety of your DB using backups.

Backups created by SQL Server itself works fine, but you need a lot of work to do to configure them and even more to automate them. An excellent third-party utility will make the process of automation is very simple. Moreover, with its help, you will be able to automate other processes such as mirroring/log shipping and backup integrity checks.

• Finally, although SQL Server backups and do what you need, they may be doing so not the best way. For example, some utilities more efficiently compress backup, thus saving space and reducing the time the backup created. In the same way, they add functionality such as encrypting the backup file (something like that possible built-in SQL Server only if the DB itself encrypted).

If the backup network, lies can someone read it?

Until you have encrypted backup file itself-Yes-this is the standard file. If someone were to gain access to the ball, he can read it in any text editor, or just copy and restore from it on another instance of SQL Server.

• Moreover, from the backup, you can get a DB schema or data, even without restoring it. If you have the utility of SQL Data Compare, it started with the/key will be able to pull all the data from the backup in CSV format by comparing the backup DB is empty and without any password. Similarly, the same SQL Data Compare can create a script that creates the schema database.

To prevent unauthorized access, you will need to do a few things. First, make sure the globe that stores backups are available to a limited number of persons. Secondly, you should store only the backups that you need. Finally, if you are using third-party backup utilities (like SQL Backup Pro), you can encrypt the backup, so if someone will get access directly to a file, then read from there, nothing will.

Without third-party tools, you can achieve this by using Transparent Data Encryption (TDE).

To ensure the best level of security, you need to perform all of the steps above.

And anybody can change the contents of the backup?

The ability to modify the substance of the backup file not provided. Because of this backup copy of the database pages (as it existed at the time the backup), the restored copy of this database will be in absolutely that same condition as the original was at the date of backup.

• When SQL Server reads every page in the restore database, it calculates its checksum, depending on its content, and compares with the value was read from the original page at the time of the creation of a backup (assuming that you used the WITH CHECKSUM option when backing up). If someone else made changes to the backup file, these values do not match, and SQL Server will mark the page as damaged.

Is there any flag by selecting that when creating a backup, can I be sure that I always will be able to recover from it?

If the flag you mean that your backup includes performing a RESTORE VERIFYONLY after creating the backup, then no, you can not be sure that can restore your database from this backup. RESTORE VERIFYONLY can perform a set of two tests.

• First, it checks the backup header to make sure there are no errors in it.If the header is damaged, you may not be able to restore the database from this backup.

RESTORE VERIFYONLY
FROM DISK = ”

</Backup_location>The second check is only possible if you run a backup procedure using WITH CHECKSUM. It means that during backup, SQL Server calculates and verifies checksums for all the pages read. If the test succeeds, the BACKUP WITH CHECKSUM calculates and records a checksum created copy.

Accordingly, the RESTORE VERIFYONLY can be used for the checksum recalculation and verification that during backup storage has not been damaged

RESTORE VERIFYONLY
FROM DISK = ”
WITH CHECKSUM

</Backup_location>Problems can arise in two places. First, check the title during runtime VERIFYONLY does not validate everything that may affect the recovery process. It means that RESTORE VERIFYONLY can complete without errors, but the DB still cannot be retrieved from “verified” copies.

• Secondly, the CHECKSUM cannot detect damage results in memory. If a data page were updated, while in memory use and then it happened the damage before it was written to disk (and thus in the backup), then the calculation the checksum will not show any errors.

But simply confirm that the backup recorded on the same page, and that contained in the database at the time of the creation of a backup. If the page already has been damaged at the date of the creation of a backup, the error cannot be found using the checksum and restore from this backup may fail.

The only way to know for sure that you can restore from backup and the resulting database is not corrupt is to restore it and, preferably, start checking database integrity at the restored copy.

Whether the backup does not contain anything other than data? Can anyone read passwords out of it?

The backup contains not only the data. It contains the entire database structure. It includes all data, views, functions, procedures, and the rest of the code. Also, it contains all the settings database. Finally, it provides all the information about the users of the database. For the shared database, each user DB associated with a SQL Server account. Passwords for these users is stored together with the account, so these passwords in the backup will not.

However, free databases (self-contained databases — prim. Translator) there is the concept of USER WITH A PASSWORD because the very idea of free databases involves minimal communication with the server. In this case, the password will be a backup that can lead to attempts to get him out of there. Passwords not stored in plain text, they hashed, the same as the passwords of user accounts (which are stored in the master system database and, naturally, fall within its backup).

Microsoft offers several best practices for security contained databases.

Why backup indexes, statistics, and other pieces that are easy to recreate? Is it just a waste of time?

And in my opinion, the loss of time is an attempt to divide things in such a way and make a backup of only one part. Firstly, how do it? It is because the leaf level of a clustered index is a page of data. We can say that clustered indexes-it you tables, so clustered indexes must include in the backup. Of course, it is possible to allocate non-indexes in a separate filegroup and don’t make her back up, but then, after restoring the backup also that we have, we will still need to will return the filegroup to life and rebuild all indexes. So what do we do?

Statistically, the same problems occur. SQL Server copies the database statistics (and it takes up little space, because the histogram, called statistics constructed only 200 rows) and restores her together with the database. However, if after the restoration, we will recreate indexes, as do their backup, we will have to recreate and statistics. It’s also will require more time and the database, meanwhile, will remain inaccessible.

In the end, I would have argued with the wording ‘ easily recreate, because, in an emergency, this whole process can be very confusing, which inevitably will cause that people working with this database will not be able to access much more time than in the case of a simple restore from backup.

The very idea of a backup is to let you recover the database as quickly and efficiently as possible. The more complicated the recovery process, the less efficient backup. Yes, to store indexes, users, stored procedures, and other things require additional space but increases the recovery speed because all rests in one place, it’s worth the extra space.

OMG! I just deleted the table! I know that this is in the transaction log. How do I return?

Once a transaction has been committed, SQL Server will not be able to roll back her. Operations DELETE and TRUNCATE deletes data entirely different ways. The DELETE operation removes the data by using transactions, delete each row. The truncate operation only notes page on which lay the data to removed, as not used. But consequences, none of these services cannot be deleted when you view the transaction log. Instead, you need to run the process, called a point-in-time restore. You should immediately make a backup of your DB transaction log to save all changes carried out before you accidentally delete the data that you want from the table. Then, you need to perform the steps described in Chapter 6 of this book for point-in-time recovery (in MSDN there too-the translator’s).

Another option is to use third-party utilities, SQL Backup Pro, which can recover individual database objects online from existing backups.

What if I just want to create using the backup script to build the database without restoring a backup directly…?

Standard tools to create such a script in SQL Server. However, utilities, type SQL Compare, can shape it. It is easily created using the GUI, but it is possible using PowerShell:

& ‘ C:Program Files (x 86) GateSQL Red Compare 8SQLCompare.exe ‘/Backup1: C:MyBackupsMyBackupFile.bak/MakeScripts: “C:MyScriptsMyBackupScript”

Similarly, you can draw attention to SQL Virtual Restore. This utility allows you to mount a backup to your SQL Server as if you ran the backup from this recovery process but does not require the use of just the place that would be necessary for recovery. Primontirovannyj so backup looks like the most common database and you can encrypt it in any convenient manner.