February 2, 2020 at 8:11 pm
Hi Everyone,
I wrote the below to assist in multiple database backups. Thought you may find it helpful.
/*
- Script to backup one or multiple databases in MS SQL Server, at once.
- Written by: Gray Meiring
- Updated: 2020-02-20
- Backup file name format: DatabaseName_yyyymmdd_hhmm.bak
Instructions:
1. Add database names (without []) with a comma seperating each db. Keep the below '' in.
2. Enter Destination path (with \ at the end). Keep the below '' in.
3. Click Execute (F5)
*/
DECLARE @databases VARCHAR(MAX) = 'Database 1,Database 2,Database 3...', -------------------------------------------------------------- 1.
@name VARCHAR(50),
@path VARCHAR(256),
@fileName VARCHAR(256),
@fileDate VARCHAR(20) ;
SET @path = 'C:\......\' -------------------------------------------------------------------------------------------------------------- 2.
SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108), ':', 'h')
DECLARE db_cursor CURSOR FOR
SELECT
name
FROM master.dbo.sysdatabases
WHERE name IN ( SELECT VALUE = B.i.value('(./text())[1]', 'varchar(max)')
FROM ( SELECT X = CAST('<x>' + REPLACE(@Databases, ',', '</x><x>') + '</x>' AS XML) ) AS A
CROSS APPLY X.nodes('x') AS B(i)
)
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
Regards,
Gray Meiring
February 3, 2020 at 10:47 am
I really hate raining on your lovely script, but this has been done 1000 or more times before
personally I use the Redgate tools (compression, encryption etc) but you might want to have a look at
It's a much loved replacement to the old SQL 2000 maintenance plan
but don't be discouraged, keep posting (or even better, prove me wrong, we all love a good bunfight on here 🙂 )
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply