June 14, 2010 at 12:33 pm
can we take backup of all the databases present in a server at a time?
thank u
June 14, 2010 at 8:36 pm
You can create 1 job that backs up all the databases. It will process them 1 at a time.
June 15, 2010 at 6:58 am
With just straight TSQL you'll have to backup the databases serially. That's how most everyone I know does it. That's how I do it. It is possible to backup in parallel, but you'll have to use some other threaded language in addition to TSQL to make it happen. But then, you'll probably see issues with contention for memory, cpu, etc. I wouldn't suggest it.
Another option, if you're on a SAN or using virtual servers, is to look to one of the newer "disk only" mechanisms of backup. Many of them are transactionally aware. You can backup hundreds of gb in only minutes using those methods.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2010 at 12:05 pm
is there any possible by using "select" statement ,
June 15, 2010 at 12:11 pm
This is a case where using a cursor makes sense. I load a cursor and then build my backup statements with dynamic sql. That way you can specify location, file name, all sorts of things.
If you're really just getting started, take a look at using one of the database maintenance plans to do the work for you. They work well, they're just not as flexible as writing your own code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2010 at 9:25 am
something like this may work if you need it
declare @command1 nvarchar(2000)
set @command1 = 'backup database ? to disk=''o:\?\?_' +
convert(varchar(20), getdate(), 112) + '.bak'''
exec sp_MSforeachdb @command1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 17, 2010 at 10:13 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply