![]() ![]() If you are using file group back ups as a means of a granular backup and restoration, keep in mind that you MUST have a backup of the Primary file group. ![]() A file group backup will back up ALL the files contained in the file group. For a backup strategy, you may have identified that the historic data only changes at the end of the year when the past years' data is migrated from the current file group to the historic file group. This helps to separate your high disk reads on one disk and high disk writes on another. The use of file groups allows you to create tables that will contain historic data on separate physical disks than tables that contain the current data. Consider a large database that contains huge amounts of historic data as well as current data that is constantly changing. Tables and indexes are created on file groups and the file group has data files that these objects are then stored on. Consider a file group a folder that can contain zero to many data files. –Fulldatabase backup BACKUP DATABASE BackThisUpĮvery database will have a primary file group which will contain the primary data file. IF EXISTS( SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)įILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’, _įILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’, _įILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’, _įILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’, _ The only exception to this is for a database that is in full or bulk logged recovery model that has not been backed up, see pseudo-simple post. In full recovery or bulk logged recovery, the log file WILL ONLY BE TRUNCATED WITH A TRANSACTION LOG BACKUP. In simple recovery, the log file is truncated upon checkpoint thereby keeping the log file a manageable size. In simple recovery model, then each full database backup is autonomous and no log file backup needs to be done, and in fact cannot be done. When the backup is restored, the backup will request the full file sizes, but it does not write empty data pages. This means that although the database is 50GB, 30GB is unused space. A native full database backup will backup the actual data, but not empty data pages. Quite often, I will see posts in the forums that ask why a database backup is 20GB while the database is 50GB and backup compression was not used. A full database backup provides the ability to restore the database to the state that it was at the time of the backup. When a full database backup is taken, then all data files, file groups, and transaction logs are backed up. The first type database backup that almost everyone is familiar with is a full backup. The recovery model will dictate when and how a transaction log is truncated as well as if a transaction log backup can be taken and if a database can be restored to a point in time. Another important consideration is the database recovery model, simple, bulk logged, or full. ![]() You must consider RTO, recovery time objective, and RPO, recovery point objective, when considering the type and frequency of your backups. ![]() This post is going to outline the different types of backups and specifically what they backup, but keep in mind that your backup plan should be based on your recovery needs. If you are just beginning SQL Server database administration, then you might not be familiar with the different types of database backups that are available and exactly what they backup. ![]()
0 Comments
Leave a Reply. |