Auto named files in multi-file backup have different names

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Auto named files in multi-file backup have different names

Postby pcassar » Mon Aug 13, 2012 10:20 pm

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!
pcassar
 
Posts: 4
Joined: Fri May 04, 2012 5:58 pm

Postby petey » Tue Aug 14, 2012 3:56 am

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
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby pcassar » Tue Aug 14, 2012 7:16 pm

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!
pcassar
 
Posts: 4
Joined: Fri May 04, 2012 5:58 pm

Postby petey » Wed Aug 15, 2012 3:23 am

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: Select all
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
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby pcassar » Wed Aug 15, 2012 6:47 pm

Thank you, that will work perfectly!
pcassar
 
Posts: 4
Joined: Fri May 04, 2012 5:58 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 1 guest