December 15, 2008 at 5:43 pm
Hi All
Can anyone provide me with the script to take flat file backup of all db's on the server.
Any help would be appreciated.
December 15, 2008 at 11:15 pm
If you mean to take backup of all databases, you can user enterprise manager and create a maintanence plan that automates the backup process using scheduler.
if you wish to do it through t-sql, something like this should help you.
declare @dbname varchar(100)
declare @backup_name varchar(100)
declare cur_backup CURSOR for
select name from master..sysdatabases where name not in ('tempdb') -- you can add db names that u wanna exclude
open cur_backup
fetch next from cur_backup into @dbname
while @@fetch_status=0
begin
select @backup_name='g:\Backup\' +@dbname + convert(varchar,getdate(),112) + '.Bak'
backup database @dbname to disk=@backup_name
fetch next from cur_backup into @dbname
end
close cur_backup
deallocate cur_backup
=================================
by flat file, If you meant you wanted to keep all data in flat text file, i dont find a reason to do so when u're using rdbms..
December 16, 2008 at 5:38 am
Or do you mean flat file as in just the files?
You could stop SQL Server, then copy the mdf, ndf, ldf files, or detach the databases and then reattach afterwards, but you really should use the built in backup commands to perform all backups.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply