Use Master
Declare @DatabaseName sysname
Declare @SQLCommand varchar(1024)
Declare curDBName Cursor For
Select [Name] From Master..Sysdatabases
Where [Name] Not In ('tempdb')
Open curDBName
Fetch curDBName Into @DatabaseName
While (@@fetch_status = 0)
Begin
if databasepropertyex (@DatabaseName,'Status') = 'online'
Begin
Select @SQLCommand = 'Backup Database ' + @DatabaseName +
' To Disk = ''D:\Backups\Databases\' + @DatabaseName + '.bak'' With Format'
execute (@SQLCommand)
End
Fetch curDBName Into @DatabaseName
End
Close curDBName
Deallocate curDBName
Refactorings
No refactoring yet !
Moonshield
September 19, 2008, September 19, 2008 23:55, permalink
No rating.
Login to rate!
Hi GateKiller
If you don't have many databases, lets say less than 75, the code below would do the job without cursor. (In general, cursor are not a good way to do thing). If you have too many databases, your script is good, I'll just move the condition in your if statement to the query that the cursor is made of as in the exemple below.
Hope you'll like it !
Use Master
Declare @ToExecute VarChar(8000)
Select @ToExecute = Coalesce (@ToExecute + 'Backup Database ' + [Name] + ' To Disk = ''D:\Backups\Databases\' + [Name] + '.bak'' With Format;' + char(13),'')
From Master..Sysdatabases
Where [Name] Not In ('tempdb') And databasepropertyex ([Name],'Status') = 'online'
--Print @ToExecute
Exec (@ToExecute)
GateKiller
September 22, 2008, September 22, 2008 11:59, permalink
No rating.
Login to rate!
@Moodshield: Love it! It's a very simple solution that will suit my server which only has about a dozen databases.
Many Thanks,
Stephen
I've recently blogged about this script and it seems to work very well. Does anyone has any improvements/suggestions or is it nearly perfect?