August 2, 2016 at 11:55 pm
i've created daily auto backup of all databases in my server. it creates backups on daily bases but store backups files with weird naming convention.
i want to store bak files in this way. like if i've a database named "dbCentral" so i want to take daily backup of it in a way
dbCentral mm/dd/yyyy.bak
dbCentral mm/dd/yyyy.bak
and so on
August 3, 2016 at 8:50 am
Try out this script and see if you like the format, of course modify the @path variable per your location -
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
-- Path to backups.
SET @path = 'F:\SqlServer\Backups\';
-- Get date to include in file name.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);
-- Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
-- Loop through the list to backup each database.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build the path and file name.
SET @fileName = @path + @name + '_' + @fileDate + '.bak';
-- Backup the database.
BACKUP DATABASE @name TO DISK = @fileName WITH INIT;
-- Loop to the next database.
FETCH NEXT FROM db_cursor INTO @name;
END
-- Clean up.
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
August 3, 2016 at 9:32 am
Messi (8/2/2016)
i've created daily auto backup of all databases in my server. it creates backups on daily bases but store backups files with weird naming convention.i want to store bak files in this way. like if i've a database named "dbCentral" so i want to take daily backup of it in a way
dbCentral mm/dd/yyyy.bak
dbCentral mm/dd/yyyy.bak
and so on
Define "weird naming convention" for the backup files?
Thomas Rushton
blog: https://thelonedba.wordpress.com
August 4, 2016 at 12:12 am
ThomasRushton (8/3/2016)
Define "weird naming convention" for the backup files?
like the generated .bak files have 7-8 digits randomly generated at the end of name before the .bak 😛
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply