Often, we will take the backup of a database and then we will maintain another copy of that file in a different location. Previously i used to do this in 2 steps. In first step, take the backup and then, copy that file to another location. I just learned from pinal's blog that we can do this in 1 step.

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
MIRROR TO DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO

Above query will take the backup of "AdvantureWorks" database and keeps the file at 2 locations.

Pinal has an interesting story about this. Read here.. http://blog.sqlauthority.com/2010/10/23/sql-server-taking-multiple-backup-of-database-in-single-command-mirrored-database-backup/

Published under: SQL Server Tips ·  ·  ·  ·