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