Enterprise Admn: Backup a database option
-
Question: how can I backup more than one database with just one backup job?
It seems that I would have to create a backup job in Ent.Man. for each database. I would like to create just one job to backup all my databases. If that's possible? I'm new to administering SQL Server. If it involves writing some kind of script, please provide some step-by-steps.
Thanks.
-
Hector,
Create a job and just make a job step for each database backup. Each step will contain a BACKUP DATABASE statement, the syntax of which is available in Books Online.
Greg
Greg
-
How 'bout this.....
declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
SELECT @sql = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''I:\MSSQL\BACKUP\'+@DBNAME+'.BAK''WITH INIT'
PRINT @sql
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
end
Just change the I:\MSSQL\BACKUP\ to the location where you want the backup file to be created. You can make this a tsql step in a job and it will back up all the databases. You can add names to the not in statement to remove other databases from the backup that you don't want.
-
In enterprise manager go to Management/ database maintainence plans. Right click on the label. Choose 'New maintainence plan' This will take you to the wizard where you can choose which databases you want to backup.
-
Thank you. Thank you. Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply