Creating Backup Devices with Transact-SQL


To create permanently defined backup devices in T-SQL, use the system stored procedure sp_addumpdevice . The name of this procedure is a throwback to pre “Version 7.0 days, when a backup device was referred to as a dump device, and taking a backup was referred to as taking a dump. I kid you not.

The required syntax is

 sp_addumpdevice 'devtype', 'logical_name', 'physical_name' 

Where ' devtype ' is one of ' disk ' ' tape ' or ' pipe ', ' logical_name ' is the name that will be used to refer to this device, and 'physical_name' is the actual path to the file or tape device.

For example:

To create a disk backup device named NWbackup:

 USE master  EXEC sp_addumpdevice 'disk', 'NWbackup', 'D:\backups\NWbackup.bak' 

To create a tape backup device named Nwtapebackup:

 USE master  EXEC sp_addumpdevice 'tape', ' Nwtapebackup ', '\.\tape0' 

Named dump devices are handy for tape backups as it allows you to substitute an easily remembered , logical name for a tape device. For disk backups, unless you plan to reuse the same disk file over and over, you can simply specify a temporary backup device in the BACKUP command by specifying the full pathname to the backup file. Here's the syntax for a backup using a temporary device:

 BACKUP DATABASE northwind TO DISK = 'D:\backups\Nwtemp.bak' 

NOTE

After you run the sp_addumpdevice stored procedure, don't panic when you go looking for the physical files on disk and can't find them. The files aren't created until the first time you actually use the backup device.

Also, the physical pathname to the dump file is not validated until the first time you use the backup device. If a bad pathname is entered when creating the dump device, you will receive an error message similar to the following when attempting to back up to it:

[View full width]
 
[View full width]
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'mydump'. Device error or device graphics/ccc.gif off-line. See the SQL Server error log for more details.

Another advantage of using temporary backup devices when backing up to disk is that you can dynamically build the backup filename. This is often done to include a timestamp in the backup filename to indicate exactly when the backup was initiated, and to keep from overwriting or appending the backup to an existing file. The following stored procedure demonstrates an example of this approach, creating a backup filename in the format of dbname_ db_ YYMMDDHHMM.bak:

[View full width]
 
[View full width]
create proc sp_SSU_backup_db (@dbname varchar(30), @backupdir varchar(128) = 'C:\ graphics/ccc.gif MSSQL2000\BACKUP') as set nocount on -- suppress display of rowcounts set concat_null_yields_null off -- prevent concatenating nulls to string from returning null exec ('use master') -- run commands from within master database declare @starttime datetime, @endtime datetime, @hours int, @minutes int, @seconds int, @backupdev varchar(4000) -- First, validate the backup directory create TABLE #fileexist (file_exists int, file_dir int, parent_dir int) insert #fileexist exec xp_fileexist @backupdir if (select file_dir from #fileexist) != 1 begin raiserror ('Invalid backup directory specified', 16, 1) return -101 end print '########################################################## ####################' print '### Begin backing up ' + @dbname + ' database' + space(48 - datalength (@dbname)) graphics/ccc.gif + '###' select @starttime = getdate() print '### backup of ' + @dbname + ' started at ' + convert (varchar(30), @starttime, 9) + space(25 - datalength(@dbname)) + '###' select @backupdir = rtrim (@backupdir) -- trim off the backslash (/) if included as you will be adding it in anyway if substring(reverse(@backupdir), 1, 1) = '\' select @backupdir = substring(@backupdir, 1, datalength(@backupdir) - 1) --Build backup filename in format of dbname_db_YYYYMMDDHHMM.bak select @backupdev = @backupdir + '\' + @dbname + '_db_' + convert(char(8), getdate(), 112) --append YYYYMMDD + substring(convert(char(8), getdate(), 108), 1, 2) --append Hour + substring(convert(char(8), getdate(), 108), 4, 2) --append minutes + '.bak' print '### backing up database ' + @dbname + ' to ' + @backupdev + space (50 - datalength(@dbname) - datalength(@backupdev)) + '###' print '####################################################################### #######' backup database @dbname to DISK = @backupdev with nounload, init, skip select @endtime = getdate() print '####################################################################### #######' print '### backup of ' + @dbname + ' finished at ' + convert (varchar(30), @endtime, 9) + space(24 - datalength(@dbname)) + '###' print '### ###' select @hours = datediff(ms, @starttime, @endtime)/3600000 select @minutes = (datediff (ms, @starttime, @endtime) / 60000 ) % 60 select @seconds = (datediff (ms, @starttime, @endtime) / 1000 ) % 60 print '### Time to complete: ' + str(@hours, 2, 0) + ' hours, ' + str(@minutes, 2, 0) + ' minutes, ' + str(@seconds, 2, 0) + ' seconds ###' print '####################################################################### #######' print '' print '' return

You can also use the extended stored procedure xp_sqlmaint to produce the same results. This stored procedure calls sqlmaint.exe, and is the basis for jobs created by the Database Maintenance Plan Wizard. The following example creates a backup of the Northwind database in the default backup directory with a filename Northwind_db_YYYYMMDDHHMM.BAK:

 EXEC master.dbo.xp_sqlmaint  '-D Northwind -BkUpMedia DISK -BkUpDB  -UseDefDir  -BkExt "BAK"' 


Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net