Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM 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=ltrim(rtrim(@.folder))+@.@.SERVERNAME
+'_'+@.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 schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry