INF: How to Script Device Information (255592)

SUMMARY

Sometimes it is necessary to re-create identical database devices on another server or to reinstall an existing one with the same database devices. For example, after you change the server sort order or character set. This article contains a stored procedure, sp_help_revdevice, which you can use to easier accomplish this by scripting database device information.

MORE INFORMATION

The following procedure, sp_help_revdevice, scripts database device information for SQL Server 6.5. In combination with the sp_help_revdatabase stored procedure (introduced with SQL Server 6.5), you can use sp_help_revdevice to re-create devices and databases on a SQL Server installation.

IMPORTANT: Do not use this procedure to script raw devices or mirrored devices.

You can create the sp_help_revdevice procedure by using the following script:

USE master
GO
CREATE PROCEDURE sp_help_revdevice
As
DECLARE devicecursor CURSOR FOR 
   select name 
   ,(high-low+1)  
   ,phyname 
   ,(low/16777216)               
   from master..sysdevices where cntrltype = 0

OPEN devicecursor

set nocount on

DECLARE @devname  varchar(255)
   , @devsize     int
   , @devlocation varchar (255)
   , @devno       int
   , @line1       varchar(255)
   , @line2       varchar(255)
   , @line3       varchar(255)
   , @line4       varchar(255)
	
FETCH NEXT FROM devicecursor into @devname, @devsize, @devlocation, @devno

WHILE @@FETCH_STATUS=0
BEGIN
   IF @devname NOT IN ('master','MSDBLog','MSDBData','TEMP_DB')
      BEGIN
      select @line1 = "NAME = " + '"' + @devname + '",'
      select @line2 = "PHYSNAME = " + '"' + @devlocation + '",'
      select @line3 = "VDEVNO = "+ convert(varchar(255),@devno) + ','
      select @line4 = "SIZE = " + convert (varchar(255), @devsize)
      Print "DISK INIT"
      Print @line1
      Print @line2
      Print @line3
      Print @line4
      Print "go"
      Print ""
      END
   FETCH NEXT FROM devicecursor into @devname, @devsize, @devlocation, @devno
   END
CLOSE devicecursor
set nocount off
DEALLOCATE devicecursor

-- END sp_help_revdevice
GO