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
