Move SQL 7 DB to Another SQL 7 Server

Hello,
I'm not a DBA (obviously!) and want to perform what I suspect is a fairly
easy task. However, I'm having troble finding how to do it.
I want to move a database off of one SQL 7 server and onto another existing
SQL 7 server. How can I go about doing this?
Thanks for your help, it's very much appreciated!
BHave a look at these. While some may be specific to 2000 it should note when
it is.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Brian" <bhanley72@.yahoo.com> wrote in message
news:OPGr1az$EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm not a DBA (obviously!) and want to perform what I suspect is a fairly
> easy task. However, I'm having troble finding how to do it.
> I want to move a database off of one SQL 7 server and onto another
> existing
> SQL 7 server. How can I go about doing this?
> Thanks for your help, it's very much appreciated!
> B
>

Move SQL 7 DB to Another SQL 7 Server

Hello,
I'm not a DBA (obviously!) and want to perform what I suspect is a fairly
easy task. However, I'm having troble finding how to do it.
I want to move a database off of one SQL 7 server and onto another existing
SQL 7 server. How can I go about doing this?
Thanks for your help, it's very much appreciated!
B
Have a look at these. While some may be specific to 2000 it should note when
it is.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Brian" <bhanley72@.yahoo.com> wrote in message
news:OPGr1az$EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm not a DBA (obviously!) and want to perform what I suspect is a fairly
> easy task. However, I'm having troble finding how to do it.
> I want to move a database off of one SQL 7 server and onto another
> existing
> SQL 7 server. How can I go about doing this?
> Thanks for your help, it's very much appreciated!
> B
>

Move SQL 7 DB to Another SQL 7 Server

Hello,
I'm not a DBA (obviously!) and want to perform what I suspect is a fairly
easy task. However, I'm having troble finding how to do it.
I want to move a database off of one SQL 7 server and onto another existing
SQL 7 server. How can I go about doing this?
Thanks for your help, it's very much appreciated!
BHave a look at these. While some may be specific to 2000 it should note when
it is.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Brian" <bhanley72@.yahoo.com> wrote in message
news:OPGr1az$EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I'm not a DBA (obviously!) and want to perform what I suspect is a fairly
> easy task. However, I'm having troble finding how to do it.
> I want to move a database off of one SQL 7 server and onto another
> existing
> SQL 7 server. How can I go about doing this?
> Thanks for your help, it's very much appreciated!
> B
>

Move Files in Data Flow

Hi all,

I'm trying to find some way to implement a move file task in my Data Flow. I parse through a flat file, retrieve a value, then I'm using a lookup table to check if that value exists in a table... if the value doesnt exist, I need to move this file to a new directory and insert the value into a different table. Does anyone have any ideas about how to move the file while using the Data Flow pane?

Or is there some way to pass information from the look up table to the Control flow pane and use the Move File Task there? I'm trying to stay away from a Script task or a Script component unless it's unavoidable. I appreciate any ideas!

Can you use a row count transform? Put it on the error output of the lookup. If the value doesn't exist, the variable you specify in the row count will be incremented by 1. Then you'd use that variable in an expression on a precedence constraint between the data flow and the File task.

Note - it sounds like you are processing a single value in the data flow. If you are trying to process multiple items, this may not work.

|||

You could also try the Import/Export Column transforms. I've never used them myself but I think they do what you're talking about.

-Jamie

|||

The import/export columns appends or creates the column information to a file. This isnt quite what I need. The existing file where I'm parsing the serial number using a Script Source component needs to be copied to a directory if that file is valid. There is one serial number per file (with other necessary information) if the serial is validated, then that particular file needs to be copied to a folder (like some Validated folder). In this way, files with invalid/valid serial numbers need to be sorted to a Invalidated/Validated folder(s) after they have been checked.

So far I have a data flow task within a Foreach Loop container and within the data flow task, the Script component grabs the serial, and next a Lookup component checks whether this serial is valid (normal output) or invalid (error output). Now from there, I'm not sure how to proceed because I want to use this information to move the file to its necessary folder (Validated/Invalidated). I hope this makes more sense!

|||

See my post above. Add a row count transform to the error output. If the file has a valid serial number, the row count will be zero, if it is invalid, rowcount will be one. In the control flow, add two File System tasks - one that moves to the validated folder, the other that moves to the invalidated folder. Add a precedence constraint between the data flow and each file system task:

Code Snippet

DF

|

|-|

| |

FS FS

Set the validated precedence constraint to Evaluation Operation : "Expression and Constraint", Value: "Success", Expression: @.rowCountVariable==0

For the invalidated precedence constraint, set the Evaluation Operation : "Expression and Constraint", Value: "Success", Expression: @.rowCountVariable==1

|||

Thanks Mr. Welch! I missed the earlier post, sorry bout that! Worked perfectly... !

Move Files in Data Flow

Hi all,

I'm trying to find some way to implement a move file task in my Data Flow. I parse through a flat file, retrieve a value, then I'm using a lookup table to check if that value exists in a table... if the value doesnt exist, I need to move this file to a new directory and insert the value into a different table. Does anyone have any ideas about how to move the file while using the Data Flow pane?

Or is there some way to pass information from the look up table to the Control flow pane and use the Move File Task there? I'm trying to stay away from a Script task or a Script component unless it's unavoidable. I appreciate any ideas!

Can you use a row count transform? Put it on the error output of the lookup. If the value doesn't exist, the variable you specify in the row count will be incremented by 1. Then you'd use that variable in an expression on a precedence constraint between the data flow and the File task.

Note - it sounds like you are processing a single value in the data flow. If you are trying to process multiple items, this may not work.

|||

You could also try the Import/Export Column transforms. I've never used them myself but I think they do what you're talking about.

-Jamie

|||

The import/export columns appends or creates the column information to a file. This isnt quite what I need. The existing file where I'm parsing the serial number using a Script Source component needs to be copied to a directory if that file is valid. There is one serial number per file (with other necessary information) if the serial is validated, then that particular file needs to be copied to a folder (like some Validated folder). In this way, files with invalid/valid serial numbers need to be sorted to a Invalidated/Validated folder(s) after they have been checked.

So far I have a data flow task within a Foreach Loop container and within the data flow task, the Script component grabs the serial, and next a Lookup component checks whether this serial is valid (normal output) or invalid (error output). Now from there, I'm not sure how to proceed because I want to use this information to move the file to its necessary folder (Validated/Invalidated). I hope this makes more sense!

|||

See my post above. Add a row count transform to the error output. If the file has a valid serial number, the row count will be zero, if it is invalid, rowcount will be one. In the control flow, add two File System tasks - one that moves to the validated folder, the other that moves to the invalidated folder. Add a precedence constraint between the data flow and each file system task:

Code Snippet

DF

|

|-|

| |

FS FS

Set the validated precedence constraint to Evaluation Operation : "Expression and Constraint", Value: "Success", Expression: @.rowCountVariable==0

For the invalidated precedence constraint, set the Evaluation Operation : "Expression and Constraint", Value: "Success", Expression: @.rowCountVariable==1

|||

Thanks Mr. Welch! I missed the earlier post, sorry bout that! Worked perfectly... !

move file task using variable

Hi,

Using the 'for each' container, I am setting a user variable (type = string) which will hold the source file name and path of files to process and then move to an archive directory.

In the move file task (which is in the for each loop container) I have set the destination to a file connection, and the source to : Issourcepathvariable=True; sourcevariable=user::sourcefilename

BUT

when I run the package, I get the following error : Error at File System Task : "Source Path" is not valid on operation type "move file".

I have tried all sorts of things relating to expressions etc. but cannot get this to work. I am obviousley doing something fundementally wrong...can anyone help me?

Thanks

Kevin

Kevin,

It looks like you may have a problem in the user::sourcefilename variable. If I were you I would put a breakpoint on the file system task and, when it breaks at debug-time, put a watch on user::sourcefilename. See what teh value in there actually is.

-Jamie

|||

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

|||

Kevin,

did you try to enter a "default" or "dummy" filename for that? I remember that I had some problems in a similar case...

|||

kevin w wrote:

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

Kevin,

Yes, that makes perfect sense. Set DelayValidation=TRUE on your File System Task. I *think* that will solve the problem.

-Jamie

|||

Cheers Jamie,

It's always the little things the trip me up!!!!

Your solution worked a treat.

Kevin

|||

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

|||

AYK wrote:

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

Annie,

Are you trying to move all files which match the pattern "*.txt"? If so, look into using the MULTIFILE connection manager.

Here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/10bdc56e-c5cd-4ddb-b2f7-375fe57fe8b2.htm

-Jamie

|||

Jamie

Yes I am trying to move all files with *.txt into an archive folder. I understand the use of multifile connection manager but I am not sure how I can associate the connection manager to a File System task to move file. In the file system task, for the source it asks for a single file name and does not allow me to specify the multifile connection manager

Thanks

Annie

|||

Ah yeah, you're right. Sorry about that.

In this case then you will need to use a ForEach loop with the FileEnumerator. Loop over all the *.txt files and move them one by one to your destination.

-Jamie

move file task using variable

Hi,

Using the 'for each' container, I am setting a user variable (type = string) which will hold the source file name and path of files to process and then move to an archive directory.

In the move file task (which is in the for each loop container) I have set the destination to a file connection, and the source to : Issourcepathvariable=True; sourcevariable=user::sourcefilename

BUT

when I run the package, I get the following error : Error at File System Task : "Source Path" is not valid on operation type "move file".

I have tried all sorts of things relating to expressions etc. but cannot get this to work. I am obviousley doing something fundementally wrong...can anyone help me?

Thanks

Kevin

Kevin,

It looks like you may have a problem in the user::sourcefilename variable. If I were you I would put a breakpoint on the file system task and, when it breaks at debug-time, put a watch on user::sourcefilename. See what teh value in there actually is.

-Jamie

|||

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

|||

Kevin,

did you try to enter a "default" or "dummy" filename for that? I remember that I had some problems in a similar case...

|||

kevin w wrote:

thanks Jamie.

I have removed the file system file task from the container, and run the package with a breakpoint on the begining of every iteration of the loop. By looking at the watch window, I can see that the variable is being set correctly with the first filename in the filesystem folder.

When I add the filemove task however and add the variable as the source, the package seems to want to validate the variables contents before it has been set by the container, and I am therefore getting an error message.

Does this make sense?

Cheers, Kevin

Kevin,

Yes, that makes perfect sense. Set DelayValidation=TRUE on your File System Task. I *think* that will solve the problem.

-Jamie

|||

Cheers Jamie,

It's always the little things the trip me up!!!!

Your solution worked a treat.

Kevin

|||

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

|||

AYK wrote:

Kevin and Jamie

I am trying to move a file from a folder to an archive folder. I do not know how to create an expression. The file name will change everyday. It is a txt file. How can I specify to move a file *.txt from a folder to archive folder. Any help will be greatly appreciated.

Thanks

Annie

Annie,

Are you trying to move all files which match the pattern "*.txt"? If so, look into using the MULTIFILE connection manager.

Here in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/10bdc56e-c5cd-4ddb-b2f7-375fe57fe8b2.htm

-Jamie

|||

Jamie

Yes I am trying to move all files with *.txt into an archive folder. I understand the use of multifile connection manager but I am not sure how I can associate the connection manager to a File System task to move file. In the file system task, for the source it asks for a single file name and does not allow me to specify the multifile connection manager

Thanks

Annie

|||

Ah yeah, you're right. Sorry about that.

In this case then you will need to use a ForEach loop with the FileEnumerator. Loop over all the *.txt files and move them one by one to your destination.

-Jamie