Red Gate forums :: View topic - Auto named files in multi-file backup have different names
Return to www.red-gate.com RSS Feed Available

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

Auto named files in multi-file backup have different names

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
pcassar



Joined: 04 May 2012
Posts: 4

PostPosted: Mon Aug 13, 2012 10:20 pm    Post subject: Auto named files in multi-file backup have different names Reply with quote

When I split up backups into multiple files, sometimes not all of the files will get the same name... the seconds portion of the filename will be different. This is probably when the seconds portion of the time is different between generation of the names for the files.

Is it possible to use <AUTO> in a multi-file backup and get the same filename for all of the files? I can of course pre-generate the file names, but I like using <AUTO>.

Thanks!
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Tue Aug 14, 2012 3:56 am    Post subject: Reply with quote

What is the <AUTO> definition that you are currently using? Could you also please provide the backup command that you are using?

Thanks.
_________________
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
pcassar



Joined: 04 May 2012
Posts: 4

PostPosted: Tue Aug 14, 2012 7:16 pm    Post subject: Reply with quote

Backup command splitting over 20 files:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [DATABASENAME] TO DISK = ''F:\SQLBackups\<AUTO>_01.sqb'', DISK = ''F:\SQLBackups\<AUTO>_02.sqb'', DISK = ''F:\SQLBackups\<AUTO>_03.sqb'', DISK = ''F:\SQLBackups\<AUTO>_04.sqb'', DISK = ''F:\SQLBackups\<AUTO>_05.sqb'', DISK = ''F:\SQLBackups\<AUTO>_06.sqb'', DISK = ''F:\SQLBackups\<AUTO>_07.sqb'', DISK = ''F:\SQLBackups\<AUTO>_08.sqb'', DISK = ''F:\SQLBackups\<AUTO>_09.sqb'', DISK = ''F:\SQLBackups\<AUTO>_10.sqb'', DISK = ''F:\SQLBackups\<AUTO>_11.sqb'', DISK = ''F:\SQLBackups\<AUTO>_12.sqb'', DISK = ''F:\SQLBackups\<AUTO>_13.sqb'', DISK = ''F:\SQLBackups\<AUTO>_14.sqb'', DISK = ''F:\SQLBackups\<AUTO>_15.sqb'', DISK = ''F:\SQLBackups\<AUTO>_16.sqb'', DISK = ''F:\SQLBackups\<AUTO>_17.sqb'', DISK = ''F:\SQLBackups\<AUTO>_18.sqb'', DISK = ''F:\SQLBackups\<AUTO>_19.sqb'', DISK = ''F:\SQLBackups\<AUTO>_20.sqb'' WITH ERASEFILES = 1b, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 0, VERIFY, CHECKSUM"', @exitcode OUT, @sqlerrorcode OUT

Here is a sample set of files generated from that:

FULL_(local)_DATABASENAME_20120807_023000_14.sqb
FULL_(local)_DATABASENAME_20120807_023000_15.sqb
FULL_(local)_DATABASENAME_20120807_023000_16.sqb
FULL_(local)_DATABASENAME_20120807_023000_17.sqb
FULL_(local)_DATABASENAME_20120807_023000_18.sqb
FULL_(local)_DATABASENAME_20120807_023000_19.sqb
FULL_(local)_DATABASENAME_20120807_023000_20.sqb
FULL_(local)_DATABASENAME_20120807_023001_01.sqb
FULL_(local)_DATABASENAME_20120807_023001_02.sqb
FULL_(local)_DATABASENAME_20120807_023001_03.sqb
FULL_(local)_DATABASENAME_20120807_023001_04.sqb
FULL_(local)_DATABASENAME_20120807_023001_05.sqb
FULL_(local)_DATABASENAME_20120807_023001_06.sqb
FULL_(local)_DATABASENAME_20120807_023001_07.sqb
FULL_(local)_DATABASENAME_20120807_023001_08.sqb
FULL_(local)_DATABASENAME_20120807_023001_09.sqb
FULL_(local)_DATABASENAME_20120807_023001_10.sqb
FULL_(local)_DATABASENAME_20120807_023001_11.sqb
FULL_(local)_DATABASENAME_20120807_023001_12.sqb
FULL_(local)_DATABASENAME_20120807_023001_13.sqb

File name format (this is the default):

<TYPE>_<INSTANCE>_<DATABASE>_<DATETIME yyyymmdd_hhnnss>

I can remove the seconds portion from the <AUTO> but the same thing could happen when crossing to a new minute.

Thanks for the help!
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2301

PostPosted: Wed Aug 15, 2012 3:23 am    Post subject: Reply with quote

I'm afraid there isn't a way to get the timestamp value to be identical for all files using your syntax. Since all the files are going into the same folder, you could use the FILECOUNT option instead, which will get you the same timestamp value, and also the sequential numbering you want e.g.

Code:
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [DATABASENAME] TO DISK = ''F:\SQLBackups\<AUTO>.sqb'' WITH FILECOUNT = 20, ERASEFILES = 1b, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 0, VERIFY, CHECKSUM"', @exitcode OUT, @sqlerrorcode OUT

_________________
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
pcassar



Joined: 04 May 2012
Posts: 4

PostPosted: Wed Aug 15, 2012 6:47 pm    Post subject: Reply with quote

Thank you, that will work perfectly!
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