Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Copy a MSSQL db using Detach-Attach between 2 servers using a script (vbs, perl or any ...)


How to Copy a MSSQL db using Detach-Attach between 2 servers using a script (vbs, perl or any ...)

Author
Message
distant star
distant star
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 38
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
Alvin Ramard
Alvin Ramard
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2870 Visits: 11592
You said you are "working" on a script.

What do you have so far and what's your question?



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19420 Visits: 32324
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
distant star
distant star
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 38
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 Sad . 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search