March 2, 2009 at 12:21 pm
Hello,
I am working on an automation script using detach-attach method for which I need help of you colleagues.
Following is the plan which has to be executed using a single script:
1. Check for the source and target SQL Server Versions (as source can be SQL 2000 or 2005) and compare to find the possibilities for any failure. E.g., if source SQL2000 BIN collation and Target SQL 2005 BIN2 collation, then system copy is not possible.
2. List and store the current path of Source DB files (data and log files of say TEST db) and free space available on the target host and throw an error msg if not enough space available.
2. Detach the db and zip the files prior to transfering over the network.
3. Once the Zipped files are copied to the target Server on a different host, unzip the files and attach the DB.
Please help me in this issue.
Thanks in advance,
Distantt Star
March 2, 2009 at 12:24 pm
You said you are "working" on a script.
What do you have so far and what's your question?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 3, 2009 at 5:55 am
You really need to show what's been done so far, even if you haven't done much.
What language are you using for the scripting? Are you doing everything through T-SQL and plan to run it through sqlcmd? Is it a .NET language? Have you considered PowerShell?
Do you have the detach/attach scripts done & tested? I'd start there to be sure I had that in hand, because after that it's just copying files around.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 12:54 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply