rbkasce.blogg.se

• USE ADVENTUREWORKS2012 GO FULL

USE ADVENTUREWORKS2012 GO FULL

The key is to always have an unbroken chain of log backups since your last FULL backup to restore to, and only use ad-hoc backups, be they COPY_ONLY or not, when you really need to. It’s a bit of a cliche to say that you need to test your restore strategy before the event of a disaster, but it really is true. It doesn’t make a great deal of sense to have full backups running outside of your typical backup strategy, as it makes the restore strategy confusing. STANDBY = N 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012_RollbackUndo_09-02-53.bak',įROM DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\log.trn' WITH FILE = 1, NOUNLOAD, STATS = 10 GO

RESTORE DATABASE FROM DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH FILE = 2, Restore AdventureWorks from a full backup taken on this server, making sure it is in full recovery mode:ĮXEC _delete_database_backuphistory = N 'AdventureWorks2012' GO I’m using a copy of AdventureWorks because it’s small and quick to backup/restore, and because it s easy to clear the backup history to make the example below easy to follow. You need to have to restore a copy of the database you recently backed up on the server. Additionally, you can restore any logs taken against the database to the COPY_ONLY version to roll it forward from a restore.Īs an example, I’m going to restore a database and make some changes to it, taking a COPY_ONLY backup, making some more changes to the database, and then dropping and restoring both the original backup and the COPY_ONLY backup and restoring the log to it. It’s important to note here that a full backup that is not COPY_ONLY will not break the log chain you can take a full backup and it will not affect the log chain at all, it will only break the differential chain. The same here can also be said for log COPY_ONLY backups it’s an ad-hoc backup that does not alter the transaction log at all there’s no clearing down of the log. The COPY_ONLY backup is not part of the restore log: so when restoring from a full backup you can ignore it. COPY_ONLY was introduced in SQL Server 2005, and allows you to take ad-hoc backups of either the log or a full backup without breaking the backup chain for differential backups. Recently I have been looking at the COPY_ONLY feature for taking backups of a database.