October 3, 2012 at 5:48 pm
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
October 4, 2012 at 6:11 am
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
October 4, 2012 at 10:49 am
I have changed it to SET @CurrentFilePath = @CurrentDirectoryPath
But still it's trying to create sub directories and failing
October 4, 2012 at 11:10 am
Can you post the script you are using to backup the database?
October 4, 2012 at 1:27 pm
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
October 5, 2012 at 4:58 pm
Thanks Ola.
October 7, 2012 at 10:52 pm
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
October 8, 2012 at 12:57 pm
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
October 9, 2012 at 4:25 pm
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
October 10, 2012 at 9:34 am
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
November 1, 2012 at 11:11 am
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
November 1, 2012 at 2:23 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply