Use of file location tags in "Copy backup to network locatio

aultmikeaultmike Posts: 43
edited April 26, 2013 8:58AM in SQL Backup Previous Versions
Can I use the file location tags in the "Copy Backup To Network Location" option? Originally I would like to organize my backups by year-month-day. I have a finite amount of local storage and organizing them this way will make them easier to migrate to long term storage.

However, In order for SQL Backup to only keep a few backups locally and adhere to the recommendation to perform the backups locally, I'll need to organize them by database.

I hope this is clear. If there is a way to achieve this other than the way I'm approaching it I'm open to ideas!

Thanks
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    <quote>Can I use the file location tags in the "Copy Backup To Network Location" option?</quote>
    Yes you can. E.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;g:\backups\&lt;DATABASE&gt;\&lt;TYPE&gt;\AUTO.sqb&#93; WITH COPYTO = &#91;\\sqlfileserver\backups\&lt;DATABASE&gt;\&lt;TYPE&gt;\&lt;DATETIME yyyy mm&gt;\&#93; WITH ERASEFILES_PRIMARY = 2b, ERASEFILES_SECONDARY = 14b"'

    In this example, g:\ is a local folder. This command will back up all your databases to this local folder, in subfolders named after the database, then by backup type. At most, only 2 backup sets will be retained in each folder (ERASEFILES_SECONDARY = 2b). It will also copy the backup file to a network share, in subfolders named after the database, backup type and current year and month. Only 14 backup sets will be retained in each of those folders (ERASEFILES_SECONDARY = 14b).

    Why create subfolders named after the database name and backup type? This helps to reduce the number of files in any one of those folders. Whenever you use any of the ERASEFILES_* options, SQL Backup needs to determine which backup files to keep, and which to delete, every time a backup completes. If your instance contains hundreds of database, and you have transaction logs running every 10 minutes, the number of files will grow rapidly. If you only backed up to a folder named 'g:\backups\', SQL Backup will need to process thousands of files every time. This would not be an issue if you only had a few databases backed up.

    Likewise for files copied to the network share. Again, if you have a lot of backup files stored remotely, it's advisable to organise the files by database name and backup type for the reasons above.

    One cavest when using folders named after the current year and month is that when SQL Backup creates a new folder based on those attributes, it won't look into the previously created folders to delete the older files. E.g. using the example above, SQL Backup may copy today's backup files into

    \\sqlfileserver\backups\AdventureWorks\LOG\2013 02\...

    We can expect to find 14 backup sets in that folder at any one time, since we're using the ERASEFILES_SECONDARY = 14b option. Tomorrow, when SQL Backup starts storing files in

    \\sqlfileserver\backups\AdventureWorks\LOG\2013 03\...

    it won't look in the \2013 02\ subfolder to erase the older files. By April, you will have the last 14 backup sets in the February folder, and the last 14 backup sets in the March folder. Which is pretty useless as they won't form a complete recovery chain.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • aultmikeaultmike Posts: 43
    I'm still digesting most of your post but I did want to mention that the use of the 'Test" button under the Copy to network section doesnt work when you use the file location tags. The process still works but that can be confusing.
    · Share on Twitter
  • aultmikeaultmike Posts: 43
    It doesnt seem like I can use multiple file location tags when specifiying a path. Am I doing something wrong?

    Here is what I specified:

    P:\MMurphy Test\<DATETIME yyyy>\<DATETIME mm>\<DATETIME dd>\DataHubPomereneHere is what I got:

    P:\MMurphy Test\2013\<DATETIME mm>\<DATETIME dd>\DataHubPomereneI got an error when it tried to backup the database to the path above.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    SQL Backup doesn't support multiple DATETIME tags. If you really do need this feature, you can download patch 7.3.2.7, that does support it. You can download it using the following URL:

    ftp://support.red-gate.com/patches/sql_ ... _3_2_7.zip

    To apply the patch, please follow the steps below:

    1. Close all GUI connections to the SQL Server.
    2. Ensure that no SQL Backup jobs are in progress.
    3. Stop the SQL Backup Agent Service, or disable the cluster resource if on a cluster.
    4. On the SQL Server where the SQL Backup server components are installed, navigate to the installation directory. Defaults are:

    32-bit Machines - C:\Program Files\Red Gate\SQL Backup 6\(local) or <SQL Instance Name>
    64-bit Machines - C:\Program Files (x86)\Red Gate\SQL Backup 6\(local) or <SQL Instance Name>

    5. In the folder you will find an existing SQBCoreService.exe and SQL BackupC.exe. Rename the existing files, for example to OLDSQBCoreService.exe and OLDSQLBackupC.exe.
    6. Now copy in the files extracted from the downloaded zip.
    7. Restart the SQL Backup agent service/cluster resource, and ensure that it starts successfully.
    8. Perform a test backup to ensure that all is OK. Then run a backup using multiple DATETIME tags via the extended stored procedure, to see if the the folders are generated correctly.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • aultmikeaultmike Posts: 43
    AWESOME Thanks!
    · Share on Twitter
Sign In or Register to comment.