We were decommissioning one of our old servers. All the DBs on the server had to be backed up and archived. Hence, I wrote a script to backup all the DBs to a fixed drive. The script uses cursors and only the location to the drive needs to be modified. In my script the location is ‘v:\stemp\’, change this to your location. The script is below:
declare @vDBname as varchar(100) declare @sqlstring as varchar(200) declare dbnamelist cursor for select name from sys.databases where name not in('tempdb') open dbnamelist fetch next from dbnamelist into @vDBname while @@fetch_status=0 begin set @sqlstring = 'backup database ['+@vDBname+'] to disk=N''v:\stemp\'+@vDBname+'_'+convert(VARCHAR,getdate(),112)+'.full.bak'' with name=N'''+@vDBname+' Full backup.''' EXECUTE(@sqlstring) fetch next from dbnamelist into @vDBname end close dbnamelist deallocate dbnamelist
I should have created a variable for the backup path, maybe I will do it for the next time. Also note that the variables for @dbname and @sqlstring may have to be increased based on your environment. Tempdb has been excluded as it cannot be backed up. This script was tested for SQL2005.
You will also have to backup the resources DB mssqlsystemresource.mdf that is located in the same directory as the master DB. This cannot be done natively from SQL and should be done at the file system level. Click here to see some commands for the resource DB.