SQL server, SQLServerPedia Syndication

Backup all the databases on a server.

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.

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s