CREATE PROCEDURE BKP AS
DECLARE @nombre VARCHAR(15)
declare @StrDataBase nvarchar(50)
declare @StrFolder nvarchar(500)
Declare @StrBackupName nvarchar(100)
DECLARE @StrBackupFile nvarchar(600)
Declare @ttoday datetime
DECLARE micursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT name FROM sys.databases where database_id >1
OPEN MICURSOR
FETCH NEXT FROM MICURSOR
INTO @nombre
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
Set @StrDataBase = @nombre
Set @StrFolder = N'C:\backUpsql'
set @ttoday = getdate()
Set @StrBackupName = @StrDataBase + N''
+ STR(DATEPART(year,@ttoday),4,0)
+ REPLACE(STR(DATEPART(month,@ttoday),2,0),N' ',N'0')
+ REPLACE(STR(DATEPART(day,@ttoday),2,0),N' ',N'0')
+ REPLACE(STR(DATEPART(hour,@ttoday),2,0),N' ',N'0')
+ REPLACE(STR(DATEPART(minute,@ttoday),2,0),N' ',N'0')
Set @StrBackupFile = @strFolder + N'\' + @strbackupName + N'.bak'
BACKUP DATABASE @strDataBase
To DISK = @strBackUpFile
WITh NOFORMAT, INIT, SKIP,
NAME = @strBackupName
END
FETCH NEXT FROM MICURSOR
INTO @nombre
END