Thanks for reverting back.
So far I have been trying to use the OSQL utility to get it done, the reason for which is that I want this script to run on all the versions of SQL server without any problem. If I use sqlcmd, some of the options will be invalid for SQL2000 where sqlcmd will not be available.
Same is the case with the powershell. It needs to be there prior to running the script.
Hence, I want to use perl or vb script (but am a novice in both of them ).
Here is what I have already tried till now:
osql -h-1 -E -d master -Q"select @@version; select serverproperty ('collation'); use TEST; select databasepropertyex ('TEST','collation'); select name, filename from sysfiles" >>C:\TEST_out
Similarly I am trying for the next step, detaching :
osql -h-1 -E -d master -Q"ALTER DATABASE TEST SET SINGLE_USER;exec sp_detach_db 'TEST','TRUE'" >>C:\TEST_out
osql -h-1 -E -d master -Q"CREATE DATABASE [TEST] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TEST1.mdf' ),( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TEST_log.ldf' ),( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TEST2.ndf' ),( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\TEST3.ndf' ),( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TEST4.ndf' ) FOR ATTACH">>C:\TEST_out
But for the first step itself the output file is not properly formatted 🙁 . It contains lot of blank lines as shown below:
Contents of C:\TEST_out:
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]a
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Win
dows NT 5.1 (Build 2600: Service Pack 2)
(1 row affected)
SQL_Latin1_General_CP850_BIN2
.
.
.
.
l
o
t
o
f
b
l
a
n
k
l
i
n
e
s
.
.
.
(1 row affected)
SQL_Latin1_General_CP850_BIN2
.
.
.
.
l
o
t
o
f
b
l
a
n
k
l
i
n
e
s
.
.
.
(1 row affected)
TEST1
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST1.mdf
TEST_log
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_log.ldf
TEST2
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST2.ndf
TEST3
C:\Program Files\Microsoft SQL Server\MSSQL.1\TEST3.ndf
TEST4
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST4.ndf
(5 rows affected)
Best Regards,
Distanttstar