Database Backups??
-
Hello All...
I am looking for a way to backup many databases at the same time. Is there a way to create a Stored Procedure that can do this to a lsit of databases? And can I get the list of databases dynamically?
Thanks for your help
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
How long a minute is....
Depends on what side of the bathroom door you are on. -
Easiest way is to build a maintenance plan. If you want to do it yourself you can just do a select against sysdatabases (or some other table with the db names you want to use), issue a backup command for each - using either TSQL or DMO.
Andy
-
Thanks Andy
I am not sure how to write that is my problem. I will more than likely use the sysdatabases table to get a list of databases that I want to backup. I cannot do this one at a time however, there are approx 400 databases on that server.
Thanks
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
How long a minute is....
Depends on what side of the bathroom door you are on. -
You have to back up each one individually - just cursor through the list. Why wouldnt the maint plan work? Probalby have some scdripts in the scdript library that will do what you need.
Andy
-
Here is a simple tsql script that when executed will go through and backup all of the databases on a machine to the given directory, it can be changed to exclude any db's you don't want there...
declare @dbname varchar(80), @sql varchar(2000)
select @dbname=min(name) from master..sysdatabases where name not in ('tempdb','Northwind','pubs','Rebate')
while @dbname is not null
begin
set @sql='backup database '+@dbname+' to disk=''d:\mssql\backup\'+@dbname+'.bak'' with init'
--print @sql
exec (@sql)
select @dbname=min(name) from master..sysdatabases where name not in ('tempdb','Northwind','pubs','Rebate') and name>@dbname
end -
This is the way I do it.
sp_MSForEachDB '
IF ''?'' NOT IN (''db1Idontwant'', ''db2idontwant'') -- You could create a table for these and other cotrol info on this.
begin
BACKUP DATABASE
--Brackets to cover odd names.TO DISK = ''driveletter:\mypath\?_Full.bac''
WITH
INIT,
NAME = ''? Full Backup''
END
'
-
Andrew,
Why not create a Maintenance plan that does them all.
Or do you want to back up the databases simultaniously ?
Because if that is what you want You should create multiple Maintenace plans.
Very Easy just try the wizzard once ......
-
I have created a cursor looking for each database on each server. This then allows you to do somethings like check to see if the backup exists, whether the dates are current, which you can also do in the backup log. This then checks to see if there is enough space, if not it emails me and copies to a secondary location. Checks to see if the maintenance plan completed, and if it fails it tries it again. Emailing my pager with the results. I have alot of large backups, that can't be run during the day. And this gives me three chances to get it backed up.
-
When performing a backup on a given database, what happens if some DML/DDL is performed on that database during the backup? Will SQL Server ensure that the backup is a consistent picture across all tables, or should the tables be locked before doing the backup to be sure? Thanks for any advice.
--Brackets to cover odd names.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply