All that you were shy to ask about backups of Microsoft SQL Server
During presentations about backups and restoring SQL Server databases, two types of questions are usually asked. The first ones are set right in the course of the presentation from the audience, the second ones are set already after, in private conversation. These "private" questions are often more interesting and I will try to give answers to the most complex and interesting ones, instead of writing another article about how you should do backups, or why you should do backups, or even why you should check your backups (but you really should check your backups).
You can restore a backup to a different version of SQL Server, but only if the version of SQL Server on which you deploy the backup is newer than the version on which you made it. In other words, you can deploy a backup made by SQL Server 2000 to SQL Server 2005, SQL Server 2005 to SQL Server 2008 R2, or from SQL Server 2008 to SQL Server 2012, but you can never do this in the opposite direction. Each version of SQL Server makes its own changes to the database and files that store it. Microsoft will not “go back in time” and rewrite previous versions of SQL Server to support these changes. If you really need to upgrade to an older version of SQL Server, you will need to script the schema and the data itself (for example, here’s an article about this transition )
In order to determine on which version of SQL Server the backup was created, you need to look at the header of the backup file:
As a result, you will see the Major, Minor and Build versions of the instance of SQL Server on which the backup was made (as shown in the screenshot below). This will allow you to determine the appropriate version of SQL Server to restore this backup.
')
When restoring a database to a newer version of SQL Server, it may turn out that there is something incompatible with this version of SQL Server. The safest approach to migrating to a new version of SQL Server will be to launch Microsoft Upgrade Advisor (a free utility available for each version of SQL Server) on the base you want to migrate, make sure that it is ready, and then make a backup and restore it on a new instance (but only in this order, and not first try to transfer the backup, and then run the assistant).
After recovery, the database will be in compatibility mode with the version of SQL Server with which the transition was made. This means that only the functionality that was supported by the version of SQL Server on which the backup was created will be available to it. In order to take full advantage of the new version of SQL Server, you need to change the level of compatibility of the database. This can be done using the GUI, and you can script:
Different numbers denote different versions of SQL Server: 90 for SQL Server 2005, 100 for SQL Server 2008 and 2008 R2, and 110 for SQL Server 2012 (for more information about versions of SQL Server, read here - note of the translator ).
It is worth adding that not all "transitions" are possible. SQL Server will allow "jump forward" only in two versions. For example, you cannot deploy a backup made by SQL Server 2000 to SQL Server 2012. First you need to deploy it to SQL Server 2008, set the appropriate compatibility level, create a new backup, and then deploy it to SQL Server 2012.
Yes, you can do it. If you deploy a backup on another server, you need to make sure that you have the same logical drives on the new server as on the “old” server, or manually set the correct paths for the database files using the WITH MOVE command option RESTORE DATABASE:
Database files have both logical names and physical file names. You only need to register all logical file names and define a new physical location for each of them.
The main problems you may encounter are errors related to the lack of free space on the disks to which you are restoring the database, or you may forget to specify a new name for the database and SQL Server will try to restore the database over the existing database.
When you restore the database on a new server, you may encounter the “Orphaned Users” problem ( users who have lost connection with the account, according to the translation in msdn - approx. Translator ) if the database user is associated with an account not represented on the new server . You will need to correct this error.
The only option when it is valid is if the transaction log was lost after the database was properly terminated. Anyway, this is not a good idea. When a database is attached, the transaction log file, as well as the data file, is needed to carry out the database recovery process ( here, database recovery is not the RESTORE DATABASE operation, but recovery is the process that occurs every time SQL Server starts, when SQL Server “runs "According to the transaction log and results data files in a consistent state - comment of the translator ). However, in some cases it is possible to attach a data file without a transaction log file, but this feature is intended only for those cases where the transaction log file has been damaged or lost due to hardware problems and in the absence of backup copies. Of course, a database cannot exist without a transaction log, and when attaching a database without a transaction log file, SQL Server will simply recreate it.
Attaching a data file without a transaction log file destroys the chain of logs and, in addition, it may be that the transactional or structural integrity of the database is compromised (depending on the state of the database at the time the transaction log is “lost”). The operation of joining such a database may fail, regardless of what action was taken.
Copying data files and transaction log files is permissible only after a detach operation (detach), or after the SQL Server process has been correctly completed - this will ensure that all transactions are completed correctly. Copying / transferring database files to another server is a faster way to transfer a database to another server than creating / expanding a backup copy, but not so securely (in case you move the database files directly without copies). Also, you need to remember that you can perform a database attachment only on the same or a newer version of SQL Server.
This may be true. The main thing is that your SAN ( storage, Network / Storage System - approx. Translator ) supports SQL Server transactions. If it is, then it will know that there are transactions in the database and the presence of these transactions may mean that the data in the data files may not be complete, since the process of writing the data changed in these transactions to the hard disk may be not completed at the time of the backup. Those backups that SQL Server itself does naturally take these moments into account.
EMC Data Domain, for example, is a combination of software and a SAN that provides transaction support, 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 like that. If you did not find them, then I would advise you to check the restoration of the database before you decide that you have enough SAN backups to fulfill all of your backup requirements. However, even after you were convinced that SAN backups are performed correctly, this does not mean that you no longer need the “native” SQL Server backups. If you need the ability to restore your database at a time, for example, you still have to make backups of the transaction log using SQL Server.
Typically, when creating a backup, a SAN with SQL Server support, uses the VDI interface of SQL Server and “freezes” the database at the time of the backup. If you start the mechanism for creating such a backup and look at the SQL Server error log, there you will see messages that the IO operations have been frozen.
If you rely on backups created by SANs, you still need to check the integrity of the database, either on a live database or on copies restored from a SAN backup. Otherwise, you can create backups of a damaged database for a long time and not even know about it.
SQL Server is not a regular desktop application. It manages its files in such a way as to ensure the implementation of all the properties of ACID (Atomic, Consistency, Isolated, Durable - in a little more detail - approx. Translator ). In short, to ensure successful completion of transactions, SQL Server tries not to give anyone access to its files and modifies them itself as it needs.
If you simply copy the data file, ignoring the locks and transactions that can be executed at the moment, this means that when you try to attach this file later, it will be in an inconsistent state, which will lead to errors.
Only in the case when the database does not change at all, can you copy the file and attach it later. If there is at least a minimal possibility that at least one transaction was opened at the time of copying the file, you will most likely get a failed backup. The only secure way to copy data files and transaction logs for use as backups is to put the database offline before copying.
It is much safer and easier to use the built-in SQL Server engine.
to create backups. Such a backup will be a complete copy of your database, and all ACID properties will be executed.
You can use something like SQLCMD and unload the tables into a simple text file, but then, instead of simply restoring the database with one command, you have to execute a number of commands. First, you will need to create an empty database. Then, you will need to create and load each table from a file. If any table contains an IDENTITY column, you will need to perform a SET IDENTITY_INSERT on each of these tables. Also, you will have to carefully determine the order in which you will load data into tables in order to ensure integrity.
Plus, keep in mind that all your tables are uploaded to a disk at different times, so if the data was somehow changed during unloading, after recovery you will not get the database in a consistent state and you will have to manually look for errors and fix them.
Of course, you are entitled to do so. On the other hand, you can simply execute the BACKUP DATABASE command, and then, when necessary, restore this backup.
There are three main reasons for using third-party programs that create backups: management, automation, and functionality. If you are a novice database administrator or not a database administrator at all, but are forced to maintain the DBMS as an addition to your main job, you may not be aware of how, where and why you need to set up backups in SQL Server. A good utility (like SQL Backup Pro) can provide you with exactly the type of manual you need in order to ensure the safety of your databases using backup copies.
The backups created by SQL Server itself work fine, but you need to do a lot of work in order to set them up and even more to automate them. A good third-party utility will make the automation process very simple. Moreover, with its help you can automate other processes related to backups, such as mirroring / delivering logs and checking the integrity of the backup.
Finally, although SQL Server backups do what you want, they may not do it in the best way. For example, some utilities compress backup copies more efficiently, thus saving more disk space and reducing backup times. Also, they add functionality - such as encrypting a backup file (something like this is possible with the built-in SQL Server tools only if the database itself is encrypted).
Until you have directly encrypted the backup file itself - yes - this is the most common file. If someone gets access to this ball, he will be able to read it with any text editor, or just copy and start recovery from it on another SQL Server instance.
Moreover, from the backup you can get the database schema or data, even without restoring it. If you have the SQL Data Compare utility, it running with the / Export key will be able to pull out all data from the backup in CSV format, comparing this backup with an empty database and without asking for any password. Also, the same SQL Data Compare will be able to create for you a script that creates a database schema.
In order to prevent unauthorized access to the backup, you have to do a few things. First, make sure that the ball on which backups are stored is available to a limited number of people. Secondly, you should keep only those backups that you really need. Finally, if you use third-party utilities to create backups (such as SQL Backup Pro), you can encrypt the backup, so if someone can directly access the file, then nothing can read from there.
Without third-party utilities, you can achieve this using Transparent Data Encryption (TDE).
To ensure the best level of security, you need to perform all of the above.
The ability to change the contents of the backup file is not provided. Since the backup is a page-by-page copy of the database (in the form in which it existed at the time of creating the backup), the restored copy of this database will be in exactly the same state as the original was at the time of the backup.
When SQL Server reads each page, during database recovery, it calculates its checksum, depending on its content, and compares it with the value that was read from the original page at the time of the backup creation (it is assumed that you used the WITH CHECKSUM parameter when creating backup copy). If someone made changes in the backup file, these values ​​do not match and SQL Server will mark this page as damaged.
If by such a flag you mean that your backup process involves performing a RESTORE VERIFYONLY operation after creating a backup, then no, you cannot be sure that you can restore the database from this backup. RESTORE VERIFYONLY can perform a set of two checks.
First, it checks the backup header to make sure that there are no errors in it. If the header is damaged, then you will not be able to restore the database from this backup.
The second check is possible only if you run the backup procedure with the WITH CHECKSUM option. This means that during backup creation, SQL Server recalculates and checks checksums for all read pages. If he stumbles upon a page for which these sums do not converge, the backup operation will end with an error. If the check succeeds, BACKUP WITH CHECKSUM will calculate and write down the checksum of the created copy.
Accordingly, RESTORE VERIFYONLY can be used to recalculate the checksum and verify that the backup has not been damaged during storage.
Problems can arise in two places. Firstly, the header check during the execution of VERIFYONLY does not check everything that may affect the recovery process. This means that RESTORE VERIFYONLY can complete without errors, but the database will still not be able to be restored from the “verified” copy.
Secondly, CHECKSUM cannot detect memory damage. If the data page was updated while in memory and then it was damaged before it was written to disk (and, accordingly, backed up), then the calculation of the checksum does not show any error, but simply confirms that the same was written to the backup page, which was contained in the database at the time of the backup. Those. If the page was already damaged at the time of the backup, the error could not be found using the checksum and recovery from this backup may fail.
The only way to know for sure that you can recover from a backup and the resulting database is intact is to restore it and, preferably, run the database integrity check on the restored copy.
Backup contains not only data. It contains the entire database structure. It includes all the data, procedures, views, functions, and the rest of the code. Also, it contains all the database settings. Finally, it contains all the information about database users. For a regular database, each database user is associated with a SQL Server account. Passwords of such users are stored together with the account, so these passwords will not be in the backup.
However, in autonomous databases ( contained databases - approx. Translator ) there is the concept of USER WITH PASSWORD, since the very idea of ​​autonomous databases implies a minimal connection between such a database and a server. In this case, the password will be in the backup, which can lead to attempts to get it from there. Passwords are not stored in clear text, they are hashed, just like account passwords (which are stored in the system master database and, of course, get into its backup).
Microsoft offers several best practices for securing offline databases.
And in my opinion, the loss of time is an attempt to separate things in this way and make a backup of only one part. First, how to do it? For example, how to backup data without doing a backup of clustered indexes? This is not possible since the leaf level of the clustered index is a data page. .., , – , . , , , , , . ?
. SQL Server ( , , , , 200 ) . , , , . , , , .
, « », , , , , , , .
, . , . , , , , , , , .
, SQL Server . DELETE TRUNCATE . DELETE , . TRUNCATE , , . . , , . , , . , , 6 ( MSDN – . ).
– , SQL Backup Pro, online .
SQL Server . , , SQL Compare, . GUI, PowerShell:
, SQL Virtual Restore. SQL Server , , , . .
From the translator:
, . , - – ( ).
As usual, any suggestions and corrections for translation and style are welcome.
Can I deploy a backup to a version of SQL Server other than the one on which the backup was made? What problems may arise?
You can restore a backup to a different version of SQL Server, but only if the version of SQL Server on which you deploy the backup is newer than the version on which you made it. In other words, you can deploy a backup made by SQL Server 2000 to SQL Server 2005, SQL Server 2005 to SQL Server 2008 R2, or from SQL Server 2008 to SQL Server 2012, but you can never do this in the opposite direction. Each version of SQL Server makes its own changes to the database and files that store it. Microsoft will not “go back in time” and rewrite previous versions of SQL Server to support these changes. If you really need to upgrade to an older version of SQL Server, you will need to script the schema and the data itself (for example, here’s an article about this transition )
In order to determine on which version of SQL Server the backup was created, you need to look at the header of the backup file:
RESTORE HEADERONLY FROM DISK = 'd:\bu\mm.bak'; As a result, you will see the Major, Minor and Build versions of the instance of SQL Server on which the backup was made (as shown in the screenshot below). This will allow you to determine the appropriate version of SQL Server to restore this backup.
')
When restoring a database to a newer version of SQL Server, it may turn out that there is something incompatible with this version of SQL Server. The safest approach to migrating to a new version of SQL Server will be to launch Microsoft Upgrade Advisor (a free utility available for each version of SQL Server) on the base you want to migrate, make sure that it is ready, and then make a backup and restore it on a new instance (but only in this order, and not first try to transfer the backup, and then run the assistant).
After recovery, the database will be in compatibility mode with the version of SQL Server with which the transition was made. This means that only the functionality that was supported by the version of SQL Server on which the backup was created will be available to it. In order to take full advantage of the new version of SQL Server, you need to change the level of compatibility of the database. This can be done using the GUI, and you can script:
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 110; Different numbers denote different versions of SQL Server: 90 for SQL Server 2005, 100 for SQL Server 2008 and 2008 R2, and 110 for SQL Server 2012 (for more information about versions of SQL Server, read here - note of the translator ).
It is worth adding that not all "transitions" are possible. SQL Server will allow "jump forward" only in two versions. For example, you cannot deploy a backup made by SQL Server 2000 to SQL Server 2012. First you need to deploy it to SQL Server 2008, set the appropriate compatibility level, create a new backup, and then deploy it to SQL Server 2012.
Can I use the restore operation to create a copy of the database? What can go wrong?
Yes, you can do it. If you deploy a backup on another server, you need to make sure that you have the same logical drives on the new server as on the “old” server, or manually set the correct paths for the database files using the WITH MOVE command option RESTORE DATABASE:
RESTORE DATABASE NewDBName FROM DISK = 'c:\bu\mm.bak' WITH MOVE 'OldDB' TO 'c:\data\new_mm.mdf', MOVE 'OldDB_Log' TO 'c:\data\new_mm_log.ldf'; Database files have both logical names and physical file names. You only need to register all logical file names and define a new physical location for each of them.
The main problems you may encounter are errors related to the lack of free space on the disks to which you are restoring the database, or you may forget to specify a new name for the database and SQL Server will try to restore the database over the existing database.
When you restore the database on a new server, you may encounter the “Orphaned Users” problem ( users who have lost connection with the account, according to the translation in msdn - approx. Translator ) if the database user is associated with an account not represented on the new server . You will need to correct this error.
Can I attach a MDF file as a database if I do not have a transaction log file?
The only option when it is valid is if the transaction log was lost after the database was properly terminated. Anyway, this is not a good idea. When a database is attached, the transaction log file, as well as the data file, is needed to carry out the database recovery process ( here, database recovery is not the RESTORE DATABASE operation, but recovery is the process that occurs every time SQL Server starts, when SQL Server “runs "According to the transaction log and results data files in a consistent state - comment of the translator ). However, in some cases it is possible to attach a data file without a transaction log file, but this feature is intended only for those cases where the transaction log file has been damaged or lost due to hardware problems and in the absence of backup copies. Of course, a database cannot exist without a transaction log, and when attaching a database without a transaction log file, SQL Server will simply recreate it.
Attaching a data file without a transaction log file destroys the chain of logs and, in addition, it may be that the transactional or structural integrity of the database is compromised (depending on the state of the database at the time the transaction log is “lost”). The operation of joining such a database may fail, regardless of what action was taken.
Copying data files and transaction log files is permissible only after a detach operation (detach), or after the SQL Server process has been correctly completed - this will ensure that all transactions are completed correctly. Copying / transferring database files to another server is a faster way to transfer a database to another server than creating / expanding a backup copy, but not so securely (in case you move the database files directly without copies). Also, you need to remember that you can perform a database attachment only on the same or a newer version of SQL Server.
My database is on SAN. I heard that SAN backups are enough. It's true?
This may be true. The main thing is that your SAN ( storage, Network / Storage System - approx. Translator ) supports SQL Server transactions. If it is, then it will know that there are transactions in the database and the presence of these transactions may mean that the data in the data files may not be complete, since the process of writing the data changed in these transactions to the hard disk may be not completed at the time of the backup. Those backups that SQL Server itself does naturally take these moments into account.
EMC Data Domain, for example, is a combination of software and a SAN that provides transaction support, 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 like that. If you did not find them, then I would advise you to check the restoration of the database before you decide that you have enough SAN backups to fulfill all of your backup requirements. However, even after you were convinced that SAN backups are performed correctly, this does not mean that you no longer need the “native” SQL Server backups. If you need the ability to restore your database at a time, for example, you still have to make backups of the transaction log using SQL Server.
Typically, when creating a backup, a SAN with SQL Server support, uses the VDI interface of SQL Server and “freezes” the database at the time of the backup. If you start the mechanism for creating such a backup and look at the SQL Server error log, there you will see messages that the IO operations have been frozen.
If you rely on backups created by SANs, you still need to check the integrity of the database, either on a live database or on copies restored from a SAN backup. Otherwise, you can create backups of a damaged database for a long time and not even know about it.
Why can't I use as backups copies of data files created by Windows? I do not need the ability to restore to an arbitrary point in time.
SQL Server is not a regular desktop application. It manages its files in such a way as to ensure the implementation of all the properties of ACID (Atomic, Consistency, Isolated, Durable - in a little more detail - approx. Translator ). In short, to ensure successful completion of transactions, SQL Server tries not to give anyone access to its files and modifies them itself as it needs.
If you simply copy the data file, ignoring the locks and transactions that can be executed at the moment, this means that when you try to attach this file later, it will be in an inconsistent state, which will lead to errors.
Only in the case when the database does not change at all, can you copy the file and attach it later. If there is at least a minimal possibility that at least one transaction was opened at the time of copying the file, you will most likely get a failed backup. The only secure way to copy data files and transaction logs for use as backups is to put the database offline before copying.
It is much safer and easier to use the built-in SQL Server engine.
to create backups. Such a backup will be a complete copy of your database, and all ACID properties will be executed.
I have a very small database. Why can't I just “unload” every table on a disk to create a backup?
You can use something like SQLCMD and unload the tables into a simple text file, but then, instead of simply restoring the database with one command, you have to execute a number of commands. First, you will need to create an empty database. Then, you will need to create and load each table from a file. If any table contains an IDENTITY column, you will need to perform a SET IDENTITY_INSERT on each of these tables. Also, you will have to carefully determine the order in which you will load data into tables in order to ensure integrity.
Plus, keep in mind that all your tables are uploaded to a disk at different times, so if the data was somehow changed during unloading, after recovery you will not get the database in a consistent state and you will have to manually look for errors and fix them.
Of course, you are entitled to do so. On the other hand, you can simply execute the BACKUP DATABASE command, and then, when necessary, restore this backup.
Why pay money for utilities that make backups if SQL Server can do it?
There are three main reasons for using third-party programs that create backups: management, automation, and functionality. If you are a novice database administrator or not a database administrator at all, but are forced to maintain the DBMS as an addition to your main job, you may not be aware of how, where and why you need to set up backups in SQL Server. A good utility (like SQL Backup Pro) can provide you with exactly the type of manual you need in order to ensure the safety of your databases using backup copies.
The backups created by SQL Server itself work fine, but you need to do a lot of work in order to set them up and even more to automate them. A good third-party utility will make the automation process very simple. Moreover, with its help you can automate other processes related to backups, such as mirroring / delivering logs and checking the integrity of the backup.
Finally, although SQL Server backups do what you want, they may not do it in the best way. For example, some utilities compress backup copies more efficiently, thus saving more disk space and reducing backup times. Also, they add functionality - such as encrypting a backup file (something like this is possible with the built-in SQL Server tools only if the database itself is encrypted).
If the backup is on the network ball, can someone read it?
Until you have directly encrypted the backup file itself - yes - this is the most common file. If someone gets access to this ball, he will be able to read it with any text editor, or just copy and start recovery from it on another SQL Server instance.
Moreover, from the backup you can get the database schema or data, even without restoring it. If you have the SQL Data Compare utility, it running with the / Export key will be able to pull out all data from the backup in CSV format, comparing this backup with an empty database and without asking for any password. Also, the same SQL Data Compare will be able to create for you a script that creates a database schema.
In order to prevent unauthorized access to the backup, you have to do a few things. First, make sure that the ball on which backups are stored is available to a limited number of people. Secondly, you should keep only those backups that you really need. Finally, if you use third-party utilities to create backups (such as SQL Backup Pro), you can encrypt the backup, so if someone can directly access the file, then nothing can read from there.
Without third-party utilities, you can achieve this using Transparent Data Encryption (TDE).
To ensure the best level of security, you need to perform all of the above.
Can anyone change the contents of the backup?
The ability to change the contents of the backup file is not provided. Since the backup is a page-by-page copy of the database (in the form in which it existed at the time of creating the backup), the restored copy of this database will be in exactly the same state as the original was at the time of the backup.
When SQL Server reads each page, during database recovery, it calculates its checksum, depending on its content, and compares it with the value that was read from the original page at the time of the backup creation (it is assumed that you used the WITH CHECKSUM parameter when creating backup copy). If someone made changes in the backup file, these values ​​do not match and SQL Server will mark this page as damaged.
Is there any flag, setting which when creating a backup, I can be sure that I can always recover from it?
If by such a flag you mean that your backup process involves performing a RESTORE VERIFYONLY operation after creating a backup, then no, you cannot be sure that you can restore the database from this backup. RESTORE VERIFYONLY can perform a set of two checks.
First, it checks the backup header to make sure that there are no errors in it. If the header is damaged, then you will not be able to restore the database from this backup.
RESTORE VERIFYONLY FROM DISK= '<Backup_location>' The second check is possible only if you run the backup procedure with the WITH CHECKSUM option. This means that during backup creation, SQL Server recalculates and checks checksums for all read pages. If he stumbles upon a page for which these sums do not converge, the backup operation will end with an error. If the check succeeds, BACKUP WITH CHECKSUM will calculate and write down the checksum of the created copy.
Accordingly, RESTORE VERIFYONLY can be used to recalculate the checksum and verify that the backup has not been damaged during storage.
RESTORE VERIFYONLY FROM DISK= '<Backup_location>' WITH CHECKSUM Problems can arise in two places. Firstly, the header check during the execution of VERIFYONLY does not check everything that may affect the recovery process. This means that RESTORE VERIFYONLY can complete without errors, but the database will still not be able to be restored from the “verified” copy.
Secondly, CHECKSUM cannot detect memory damage. If the data page was updated while in memory and then it was damaged before it was written to disk (and, accordingly, backed up), then the calculation of the checksum does not show any error, but simply confirms that the same was written to the backup page, which was contained in the database at the time of the backup. Those. If the page was already damaged at the time of the backup, the error could not be found using the checksum and recovery from this backup may fail.
The only way to know for sure that you can recover from a backup and the resulting database is intact is to restore it and, preferably, run the database integrity check on the restored copy.
Does the backup contain anything other than data? Can anyone read passwords from it?
Backup contains not only data. It contains the entire database structure. It includes all the data, procedures, views, functions, and the rest of the code. Also, it contains all the database settings. Finally, it contains all the information about database users. For a regular database, each database user is associated with a SQL Server account. Passwords of such users are stored together with the account, so these passwords will not be in the backup.
However, in autonomous databases ( contained databases - approx. Translator ) there is the concept of USER WITH PASSWORD, since the very idea of ​​autonomous databases implies a minimal connection between such a database and a server. In this case, the password will be in the backup, which can lead to attempts to get it from there. Passwords are not stored in clear text, they are hashed, just like account passwords (which are stored in the system master database and, of course, get into its backup).
Microsoft offers several best practices for securing offline databases.
Why in backup indexes, statistics and other things that are easy to recreate? Is this just a waste of time?
And in my opinion, the loss of time is an attempt to separate things in this way and make a backup of only one part. First, how to do it? For example, how to backup data without doing a backup of clustered indexes? This is not possible since the leaf level of the clustered index is a data page. .., , – , . , , , , , . ?
. SQL Server ( , , , , 200 ) . , , , . , , , .
, « », , , , , , , .
, . , . , , , , , , , .
OMG! ! , . ?
, SQL Server . DELETE TRUNCATE . DELETE , . TRUNCATE , , . . , , . , , . , , 6 ( MSDN – . ).
– , SQL Backup Pro, online .
, …?
SQL Server . , , SQL Compare, . GUI, PowerShell:
& 'C:\Program Files (x86)\Red Gate\SQL Compare 8\SQLCompare.exe' /Backup1:C:\MyBackups\MyBackupFile.bak /MakeScripts:"C:\MyScripts\MyBackupScript", SQL Virtual Restore. SQL Server , , , . .
From the translator:
, . , - – ( ).
As usual, any suggestions and corrections for translation and style are welcome.
Source: https://habr.com/ru/post/162497/
All Articles