question

HP1979 avatar image
0 Votes"
HP1979 asked Cathyji-msft commented

Different backup folder using ola script

Hello,

We do have requirement to create separate backup folder using ola script rather than separate database folder.
Once backup completed, we are moving only full backups from the SQL server to centralized backup server.
Can anyone please assist with this request?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @HaritPatel-9575,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @HaritPatel-9575,

Suggest you try below T-SQL to achieve your requirement.

 DECLARE @name VARCHAR(50) -- database name  
 DECLARE @path VARCHAR(256) -- path for backup files  
 DECLARE @fileName VARCHAR(256) -- filename for backup  
 DECLARE @fileDate VARCHAR(20) -- used for file name
     
 -- specify database backup directory
 SET @path = 'C:\backuptest\T-SQLbackup\'  
     
 -- specify filename format
 SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
     
 DECLARE db_cursor CURSOR READ_ONLY FOR  
 SELECT name 
 FROM master.sys.databases 
 WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 AND state = 0 -- database is online
 AND is_in_standby = 0 -- database is not read only for log shipping
     
 OPEN db_cursor   
 FETCH NEXT FROM db_cursor INTO @name   
     
 WHILE @@FETCH_STATUS = 0   
 BEGIN   
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName  
     
    FETCH NEXT FROM db_cursor INTO @name   
 END   
    
     
 CLOSE db_cursor   
 DEALLOCATE db_cursor

The result is as below screenshot.

91890-screenshot-2021-04-28-111230.jpg

Please refer to the blog Simple script to backup all SQL Server databases to get more information.

If you want to use ola script to achieve your requirement, suggest you post your issue in Ola issues feedback to get better help.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.