Backup script

  • Hi,

    I'm using ola backup script and want to have all backups in the given backup directory NOT to create Instancename\databaseName\BackupType inside the given directory.

    http://ola.hallengren.com/sql-server-backup.html

    For example, If I give backup directory as C:\Backups, the backup script should backup all databases to that directory do not want to create folders inside the given directory.

    Can you please advise what code need to be changed in backup script to achieve this?

    Thanks

  • Hi

    you need to edit the below section in the script and remove addition of current database

    SET @CurrentFilePath = @CurrentDirectoryPath + '\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '_' + @CurrentDatabaseNameFS + '_' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END + '_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@CurrentDate,120)),'-',''),' ','_'),':','') + CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '_' + CAST(@CurrentFileNumber AS nvarchar) WHEN @NumberOfFiles >= 10 THEN '_' + RIGHT('0' + CAST(@CurrentFileNumber AS nvarchar),2) ELSE '' END + '.' + @CurrentFileExtension

  • I have changed it to SET @CurrentFilePath = @CurrentDirectoryPath

    But still it's trying to create sub directories and failing

  • Can you post the script you are using to backup the database?

  • I have looked at the code. It is this code that is setting the directory structure.

    INSERT INTO @CurrentDirectories (ID, DirectoryPath, CreateCompleted, CleanupCompleted)

    SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, 0, 0

    FROM @Directories

    ORDER BY ID ASC

    You should be very careful if you change this. DatabaseBackup has been designed to delete backup files only when the backup and verification (if selected) were successful. Even then, DatabaseBackup deletes only backups of the same instance, database, and type. Therefore, you’re guaranteed to always have the most recent backup on disk. If you remove any of this information from the directory path, you no longer have this guarantee.

    This is because how xp_deletefile works: xp_deletefile is the extended stored procedure that DatabaseBackup (and the maintenance plans) uses to delete backup files. xp_deletefile deletes backup files based on a directory, a file extension, and a modified date.

    Ola Hallengren

    http://ola.hallengren.com

  • Thanks Ola.

  • Ola,

    What is your referring to DatabaseNameFS in the following script?

    SET @ErrorMessage = '';

    WITH tmpDatabasesCTE

    AS

    (

    SELECT name AS DatabaseName,

    UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','')) AS DatabaseNameFS

    FROM sys.databases

    )

    SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '

    FROM tmpDatabasesCTE

    WHERE DatabaseNameFS IN(SELECT DatabaseNameFS FROM tmpDatabasesCTE GROUP BY DatabaseNameFS HAVING COUNT(*) > 1)

    AND DatabaseNameFS IN(SELECT UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DatabaseName COLLATE DATABASE_DEFAULT,'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','')) FROM @tmpDatabases)

    AND DatabaseNameFS <> ''

    ORDER BY DatabaseNameFS ASC, DatabaseName ASC

    IF @@ROWCOUNT > 0

    BEGIN

    SET @ErrorMessage = 'The names of the following databases are not unique in the file system; ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '

    RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

    SET @Error = @@ERROR

    END

  • When you are using the database name in the directory name and in the file name in a script, there are some things to think about.

    One thing is that there are some characters that are allowed in database names, but that are not allowed in the file system.

    Another thing is that you can have database names that are unique in the database, but not unique in the file system, if you have a case sensitive SQL Server.

    So the code that you posted and also some other code are there to handle these cases.

    Please send me a mail if you have more questions about the code.

    Ola Hallengren

    http://ola.hallengren.com

  • DECLARE @intFlag INT

    SET @intFlag = 1

    WHILE (@intFlag <=5)

    BEGIN

    PRINT @intFlag

    SET @intFlag = @intFlag + 1

    CONTINUE;

    IF @intFlag = 4 -- This will never executed

    BREAK;

    END

    GO

    The [Continue] statement in the below will work similarly as above??

    IF @Directory IS NULL

    BEGIN

    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultDirectory OUTPUT

    INSERT INTO @Directories (ID, DirectoryPath, Completed)

    SELECT 1, @DefaultDirectory, 0

    END

    ELSE

    BEGIN

    WITH Directory AS

    (

    SELECT REPLACE(@Directory, ', ', ',') AS DirectoryName

    ),

    Directories AS

    (

    SELECT CASE WHEN CHARINDEX(',', DirectoryName) = 0 THEN DirectoryName ELSE SUBSTRING(DirectoryName, 1, CHARINDEX(',', DirectoryName) - 1) END AS Directory,

    CASE WHEN CHARINDEX(',', DirectoryName) = 0 THEN '' ELSE SUBSTRING(DirectoryName, CHARINDEX(',', DirectoryName) + 1, LEN(DirectoryName)) END AS String,

    1 AS [ID],

    CASE WHEN CHARINDEX(',', DirectoryName) = 0 THEN 0 ELSE 1 END [Continue]

    FROM Directory

    UNION ALL

    SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS Directory,

    CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

    [ID] + 1 AS [ID],

    CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

    FROM Directories

    WHERE [Continue] = 1

    )

    INSERT INTO @Directories (ID, DirectoryPath, Completed)

    SELECT ID, Directory, 0

    FROM Directories

    END

  • In my script I am using Continue just as a column alias.

    For more general questions about T-SQL I think that it is better if you start a new thread.

    Ola Hallengren

    http://ola.hallengren.com

  • I have looked at the code. It is this code that is setting the directory structure.

    INSERT INTO @CurrentDirectories (ID, DirectoryPath, CreateCompleted, CleanupCompleted)

    SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, 0, 0

    FROM @Directories

    ORDER BY ID ASC

    You should be very careful if you change this. DatabaseBackup has been designed to delete backup files only when the backup and verification (if selected) were successful. Even then, DatabaseBackup deletes only backups of the same instance, database, and type. Therefore, you’re guaranteed to always have the most recent backup on disk. If you remove any of this information from the directory path, you no longer have this guarantee.

    This is because how xp_deletefile works: xp_deletefile is the extended stored procedure that DatabaseBackup (and the maintenance plans) uses to delete backup files. xp_deletefile deletes backup files based on a directory, a file extension, and a modified date.

    Hi Ola,

    Is it possible for you to modify and backup script to backup all databases to one place?

    Z:\Backups\ServerName$InstanceName\BackupType (No database name in directory structure)

    for example full backup of all databases will go under Z:\Backups\ServerName$InstanceName\Full

    Thanks

  • I do not recommend changing this for the reasons that I have described, but you are free to change the script as you like.

    Ola Hallengren

    http://ola.hallengren.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply