Red Gate forums :: View topic - Use of file location tags in "Copy backup to network locatio
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

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

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
aultmike



Joined: 08 Jan 2010
Posts: 26
Location: Canton, OH

PostPosted: Wed Feb 27, 2013 4:06 pm    Post subject: Use of file location tags in "Copy backup to network locatio Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2298

PostPosted: Thu Feb 28, 2013 7:25 am    Post subject: Reply with quote

<quote>Can I use the file location tags in the "Copy Backup To Network Location" option?</quote>
Yes you can. E.g.

Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] TO DISK = [g:\backups\<DATABASE>\<TYPE>\AUTO.sqb] WITH COPYTO = [\\sqlfileserver\backups\<DATABASE>\<TYPE>\<DATETIME yyyy mm>\] 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 7
Back to top
View user's profile Send private message Send e-mail
aultmike



Joined: 08 Jan 2010
Posts: 26
Location: Canton, OH

PostPosted: Fri Mar 08, 2013 3:41 pm    Post subject: Thanks for the through reply! Reply with quote

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.
Back to top
View user's profile Send private message
aultmike



Joined: 08 Jan 2010
Posts: 26
Location: Canton, OH

PostPosted: Mon Apr 08, 2013 1:59 pm    Post subject: Multiple File Location Tags in Path Reply with quote

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>\DataHubPomerene
Here is what I got:

P:\MMurphy Test\2013\<DATETIME mm>\<DATETIME dd>\DataHubPomerene
I got an error when it tried to backup the database to the path above.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2298

PostPosted: Fri Apr 26, 2013 1:34 am    Post subject: Reply with quote

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_backup/SQB_7_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 7
Back to top
View user's profile Send private message Send e-mail
aultmike



Joined: 08 Jan 2010
Posts: 26
Location: Canton, OH

PostPosted: Fri Apr 26, 2013 1:58 pm    Post subject: Multiple File Location Tags in Path Reply with quote

AWESOME Thanks!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group