How to add each backups to it's own folder

SQL Server Administration
1

I'm able to create the folder and the backup, however the backups do not end up in their folder...thanks

DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @Basefolder nvarchar(128)
DECLARE @Databasefolder nvarchar(256)

-- specify database backup directory
SET @path = 'G:\Backups\Test'

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(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
--Dynamically create a folder for each database.
SET @fileName = @path + @name + '_' + @fileDate + '.bak'
SET @DatabaseFolder = @path + '' + '' + @name;
EXEC master.sys.xp_create_subdir @DatabaseFolder;
--Backup database

BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

2

You didn't put @DatabaseFolder into the @fileName variable.

3

That doesn't see to work. Thanks

4 

DECLARE @Basefolder nvarchar(128)
DECLARE @Databasefolder nvarchar(256)
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files

-- specify database backup directory
SET @path = 'G:\Backups\Test'

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(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
--Dynamically create a folder for each database.
SET @DatabaseFolder = @path + '\' +  @name; --<<--
SET @fileName = @DatabaseFolder + '\' + @name + '_' + @fileDate + '.bak' --<<--
EXEC master.sys.xp_create_subdir @DatabaseFolder;
--Backup database

BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
5

Thanks...I appreciate your help. Thanks

6

Why are you reinventing the wheel?

1 Like
7

I needed something simple...not crazy about the Ola.hallengren scripts. Thanks