Backup filename – backup all db

Ces instructions permettent de générer le nom du fichier de sauvegarde au format ‘Base_DateTHeure.bak’

DECLARE @databasename NVARCHAR(128) = 'MyDB'
DECLARE @backupname NVARCHAR(128)
SET @backupname = REPLACE(REPLACE(REPLACE(@databasename + '_' + CONVERT(VARCHAR(100),getdate(),126) ,':',''),'.',''),'-','')+ '.bak'
PRINT @backupname

DECLARE @databasename NVARCHAR(128) = 'MyDB'

DECLARE @backupname NVARCHAR(128)

SET @backupname = REPLACE(REPLACE(REPLACE(@databasename + '_' + CONVERT(VARCHAR(100),getdate(),126) ,':',''),'.',''),'-','')+ '.bak'

PRINT @backupname

Une application de ces instructions est par exemple d’effectuer les sauvegardes de toutes les bases de données en générant les noms de fichiers :

/*-------------------------------------------------------------------
[SCRIPT] backup all databases   
[DATABASE] master
[DESCRIPTION] backup all database (change the path in the script)
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
EXEC sp_MSforeachdb @command1 = '
IF ''?'' <> ''Tempdb'' BEGIN
declare @backupname nvarchar(800), @backupcommand nvarchar(800)
set @backupname = REPLACE(REPLACE(REPLACE(''?_'' + CONVERT(VARCHAR(100),getdate(),126) ,'':'',''''),''.'',''''),''-'','''')+ ''.bak''
SET @backupcommand = ''BACKUP DATABASE ? TO DISK = ''''G:\BACKUP\''+@backupname + '''''' WITH COMPRESSION;''
PRINT @backupcommand
EXEC (@backupcommand)
END';

/*-------------------------------------------------------------------

[SCRIPT] backup all databases

[DATABASE] master

[DESCRIPTION] backup all database (change the path in the script)

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

EXEC sp_MSforeachdb @command1 = '

IF ''?'' <> ''Tempdb'' BEGIN

declare @backupname nvarchar(800), @backupcommand nvarchar(800)

set @backupname = REPLACE(REPLACE(REPLACE(''?_'' + CONVERT(VARCHAR(100),getdate(),126) ,'':'',''''),''.'',''''),''-'','''')+ ''.bak''

SET @backupcommand = ''BACKUP DATABASE ? TO DISK = ''''G:\BACKUP\''+@backupname + '''''' WITH COMPRESSION;''

PRINT @backupcommand

EXEC (@backupcommand)

END';