Microsoft KB Archive/255592

= INF: How to Script Device Information =

Article ID: 255592

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q255592



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

Keywords: kbinfo kbcodesnippet KB255592

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.