3

I am using SQL Server 2008 R2. I am trying to create a maintenance plan which backs up multiple databases into one single folder for a day.

For ex. I need to backup db1, db2, db3 and dump all these 3 .bak files into a single folder with a date.

Is this possible?

Any help would be really help. Thanks in advance.

migrated from stackoverflow.com Dec 3 '12 at 9:46

This question came from our site for professional and enthusiast programmers.

  • Let me try to explain bit clearly. for ex. i want a folder for a day something called "20121203" inside that i need all the backups like db1_20121203.bak, db2_20121203.bak, db3_20121203.bak then for the next day there should be a new folder called "20121204" inside that i need all the backups like db1_20121204.bak, db2_20121204.bak, db3_20121204.bak. – Gnanasekaran Kuppusamy Dec 3 '12 at 13:43
1

Yes it is possible Use the Maintenance plan wizard its pretty easy to use. Set your schedule type to recurring and frequency to daily on the "new job schedule form" when you get to the "Define Back Up Database Task" form select the databases you wanna backup and select "create a backup file for every database " then set path of the folder you want to save to.

hope that helps

  • But this is creating folder for each database. But what am asking is all 3 .bak files in a single folder for the day. Let me try to elaborate bit more. I have scheduled daily backup for 3 databases. When I see the backup file path, am getting 3 .bak files in it. what i would like to do is all these 3 backup files in a single folder. – Gnanasekaran Kuppusamy Dec 3 '12 at 13:39
  • for ex. i want a folder for a day something called "20121203" inside that i need all the backups like db1_20121203.bak, db2_20121203.bak, db3_20121203.bak then for the next day there should be a new folder called "20121204" inside that i need all the backups like db1_20121204.bak, db2_20121204.bak, db3_20121204.bak. – Gnanasekaran Kuppusamy Dec 3 '12 at 13:47
  • Then you are going to have to create separate maintenance plans for each database and direct the backups to the individual folders – Yugz Dec 3 '12 at 14:49
  • -1 since this does not answer the question. – Max Vernon Dec 3 '12 at 15:40
1

You can create a job that's normally backup your databases.

then In a batch file, you can create a folder with datetime, and then copy files to it ( You can do it all via SQL with XP_cmdshell:

Use this code in a .bat file:

@echo off
Set vData=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
mkdir %vData%
copy SOURCE\*.bak \%vData%

or instead of copy, you can use move.

  • Put the bat file inside the folder you want the Date Folder to be created.Or, with mkdir, select the directory you want the file to be created. – Racer SQL May 29 '17 at 13:49
1

I don't think you will be able to do what you want with a Maintenance Plan. There is no option to dynamically set the file path. I would do it as a scheduled job with t-SQL. The code below will create a directory with the current date and then run the backup. Be sure to filter the query on sys.databases to include the DBs you want to backup.


declare @FilePath varchar(100)
set @FilePath = (select 'c:\SQLBAK\'+ cast(cast(getdate() as [date]) as varchar))
EXEC master.dbo.xp_create_subdir @FilePath



DECLARE @sqlstate NVARCHAR(max)
DECLARE @dbname VARCHAR(100)
DECLARE @filename VARCHAR(100)


DECLARE BackupDB CURSOR
FOR
SELECT [name], [name]+'-'+replace(CONVERT(nvarchar(30), getdate(), 120),':','')+'.bak'
FROM [master].sys.databases
WHERE [name] like 'db%' --select dbs you want here

OPEN BackupDB

FETCH NEXT
FROM BackupDB
INTO @dbname,@filename

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlstate = '

BACKUP DATABASE ['+@dbname+'] TO  DISK = N'''+@FilePath+'\'+@filename+'''
'

--PRINT @sqlstate
EXEC sp_executesql @sqlstate

    FETCH NEXT
    FROM BackupDB
    INTO @dbname, @filename
END

CLOSE BackupDB

DEALLOCATE BackupDB


GO





Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.