April 4, 2008 at 10:40 am
I used to use this at previous clients but never saved the syntax becuase hoped I would never need to do this nor be back on sql 2000.
I need to backup all databases, obvously excluding master, pubs, model and tempdb to a file on my H:\ drive using T-SQL. However, I need to append the servername and date with time to the backup file so we can tell the diff between the files on several servers. I will then just place this in a job as opposed to running a maint plan and need to do this because of new naming conventions. Can someone please help? Thanks.
April 4, 2008 at 3:46 pm
you will need to play with it a bit to get the right format(server name, then date) or change the format of the date, but this should get you going. Once you get it formatted how you want, just change the "print @sql" to "Execute(@sql)" and you should be on your way.
declare @databases table(name varchar(50),id int identity)
declare @dbName varchar(50)
declare @max-2 int
declare @count int
declare @sql varchar(5000)
insert into @databases(name)
select name from master.dbo.sysdatabases
where dbid > 4
select @count = 1,@max= max(id) from @databases
while @count <= @max-2
BEGIN
select @dbname = name from @databases where id = @count
SET @sql = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ''e:\' + @@SERVERNAME + '-' + convert(VARCHAR(50),GETDATE()) + @dbname + '.bak'''
PRINT @sql
select @count = @count + 1
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply