## SQL Statement [sql]
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
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?
2008-09-19T15:19:54+00:00
497
C#
backup-all-ms-sql-server-databases
2
Backup All MS SQL Server Databases
2009-03-05T15:11:26+00:00
663
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)
497
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 !
2008-09-19T23:55:56+00:00
24687
C#
0
0
On Backup All MS SQL Server Databases
1025
Moonshield
www.analystik.ca