Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Backup script
Add to briefcase
12 posts, Page 1 of 2
12»»

Backup script Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Mani-584606
Posted Wednesday, October 03, 2012 5:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797
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
Post #1368114
Nchax Phoku
Posted Thursday, October 04, 2012 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:56 AM
Points: 32, Visits: 391
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
Post #1368334
Mani-584606
Posted Thursday, October 04, 2012 10:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797
I have changed it to SET @CurrentFilePath = @CurrentDirectoryPath

But still it's trying to create sub directories and failing
Post #1368579
sql-noob
Posted Thursday, October 04, 2012 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 19, 2013 7:11 AM
Points: 27, Visits: 124
Can you post the script you are using to backup the database?
Post #1368595
Ola Hallengren
Posted Thursday, October 04, 2012 1:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 167, Visits: 1,759
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
Post #1368669
Mani-584606
Posted Friday, October 05, 2012 4:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797
Thanks Ola.
Post #1369347
Mani-584606
Posted Sunday, October 07, 2012 10:52 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797
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
Post #1369616
Ola Hallengren
Posted Monday, October 08, 2012 12:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 167, Visits: 1,759
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
Post #1370005
Mani-584606
Posted Tuesday, October 09, 2012 4:25 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797


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
Post #1370610
Ola Hallengren
Posted Wednesday, October 10, 2012 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 167, Visits: 1,759
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
Post #1371010
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.