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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » How to Copy a MSSQL db using Detach-Attach...

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

Rate Topic

Display Mode

Topic Options

Author

Message

distant star

Posted Monday, March 02, 2009 12:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 06, 2009 10:38 AM
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

Post #666813

Alvin Ramard

Posted Monday, March 02, 2009 12:24 PM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031, Visits: 7,383

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.

Post #666815

Grant Fritchey

Posted Tuesday, March 03, 2009 5:55 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #667210

distant star

Posted Wednesday, March 04, 2009 12:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 06, 2009 10:38 AM
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

Post #668614

« Prev Topic | Next Topic »

Permissions