That's my question.

Thanks,
Tom

Yes. Ultra-simple example:

CREATE PROC myBackupProc
AS
BACKUP DATABASE master TO DISK = 'C:\master.bak'

Show quoteHide quote

To add on to Tibor, This procedure will Backup Master, MSDB and all the User
databases. This script will create the unique Backup
files names, this will ensure that old backup sets were not overwritten.


Script


CREATE PROCEDURE BACKUP_SP AS
BEGIN
SET NOCOUNT  ON
DECLARE  @NAME VARCHAR(100),
DECLARE  @DBNAME VARCHAR(100)
DECLARE  BACKUP_CUR  CURSOR  FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')


OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @DBNAME


WHILE @@FETCH_STATUS=0


BEGIN


SELECT  @NAME='C:\backup\'+@DBNAME+'_'+ltrim (rtrim (convert
(char,getdate(),105)))+'Dump.bak'
      BACKUP DATABASE @DBNAME TO  DISK = @NAME  WITH INIT ,  NOUNLOAD , NAME
=   @DBNAME, NOSKIP, STATS = 10, NOFORMAT
      FETCH NEXT FROM BACKUP_CUR INTO @DBNAME


END


CLOSE BACKUP_CUR
DEALLOCATE  BACKUP_CUR
END


How to Execute:
-----------------


EXEC BACKUP_SP


This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.

Thanks
Hari
Show quoteHide quote

Problem on scripting stored procedures
Telling if SQL2005 is running
SMSS - script to new window uses sp_executesql?!
error importing data from Access to SQL Server
View Image