Copy items from one SQL Database to another...

...such as stored procedures, tables etc.

Initially this started as a case of "doh, I should just be using one database here, not two", and I was simply wanting to copy database tables. In VS2K5 I tried as there is a right click menu option of copy when a table is selected but this doesn't work for me with any database object of any kind.

So fast forward to the present...I now am attempting to deploy an app to a hosting service, 1&1.com. I am allowed only one database in my current package, which should be fine for now. So I had to combine the ASPNETDB along with 2 other databases. It took a bit of time, but I got everything done, I thought, and posted to the servers. While debugging I get an error saying a stored procedure can not be found. And it indeed is not.

This really confuses me as I made the changes in VS2k5, shut down and restarted to make sure I didn't miss anything, then used SQL Server Man. Studio to make a .bak file to upload to my hosting service. It never occurred to me to verify the changes I made in VS2k5 were actually on the database when viewed there. Well, they aren't, and I have no idea why. That would be issue #1 I suppose.

So after giving the background info here, what I am looking for help with is how to get the changes I am making in VS2k5 to also be present when viewed from SQL Server Man. Studio as the only means of posting a db to my hosting provider is by using a .bak file.

Also, why is it a project template I download has a .mdf file I am not able to even see in SQL Server Man. Studio? I guess if I had this answer the issue would be resolved.

TIA

Regards,

Joe

When you say you want to copy tables, do you mean the data in the tables or just the structure of the tables? If it is just the structure you could script out the tables and stored procedures and execute them against your other database.

|||

OK...I'll start looking for pointers on what exactly you mean. I honestly have no idea as I am not an SQL guy, and am new to ASP.NET/VS/VB etc.

If you can point me in the direction of what you mean that would be great.

I appreciate the response.

Regards,

Joe

|||

I think I was too long winded and did not communicate my question well in an earlier post.

I want to transfer database tables and stored procedures from datbase1 and database2 to database3, and end up with just database3. (I don't care one way or the other about the data in the tables) I am finding posts regarding how to do this, but most expect the database to be viewable in SQL Server management studio, and I can not see all the databases there. In one case I have a website completed from a MSDN template, and in another case the database was created as a part of a web app I created from scratch. Both were created using VS2K5 pro.

The odd bit is that I created all the databases from within VS2K5 while working on web apps. So I am very confused as to why some databases show up, some don't, and some work done doesn't show up in the database that is showing up in server management studio. (to clarify, all the databases are visible from VS2K5, but only one is visible in the server management studio)

Any help would be great, thanks

TIA

Regards,

Joe

|||Right click on the database and select Tasks > Generate Scripts. Follow the steps and select stored procedures and tables to be scripted. It will generate a sql script that you can run on another database that will create those tables and stored procedures.|||

OK. I did see that option and was hoping to get a response confirming that.

My big problem though, is that I can't even see one of the databases in the management studio. I am not sure what I'm missing, but I have tried to make it visible but have failed. I do not understand this because I created all the databases from within VS2K5, so why I can see some but not others.

Any pointers in this regard would be much appreciated.

Regards,

Joe

|||

Hi Joe,

Are you sure that the database has been attached to the SQL Server instance? Because some databases are attached dynamically at runtime.

You can try to attach the database in SQL Server management studio.

|||

I am starting to feel very ignorant, lol. Not that it's a terrible thing, we all have to learn right...

I think in order to grasp what is going on here I need to understand what's happening when I add the database in VS2K5. If in VS2K5 I "add an existing item" (the database that SQL Server management studio sees) then why am I getting two instances of the database? I don't understand why I end up with two copies. I am not asking for another db to be created as far as I know.

I appreciate the responses posted so far, they are helpful, but I don't understand why I need to take either action suggested. I am certainly not unwilling to do either. But if I do I am still going to have no idea why I have tow copies of the db, and unless something changes will the same result not continue? When I make changes in either software (VS2K5/SQL Server management studio) will I not be changing just one db?

Still very uncertain here...

Any comments are appreciated.

Regards,

Joe

|||

OK...

I may have discovered the issue.

I went poking around again paying veryclose attention to every step as I am adding a database to my site. As I stepped through I get to the point where I test the connection and it fails, saying it's in use by another process, almost without thinking I begin to do what I have done numerous times in the past. I was going to stop the instance of SQL server. And it was then I realized..why? As I considered this I realized that did not make sense. So i looked at the dialog for add database and realized the dropdown had SQl Database selected, not SQL server. I feel relatively confident this is the cause of my troubles.

For anyone who is having siilar issues, I'll post as soon as i determine whether this was the problem.

Regards,

Joe

|||

OK.

That was it. I now have a connection only, not a new copy. I hope this helps anyone else as new as me...

Regards,

Joe

PS: do not know how to mark a question as answered...

sqlsql

copy from one table to the other using stored procedure

Hi there,
How to the scripts for copying data in table A to table B using stored procedure?
ThanxINSERT INTO Table_A
SELECT * FROM Table_B|||Hi there,

I got this one prob.............i have to copy from data which consist in the VIEWS section in SQL Server Enterprise Manager.
This views section already contain the script which limit the user to few databases and i need to copy watever display in the view section to a new table in other database.
Thanx|||Hi there...

Maybe this will help:

Select * into Other_db..newTable from Source_db..View

Other_db = the database where the new table should be created
newTable = the name of the new table
Source_db = is the database where you query the view
View = the name of the View

I hope this helps.

Greetz,
Marc,
:)|||thanx

copy file in stored procedure

All I want to do is copy a text file and add a 1 after the name in a stored procedure that already cretes this file.

CREATE PROCEDURE [dbo].[sp_export_req] AS
DECLARE @.trenutniRed varchar(30),
@.tableRow1 varchar(3000),
@.sql varchar(4000),
@.sql1 varchar(4000),
@.firstColumnName varchar(30),
@.HTMLfile varchar(1000),
@.columnNumber varchar(30),
@.fs int,
@.ole int,
@.file int
declare @.body as nvarchar(225)
CREATE TABLE #temptableHTML (code nvarchar(225))
select @.HTMLfile = 'c:\test_new.txt'

This is the start of the procedure that creates the test_new.txt file. I want to copy the test_new.txt file and name it test_new1.txt. Any help is appreciated. thank you

you could manipulate file name with string functions and build command for xp_cmdshell to copy file XXX.txt in XXX1.txt... or not?sqlsql

Copy db with dtswizard doesnt export triggers and stored procs

I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me.

TIA

Thomas

The SQL Server team have created a tool to move your database to a hosting server, try the link below for details. Post again if you still need help.

http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

|||

If you know IP or dns name of SQL server on your Host and you have user name and password to connect to your database I would recommend to script database to Query window next switch connection for this window to you host SQL server and just run it.

I did it for my web host SQL server and it worked perfectly.

If you can connect to your web SQL server from management studio it will pay in the future because you can modify you database using Microsoft tools which is probably better than any asp.net page.

Thanks

|||Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(|||

thomasabcd:

Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(

If you can see the stored procs and triggers just right click copy all and paste in your Management Studio. Hope this helps.

|||

Isn't there a way to copy the entire db with tables, rows, stored procedures and triggers? It seems cumbersome to e.g. go to every table, open triggers, script each trigger instead of just doing a bulk copy of the entire DB.

tia

|||

If you have access to your MDF and LDF you can use the code in thread below just put the files in the data sub folder in Microsoft SQL Server folder in programs and modify the code below for your database and box. Hope this helps.

http://forums.asp.net/thread/977493.aspx

|||

OR you could open up your query analyser ,set the output to text and then run this:

declare @.objNamesysnamedeclare objCurcursor for select [name]fromsysobjectswhere xtypein ('P','TR')OPEN objCurwhile 1=1begin-- Fetch datafetch objCurinto @.objNameif@.@.fetch_status <> 0break;execsp_helptext @.objNameENDCLOSE objCurDEALLOCATE objCur

The output then contains all the stored procs xtype = P and triggers xtype='TR'.Each objects script will be separated by the text, but I'm sure you can deal with that.

|||

Sounds like you have very limited permission on the hosted database. The best bet is to do just copy and paste all the stored proc and views.

If the data is important, i would pay your hoster for a backup or mdf of your database.

Most hosters nowaday offers free database backup.

Copy DB tables and stored procedures to a new DB with different name

Hello all!

What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB?

So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?

The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!

Any help would be greatly appreciated!!!

Hello,

1) Create empty dbs

db2,3,4

2) Right click on db1 and select Generate SQL script

3) Generate the script for tables,sp,views....

4) Open your query analyzer

Now, you have to do the following for each db

Use db2
-paste the sql script you did generate in step 3

Run it

Repeat step 4 for all your db.

Hope that helps
Regards

|||

That is easy use the Backup and Restore wizard use the restore from device option it will ask you for a new name just add the name and you will be done in a few minutes. Known issues orphaned permissions just delete the restore and create a new one. If you have more questions post again. Hope this helps.

|||

Hello

Is there another way so I can copy tables and procedures to a new DB , without any manual thing "by coding??"

Thank you

sqlsql

Copy Database Wizard

Hello, I am trying to copy my database tables and stored procedures to my local machine. I keep gettin gthis error
CDW Error Message

X "Your SQL Server Service is running under the local system account. You need to change you SQL Server Sevice account to have the rights to copy files over the network."

What do I do to correct this?

Thanks,
EricAre you working in a controlled environment ??
Active Directory and so ??
If that is so, you need to be system admin in the network to be able to copy those files ...|||The local System account only has permissions to run proggys on the local machine, to do any networkable actions the SQL Server needs to be run under an account that is able to have permissions added to it.

What You should do when createing or installing a SQL Server is to create another account for it to run under. If you are working in a domain You would create a domain account that is a member of the domain users group, and has the add permissions to log on as a service. Then increase the permissions as needed. Now if you want to use SQL Mail you need to do the above so that you can add a mail profile to the server so that the user can send emails (SQLServer mail uses the MAPI system same as Outlook).

If you are in a workgroup, create a local user and add the log on as service permissions. then on the other machines that it needs to talk to you would add the user.|||Is it at all possible to get instructions on how this can happen. Even if I have to uninstall Sql server and re-install it. I need to copy the Database to my local machine.|||Are you just trying to copy it once, or do you want this to be a regular job.

Copy database using stored procedure ?

We are using SQL Server 2005.
From my VB.NET program, I would like to call a SQL Server Stored Procedure
to copy the database to another database.
I can do that with Backup (using BACKUP DATABASE T-SQL command), but is
there a COPY DATABASE command ?
Thank you.
Using BACKUP/RESTORE is a good and reliable option. There is no COPY
DATABASE command in T-SQL.
Another option to explore is using SMO (since you are using .NET). Take a
look at the links below:
[url]http://davidhayden.com/blog/dave/archive/2006/11/09/CopyDatabaseSchemaDataSQLServerManagementObjects.a spx[/url]
[url]http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.transferdatabasetask .transferdatabasetask.aspx[/url]
HTH,
Plamen Ratchev
E-Mail: Plamen@.Ratchev.com
|||fniles wrote:
> We are using SQL Server 2005.
> From my VB.NET program, I would like to call a SQL Server Stored Procedure
> to copy the database to another database.
> I can do that with Backup (using BACKUP DATABASE T-SQL command), but is
> there a COPY DATABASE command ?
> Thank you.
>
>
not heard of such command but you can still simulate COPY with
ATTACH/DETACH functions...
http://support.microsoft.com/kb/224071/
HTH,
Petar Atanasov
http://a-wake.net

Copy database using stored procedure ?

We are using SQL Server 2005.
From my VB.NET program, I would like to call a SQL Server Stored Procedure
to copy the database to another database.
I can do that with Backup (using BACKUP DATABASE T-SQL command), but is
there a COPY DATABASE command ?
Thank you.Using BACKUP/RESTORE is a good and reliable option. There is no COPY
DATABASE command in T-SQL.
Another option to explore is using SMO (since you are using .NET). Take a
look at the links below:
http://davidhayden.com/blog/dave/archive/2006/11/09/CopyDatabaseSchemaDataSQLServerManagementObjects.aspx
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.transferdatabasetask.transferdatabasetask.aspx
HTH,
Plamen Ratchev
E-Mail: Plamen@.Ratchev.com|||fniles wrote:
> We are using SQL Server 2005.
> From my VB.NET program, I would like to call a SQL Server Stored Procedure
> to copy the database to another database.
> I can do that with Backup (using BACKUP DATABASE T-SQL command), but is
> there a COPY DATABASE command ?
> Thank you.
>
>
not heard of such command but you can still simulate COPY with
ATTACH/DETACH functions...
http://support.microsoft.com/kb/224071/
HTH,
Petar Atanasov
http://a-wake.net

Copy database using stored procedure ?

We are using SQL Server 2005.
From my VB.NET program, I would like to call a SQL Server Stored Procedure
to copy the database to another database.
I can do that with Backup (using BACKUP DATABASE T-SQL command), but is
there a COPY DATABASE command ?
Thank you.Using BACKUP/RESTORE is a good and reliable option. There is no COPY
DATABASE command in T-SQL.
Another option to explore is using SMO (since you are using .NET). Take a
look at the links below:
http://davidhayden.com/blog/dave/ar...entObjects.aspx
http://msdn2.microsoft.com/en-us/li...tabasetask.aspx
HTH,
Plamen Ratchev
E-Mail: Plamen@.Ratchev.com|||fniles wrote:
> We are using SQL Server 2005.
> From my VB.NET program, I would like to call a SQL Server Stored Procedure
> to copy the database to another database.
> I can do that with Backup (using BACKUP DATABASE T-SQL command), but is
> there a COPY DATABASE command ?
> Thank you.
>
>
not heard of such command but you can still simulate COPY with
ATTACH/DETACH functions...
http://support.microsoft.com/kb/224071/
HTH,
Petar Atanasov
http://a-wake.net

Copy database to same server but with a different name

I have a production database named "PRODUCTION_DATABASE" on "SERVER1". I would like to copy this database (and all its views, stored procedures, etc.) to the same server but under a different database name (i.e., "TEST_DATABASE" on "SERVER1"). This copy will be used for software development testing purposes.

What is the best way to do this?

P.S. I have SQL Server 2000 Enterprise Edition.You can use DTS to copy the database, or you can use a backup file from the production database and restore it to the test database. to use DTS, right click on the database, select Export, then when given the chance, Export Objects. When selecting a destination database, select New and you will be able to create the new database.|||douglas.reilly,

Your DTS suggestion worked perfectly! Thanks :)

copy database easiest way!

Hi I have a database with tables, views, stored procedures and want to put a
copy of it on another server. Just wondering an easy way to do this? I have
Enterprise manager, SQL query analyzer as well.
Paul G
Software engineer.
I prefer backup and restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> Hi I have a database with tables, views, stored procedures and want to put a
> copy of it on another server. Just wondering an easy way to do this? I have
> Enterprise manager, SQL query analyzer as well.
> --
> Paul G
> Software engineer.
|||ok can I do this from Enterprise manager on the source machine?
thanks.
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> I prefer backup and restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>
|||Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
on that (which you can do remotely in EM , connected to the target machine..).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...[vbcol=seagreen]
> ok can I do this from Enterprise manager on the source machine?
> thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
|||Hi thanks for the additional information. Just had a question on the copy, I
found the back and restore as part of the tools of EM. I could not find the
copy, can you copy the dbase just like a standard file from explorer?
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
> on that (which you can do remotely in EM , connected to the target machine..).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>
|||Only if you detach it from the SQL Server or stop the SQL Server service
first...detach/attach is preferred
look up sp_detach_db in Books Online
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
|||Yes, a regular file copy operation (explorer)...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the additional information. Just had a question on the copy, I
> found the back and restore as part of the tools of EM. I could not find the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
|||Hi Paul,
After the backup, Just copy the .BAK file to destination server. After that
restore the database in destination server using
Enterprise Manager. Otherwise use the RESTORE DATABASE from Query analyzer.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
|||Here's a link that might help you:
http://support.microsoft.com/default...b;en-us;314546
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
copy, I
> found the back and restore as part of the tools of EM. I could not find
the[vbcol=seagreen]
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
machine. Then do the restore[vbcol=seagreen]
machine..).[vbcol=seagreen]
to put a[vbcol=seagreen]
this? I have[vbcol=seagreen]
|||Hi,
In this approach the source database will be unavailable until you attach
the database back. This approach is not recommended in
a production server.
Thanks
Hari
SQL Server MVP
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23jtPRLOfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Only if you detach it from the SQL Server or stop the SQL Server service
> first...detach/attach is preferred
> look up sp_detach_db in Books Online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>

copy database easiest way!

Hi I have a database with tables, views, stored procedures and want to put a
copy of it on another server. Just wondering an easy way to do this? I hav
e
Enterprise manager, SQL query analyzer as well.
--
Paul G
Software engineer.I prefer backup and restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> Hi I have a database with tables, views, stored procedures and want to put
a
> copy of it on another server. Just wondering an easy way to do this? I h
ave
> Enterprise manager, SQL query analyzer as well.
> --
> Paul G
> Software engineer.|||ok can I do this from Enterprise manager on the source machine?
thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> I prefer backup and restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>|||Do the backup on the source machine. Copy the backup file to the target mach
ine. Then do the restore
on that (which you can do remotely in EM , connected to the target machine..
).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...[vbcol=seagreen]
> ok can I do this from Enterprise manager on the source machine?
> thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>|||Hi thanks for the additional information. Just had a question on the copy,
I
found the back and restore as part of the tools of EM. I could not find the
copy, can you copy the dbase just like a standard file from explorer?
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:

> Do the backup on the source machine. Copy the backup file to the target ma
chine. Then do the restore
> on that (which you can do remotely in EM , connected to the target machine
.).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>|||Only if you detach it from the SQL Server or stop the SQL Server service
first...detach/attach is preferred
look up sp_detach_db in Books Online
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>|||Yes, a regular file copy operation (explorer)...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the additional information. Just had a question on the copy
, I
> found the back and restore as part of the tools of EM. I could not find t
he
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>|||Hi Paul,
After the backup, Just copy the .BAK file to destination server. After that
restore the database in destination server using
Enterprise Manager. Otherwise use the RESTORE DATABASE from Query analyzer.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>|||Here's a link that might help you:
http://support.microsoft.com/defaul...kb;en-us;314546
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
copy, I
> found the back and restore as part of the tools of EM. I could not find
the[vbcol=seagreen]
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>
machine. Then do the restore[vbcol=seagreen]
machine..).[vbcol=seagreen]
to put a[vbcol=seagreen]
this? I have[vbcol=seagreen]|||Hi,
In this approach the source database will be unavailable until you attach
the database back. This approach is not recommended in
a production server.
Thanks
Hari
SQL Server MVP
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23jtPRLOfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Only if you detach it from the SQL Server or stop the SQL Server service
> first...detach/attach is preferred
> look up sp_detach_db in Books Online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>

copy database easiest way!

Hi I have a database with tables, views, stored procedures and want to put a
copy of it on another server. Just wondering an easy way to do this? I have
Enterprise manager, SQL query analyzer as well.
--
Paul G
Software engineer.I prefer backup and restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> Hi I have a database with tables, views, stored procedures and want to put a
> copy of it on another server. Just wondering an easy way to do this? I have
> Enterprise manager, SQL query analyzer as well.
> --
> Paul G
> Software engineer.|||ok can I do this from Enterprise manager on the source machine?
thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> I prefer backup and restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > Hi I have a database with tables, views, stored procedures and want to put a
> > copy of it on another server. Just wondering an easy way to do this? I have
> > Enterprise manager, SQL query analyzer as well.
> > --
> > Paul G
> > Software engineer.
>|||Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
on that (which you can do remotely in EM , connected to the target machine..).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> ok can I do this from Enterprise manager on the source machine?
> thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> I prefer backup and restore.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> > Hi I have a database with tables, views, stored procedures and want to put a
>> > copy of it on another server. Just wondering an easy way to do this? I have
>> > Enterprise manager, SQL query analyzer as well.
>> > --
>> > Paul G
>> > Software engineer.
>>|||Hi thanks for the additional information. Just had a question on the copy, I
found the back and restore as part of the tools of EM. I could not find the
copy, can you copy the dbase just like a standard file from explorer?
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
> on that (which you can do remotely in EM , connected to the target machine..).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > ok can I do this from Enterprise manager on the source machine?
> > thanks.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> I prefer backup and restore.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> >> > Hi I have a database with tables, views, stored procedures and want to put a
> >> > copy of it on another server. Just wondering an easy way to do this? I have
> >> > Enterprise manager, SQL query analyzer as well.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
>|||Only if you detach it from the SQL Server or stop the SQL Server service
first...detach/attach is preferred
look up sp_detach_db in Books Online
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Yes, a regular file copy operation (explorer)...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the copy, I
> found the back and restore as part of the tools of EM. I could not find the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target machine. Then do the
>> restore
>> on that (which you can do remotely in EM , connected to the target machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want to put a
>> >> > copy of it on another server. Just wondering an easy way to do this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Hi Paul,
After the backup, Just copy the .BAK file to destination server. After that
restore the database in destination server using
Enterprise Manager. Otherwise use the RESTORE DATABASE from Query analyzer.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Here's a link that might help you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
copy, I
> found the back and restore as part of the tools of EM. I could not find
the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
> > Do the backup on the source machine. Copy the backup file to the target
machine. Then do the restore
> > on that (which you can do remotely in EM , connected to the target
machine..).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > ok can I do this from Enterprise manager on the source machine?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> I prefer backup and restore.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > >> > Hi I have a database with tables, views, stored procedures and want
to put a
> > >> > copy of it on another server. Just wondering an easy way to do
this? I have
> > >> > Enterprise manager, SQL query analyzer as well.
> > >> > --
> > >> > Paul G
> > >> > Software engineer.
> > >>
> > >>
> >
> >|||Hi,
In this approach the source database will be unavailable until you attach
the database back. This approach is not recommended in
a production server.
Thanks
Hari
SQL Server MVP
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23jtPRLOfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Only if you detach it from the SQL Server or stop the SQL Server service
> first...detach/attach is preferred
> look up sp_detach_db in Books Online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>> Hi thanks for the additional information. Just had a question on the
>> copy, I
>> found the back and restore as part of the tools of EM. I could not find
>> the
>> copy, can you copy the dbase just like a standard file from explorer?
>> --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>
>|||Hi thanks for all the replies. I created a sample database and then backed
it up. I then detatched it but could not find it to re-attatch it. I tried
to re-attatch the backup but it said this is not a correct file format. Also
when I actually copy it over do I use the database wizard?
--
Paul G
Software engineer.
"Ron Hinds" wrote:
> Here's a link that might help you:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> > Hi thanks for the additional information. Just had a question on the
> copy, I
> > found the back and restore as part of the tools of EM. I could not find
> the
> > copy, can you copy the dbase just like a standard file from explorer?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Do the backup on the source machine. Copy the backup file to the target
> machine. Then do the restore
> > > on that (which you can do remotely in EM , connected to the target
> machine..).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > > ok can I do this from Enterprise manager on the source machine?
> > > > thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> I prefer backup and restore.
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://www.solidqualitylearning.com/
> > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >>
> > > >>
> > > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > > >> > Hi I have a database with tables, views, stored procedures and want
> to put a
> > > >> > copy of it on another server. Just wondering an easy way to do
> this? I have
> > > >> > Enterprise manager, SQL query analyzer as well.
> > > >> > --
> > > >> > Paul G
> > > >> > Software engineer.
> > > >>
> > > >>
> > >
> > >
>
>|||I ended up creating an empty database at the desintation machine and then
using export data wizard from the source dbase (EM). Seemed to work ok.
--
Paul G
Software engineer.
"Paul" wrote:
> Hi thanks for all the replies. I created a sample database and then backed
> it up. I then detatched it but could not find it to re-attatch it. I tried
> to re-attatch the backup but it said this is not a correct file format. Also
> when I actually copy it over do I use the database wizard?
> --
> Paul G
> Software engineer.
>
> "Ron Hinds" wrote:
> > Here's a link that might help you:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> > > Hi thanks for the additional information. Just had a question on the
> > copy, I
> > > found the back and restore as part of the tools of EM. I could not find
> > the
> > > copy, can you copy the dbase just like a standard file from explorer?
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Do the backup on the source machine. Copy the backup file to the target
> > machine. Then do the restore
> > > > on that (which you can do remotely in EM , connected to the target
> > machine..).
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > > > ok can I do this from Enterprise manager on the source machine?
> > > > > thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > >> I prefer backup and restore.
> > > > >>
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> http://www.karaszi.com/sqlserver/default.asp
> > > > >> http://www.solidqualitylearning.com/
> > > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > > >>
> > > > >>
> > > > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > > > >> > Hi I have a database with tables, views, stored procedures and want
> > to put a
> > > > >> > copy of it on another server. Just wondering an easy way to do
> > this? I have
> > > > >> > Enterprise manager, SQL query analyzer as well.
> > > > >> > --
> > > > >> > Paul G
> > > > >> > Software engineer.
> > > > >>
> > > > >>
> > > >
> > > >
> >
> >
> >|||It seems like you've mixed things up....
When you backup the database, you get a file with the extention .BAK
(..actually it can have what ever extension you give it, but .BAK it the
"standard"). This .BAK file is just a normal file that you can copy as any
other file. If you copy this file to a location on your destination server,
you can restore this file on the destination server, and then you'll have an
exact copy of your database.
When you use sp_detach_db/sp_attach_db it's the actual files belonging to
the database you are working with (Database and logfiles). If you run
sp_detach_db, you'll have to copy the .mdf and .ldf file from the source
server to the destination server. When you've copied the files, you'll have
to run sp_attach_db BOTH on the source server (to get the database attach
again) and on the destination server (to get the database created).
Both the BACKUP/RESTORE and sp_detach_db/sp_attach_db syntaxes can be found
in Books On Line.
Regards
Steen
Paul wrote:
> I ended up creating an empty database at the desintation machine and
> then using export data wizard from the source dbase (EM). Seemed to
> work ok.
>> Hi thanks for all the replies. I created a sample database and then
>> backed it up. I then detatched it but could not find it to
>> re-attatch it. I tried to re-attatch the backup but it said this is
>> not a correct file format. Also when I actually copy it over do I
>> use the database wizard?
>> --
>> Paul G
>> Software engineer.
>>
>> "Ron Hinds" wrote:
>> Here's a link that might help you:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>> Hi thanks for the additional information. Just had a question on
>> the copy, I found the back and restore as part of the tools of EM.
>> I could not find the copy, can you copy the dbase just like a
>> standard file from explorer? --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the
>> target machine. Then do the restore on that (which you can do
>> remotely in EM , connected to the target machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> ok can I do this from Enterprise manager on the source machine?
>> thanks.
>> --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>>> I prefer backup and restore.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>>> Hi I have a database with tables, views, stored procedures and
>>> want to put a copy of it on another server. Just wondering an
>>> easy way to do this? I have Enterprise manager, SQL query
>>> analyzer as well. --
>>> Paul G
>>> Software engineer.|||thanks for the additional information.--
Paul G
Software engineer.
"Steen Persson (DK)" wrote:
> It seems like you've mixed things up....
> When you backup the database, you get a file with the extention .BAK
> (..actually it can have what ever extension you give it, but .BAK it the
> "standard"). This .BAK file is just a normal file that you can copy as any
> other file. If you copy this file to a location on your destination server,
> you can restore this file on the destination server, and then you'll have an
> exact copy of your database.
> When you use sp_detach_db/sp_attach_db it's the actual files belonging to
> the database you are working with (Database and logfiles). If you run
> sp_detach_db, you'll have to copy the .mdf and .ldf file from the source
> server to the destination server. When you've copied the files, you'll have
> to run sp_attach_db BOTH on the source server (to get the database attach
> again) and on the destination server (to get the database created).
> Both the BACKUP/RESTORE and sp_detach_db/sp_attach_db syntaxes can be found
> in Books On Line.
> Regards
> Steen
> Paul wrote:
> > I ended up creating an empty database at the desintation machine and
> > then using export data wizard from the source dbase (EM). Seemed to
> > work ok.
> >
> >> Hi thanks for all the replies. I created a sample database and then
> >> backed it up. I then detatched it but could not find it to
> >> re-attatch it. I tried to re-attatch the backup but it said this is
> >> not a correct file format. Also when I actually copy it over do I
> >> use the database wizard?
> >>
> >> --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Ron Hinds" wrote:
> >>
> >> Here's a link that might help you:
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> >> Hi thanks for the additional information. Just had a question on
> >> the copy, I found the back and restore as part of the tools of EM.
> >> I could not find the copy, can you copy the dbase just like a
> >> standard file from explorer? --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Do the backup on the source machine. Copy the backup file to the
> >> target machine. Then do the restore on that (which you can do
> >> remotely in EM , connected to the target machine..).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> >> ok can I do this from Enterprise manager on the source machine?
> >> thanks.
> >> --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >>> I prefer backup and restore.
> >>>
> >>> --
> >>> Tibor Karaszi, SQL Server MVP
> >>> http://www.karaszi.com/sqlserver/default.asp
> >>> http://www.solidqualitylearning.com/
> >>> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>>
> >>>
> >>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> >>> Hi I have a database with tables, views, stored procedures and
> >>> want to put a copy of it on another server. Just wondering an
> >>> easy way to do this? I have Enterprise manager, SQL query
> >>> analyzer as well. --
> >>> Paul G
> >>> Software engineer.
>
>

Copy data from SQL Server to SQL Mobile

Hello

I'm developing an application that need to create everyday information to be stored in a SQL Mobile Database.

The SQL Mobile database will have:

Last schema from tables (in SQL Server database);

Last information in the tables;

I don't want to copy all the tables from SQL Server database than this mecanism should give me the way to choose the tables.

I have been reading about the SqlCeReplication but I don't undestand what is InternetUrl property. The SQL Mobile database is created in the PC and after is copy to the the Mobile Device.
How can I avoid the InternetURL property?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

Olá Paulo,

The Merge Replication process is based on an agent running on a DLL on IIS, so that's why you need the InternetURL property.

You may also consider the tools from www.primeworks-mobile.com which avoid IIS and may be used over TCP/IP or ActiveSync (RAPI). BTW, these tools are developed very close from where you live...

If you prefer to follow this in Portuguese, try the microsoft.public.pt.pocketpc newsgroup

Alberto Silva
http://msmvps.com/AlbertoSilva

Copy Data from Access to SQL Express in Code?

I have tried the upsize wizard from access 2000 and access 2003 and get records transfer in 90% of the tables I have data stored an 89 meg MS access database (2003) on an XP machine. I can use, compact and copy etc in MS Access without problem.

With the upsize, I get No errors, just does not reliably move ALL data. I have run it multiple times and occasionally get some data in these tables, but never all data in a few tables and inconsistent.

========================================

So I have written code to copy the data from the access database and move it field by field into an Upsized database that was structure only, no data.

I have ONLY one Identity field (autonumber in MS access) that is the Key Relationship number to all other tables.

1 questions and one "bug" report need solutions to both.

Question: How can I copy the value from the MS access autonumber field to the identity field. (Some of the autonumber rows have been deleted so not always sequentiall) (I can do this moving data in code between to MS access databases that have autonumber fields)

=========================

I tied an elaborate write around to addNEW and then read the data in the newly created row. Reading the Identity field, I look up that number in the MS access database. Then I copy the ACCESS data to the Newly created table row in SQL Exress and update.

It works fine for about 30 record updates, then the Identify field stops giving me the correct sequence for the Identity field value. Makes it impossible to make sure all of my Access rows get copied. Tried transactions but not supported with the ADODB configuration, but doubt that would help.

Example data returned in the SQL Express recordset, when I read the Identify field back I get

1,2,3 etc 31, 33, 32 (out of sequence) then it (my code) can't figure out the out of sequence and end up with the next identity value at 41.

The only info I found was that it could be a cursorlocation problem, what do you recommend?

?Is there a way in code to change the identity field to string, then copy the data and change back to identity field again?

As you are just loading the data I would set the field in the SQL Express system to just an int then load the data back in. Once complete change the filed back to an identity field and start the numbers off on the next availible number.

|||

I tried that, but it will NOT let me set the identity field. It generates it.

It works with Access but NOT SQL EXPRESS, if I hit the ADDNEW and then set the identify field and update it errors.

|||

Hey TurboDoc,

You should be able to insert data into your Identity field by setting the IDENTITY_INSERT option. Check out SQL BOL for more information on that. I think that will be more straight forward than trying to create blank records in SQL and then matching up the Identity values.

Hope this helps,

Mike

copy data from 1 table to other in stored procedure in sql server

Hi there,

Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so.
Please help.
ThanxOriginally posted by michaelfg81
Hi there,

Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so.
Please help.
Thanx

if my code in sql query analyszer like below

insert into esipquery.dbo.tsip_wip
select assy_lot_no, location_id, traveler_type, ase_lot_no, device_type, process_id,
process_rev, sequence, in_qty, out_qty, status, check_in_date, check_in_time,
check_out_date, check_out_time, package_grp, cust_id, ex_lot, getdate() as create_time
from esipdata.dbo.vSIP_LOT_TRACK_WIP

and i get error sound like this:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_tSIP_WIP'. Cannot insert duplicate key in object 'tSIP_WIP'.
The statement has been terminated.

after i made a check on it, i found out that one of the primary key in table esipquery.dbo.tsip_wip
is not a primary key in table esipdata.dbo.vSIP_LOT_TRACK_WIP

How can i fix it?
Can anyone pls help me
Thanx|||Post the table structure, including the primary keys and indexes. You have a primary key defined on the table you are inserting into. You keep trying to insert a value into that table, which is alread there. This won't work.

copy all the sps

I wanna copy all the stored procedures from one database to another database
throug stored procedure,
is there any ways?
Thanks
Why would you want to do this thru a stored procedure? In any case you can
use SQL-DMO thru sp_OAcreate. Have a look in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"Roger" <naissani@.hotmail.com> wrote in message
news:OpDEVHZVGHA.1160@.TK2MSFTNGP09.phx.gbl...
>I wanna copy all the stored procedures from one database to another
>database throug stored procedure,
> is there any ways?
> Thanks
>

Copy a text file to a different server

I have a stored procedure that makes a call to the BCP and creates a
text file. I then need to copy this text file over to a different
server in the same domain. What is the best way to do it?
Also, I would like to know what would be the best way to copy this same
file over to another server that is not in the same domain.
Users running this stored procedure will have minimal rights they will
not have SA rights.
Any help or suggestion in this reagrd will be greatly appreciated.
Thanks
ShubHi
From Haidong "Alex" Ji
CREATE proc usp_CopyFiles @.SourceDirectory varchar(200),
@.DestinationDirectory varchar(200) as
--Created by Haidong "Alex" Ji 05/15/03
--Given source and destination folder, this procedure copies
--files
--Usage: exec dbo.usp_CopyFiles 'SourceFolder', 'DestinationFolder'
declare @.DOSCommand varchar(150)
set nocount on
--Check whether the user supply \ in the directory name
if not (right(@.SourceDirectory , 1) = '\')
set @.SourceDirectory = @.SourceDirectory + '\'
--Check whether the user supply \ in the directory name
if not (right(@.DestinationDirectory, 1) = '\')
set @.DestinationDirectory = @.DestinationDirectory + '\'
--The following DOS command will copy files. The /D switch only copies new
files.
--If a file exists in the destination folder, it will not try to copy it
again.
set @.DOSCommand = 'xcopy /D ' + '"' + @.SourceDirectory + '*.*' + '"' + ' ' +
'"'
+ @.DestinationDirectory + '"'
print @.DOSCommand
exec master..xp_cmdshell @.DOSCommand
GO
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>|||Thanks for your response but when I run this i get the following
message...
xcopy /D "C:\source\*.*" "C:\dest\"
output
File not found - *.*
0 File(s) copied
NULL
If I run this same command from the dos promt it works fine. Any ideas?|||Hi,
Is the directory C:\Source and C:\dest available in the SQL Server machine?
Thanks
Hari
SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1126529298.347453.164080@.f14g2000cwb.googlegroups.com...
> Thanks for your response but when I run this i get the following
> message...
> xcopy /D "C:\source\*.*" "C:\dest\"
> output
> File not found - *.*
> 0 File(s) copied
> NULL
> If I run this same command from the dos promt it works fine. Any ideas?
>|||No actually I was just trying this on my local computer. I have not yet
tried the real test. In my real example C:\source will and C:\dest will
be in two different servers. In some cases both the servers will be in
the same domain and in others it may not be in the same domain.
Thanks|||Below is a file copy sp I created for a project a long time ago...The thing
I wanted to mention is xcopy might fail, the file may not get copied...
There is an undoc-d I think sp ms_file_exists, that I called after the copy
to see if the copy was successfulll..
--add p_copy_file
--Aug 25, 19999 13:30 kws added error message for failed ms_exists_File call
/*
declare @.ret_status int
exec @.ret_Status =p_copy_file 'C:\party.sql', 'c:\backup\'
--exec @.ret_status = dbo.sp_MSexists_file 'c:\party2.sql', 'party2.sql'
select @.ret_status
--DOES NOT SUPPORT SPACES OR SPECIAL CHARACTERS IN DIRECTORIES< FILENAMES
*/
drop proc p_copy_file
go
create proc p_copy_file
(
@.inputfile udt_entire_filename = null
,@.outputfile udt_entire_filename = null
,@.debug int = 0
)
as
set nocount on
Declare @.trancount int
,@.msg varchar(4000)
,@.msg2 varchar(1000)
,@.me varchar(128)
,@.error int
,@.rowcount int
,@.filecopy_cmd nvarchar(1000)
,@.ret_status int
,@.filename udt_filename
set nocount on
select @.me = 'p_copy_file'
select @.debug = @.debug & isnull(dbg_prmtr_value,0) from debug_parameter
where dbg_prmtr_name = @.me
select @.trancount = @.@.trancount
/*
if @.debug &2 = 2 -- display input parameters
begin
-- select @.msg = 'Input parameters for ' + @.me + ' are:' + char(10) +
-- '@.param1:'-- + @.param1
select @.msg
end
*/
if @.inputfile is null or @.outputfile is null
begin
select @.msg = 'Usage: Exec ' + @.me + ' @.inputfile, @.outputfile' + char(10)
+
' All parameters are required.'
raiserror (@.msg,16,1)
return -500
end
select @.filecopy_cmd = 'copy ' + @.inputfile + ' ' + @.outputfile
if @.debug & 3 = 3 select @.filecopy_cmd
EXEC @.ret_status = master..xp_cmdshell @.filecopy_cmd, NO_OUTPUT
select @.error = @.@.error
IF @.ret_status <> 0 OR @.@.ERROR <> 0
BEGIN
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, null)
exec master..xp_logevent 90108, @.msg, Error
return (-1)
END
--maybe use this if needed --0 = not found 1 = found
exec @.ret_status = p_parse_filename @.full_path = @.outputfile, @.filename =@.filename OUTPUT
if @.ret_status != 0
begin
select @.msg = formatmessage(90107, @.me, 'p_parse_filename', @.ret_status,
null)
exec master..xp_logevent 90107, @.msg, Error
return -500
end
exec @.ret_status = dbo.sp_MSexists_file @.full_path = @.outputfile, @.filename
= @.filename
if @.ret_status != 1
begin
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, 'File was not copied(it does not exist).')
exec master..xp_logevent 90108, @.msg, Error
return (-1)
end
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>

Copy a text file to a different server

I have a stored procedure that makes a call to the BCP and creates a
text file. I then need to copy this text file over to a different
server in the same domain. What is the best way to do it?
Also, I would like to know what would be the best way to copy this same
file over to another server that is not in the same domain.
Users running this stored procedure will have minimal rights they will
not have SA rights.
Any help or suggestion in this reagrd will be greatly appreciated.
Thanks
Shub
Hi
From Haidong "Alex" Ji
CREATE proc usp_CopyFiles @.SourceDirectory varchar(200),
@.DestinationDirectory varchar(200) as
--Created by Haidong "Alex" Ji 05/15/03
--Given source and destination folder, this procedure copies
--files
--Usage: exec dbo.usp_CopyFiles 'SourceFolder', 'DestinationFolder'
declare @.DOSCommand varchar(150)
set nocount on
--Check whether the user supply \ in the directory name
if not (right(@.SourceDirectory , 1) = '\')
set @.SourceDirectory = @.SourceDirectory + '\'
--Check whether the user supply \ in the directory name
if not (right(@.DestinationDirectory, 1) = '\')
set @.DestinationDirectory = @.DestinationDirectory + '\'
--The following DOS command will copy files. The /D switch only copies new
files.
--If a file exists in the destination folder, it will not try to copy it
again.
set @.DOSCommand = 'xcopy /D ' + '"' + @.SourceDirectory + '*.*' + '"' + ' ' +
'"'
+ @.DestinationDirectory + '"'
print @.DOSCommand
exec master..xp_cmdshell @.DOSCommand
GO
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>
|||Thanks for your response but when I run this i get the following
message...
xcopy /D "C:\source\*.*" "C:\dest\"
output
File not found - *.*
0 File(s) copied
NULL
If I run this same command from the dos promt it works fine. Any ideas?
|||Hi,
Is the directory C:\Source and C:\dest available in the SQL Server machine?
Thanks
Hari
SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1126529298.347453.164080@.f14g2000cwb.googlegr oups.com...
> Thanks for your response but when I run this i get the following
> message...
> xcopy /D "C:\source\*.*" "C:\dest\"
> output
> File not found - *.*
> 0 File(s) copied
> NULL
> If I run this same command from the dos promt it works fine. Any ideas?
>
|||No actually I was just trying this on my local computer. I have not yet
tried the real test. In my real example C:\source will and C:\dest will
be in two different servers. In some cases both the servers will be in
the same domain and in others it may not be in the same domain.
Thanks
|||Below is a file copy sp I created for a project a long time ago...The thing
I wanted to mention is xcopy might fail, the file may not get copied...
There is an undoc-d I think sp ms_file_exists, that I called after the copy
to see if the copy was successfulll..
--add p_copy_file
--Aug 25, 19999 13:30 kws added error message for failed ms_exists_File call
/*
declare @.ret_status int
exec @.ret_Status =p_copy_file 'C:\party.sql', 'c:\backup\'
--exec @.ret_status = dbo.sp_MSexists_file 'c:\party2.sql', 'party2.sql'
select @.ret_status
--DOES NOT SUPPORT SPACES OR SPECIAL CHARACTERS IN DIRECTORIES< FILENAMES
*/
drop proc p_copy_file
go
create proc p_copy_file
(
@.inputfile udt_entire_filename = null
,@.outputfile udt_entire_filename = null
,@.debug int = 0
)
as
set nocount on
Declare @.trancount int
,@.msg varchar(4000)
,@.msg2 varchar(1000)
,@.me varchar(128)
,@.error int
,@.rowcount int
,@.filecopy_cmd nvarchar(1000)
,@.ret_status int
,@.filename udt_filename
set nocount on
select @.me = 'p_copy_file'
select @.debug = @.debug & isnull(dbg_prmtr_value,0) from debug_parameter
where dbg_prmtr_name = @.me
select @.trancount = @.@.trancount
/*
if @.debug &2 = 2 -- display input parameters
begin
-- select @.msg = 'Input parameters for ' + @.me + ' are:' + char(10) +
-- '@.param1:'-- + @.param1
select @.msg
end
*/
if @.inputfile is null or @.outputfile is null
begin
select @.msg = 'Usage: Exec ' + @.me + ' @.inputfile, @.outputfile' + char(10)
+
' All parameters are required.'
raiserror (@.msg,16,1)
return -500
end
select @.filecopy_cmd = 'copy ' + @.inputfile + ' ' + @.outputfile
if @.debug & 3 = 3 select @.filecopy_cmd
EXEC @.ret_status = master..xp_cmdshell @.filecopy_cmd, NO_OUTPUT
select @.error = @.@.error
IF @.ret_status <> 0 OR @.@.ERROR <> 0
BEGIN
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, null)
exec master..xp_logevent 90108, @.msg, Error
return (-1)
END
--maybe use this if needed --0 = not found 1 = found
exec @.ret_status = p_parse_filename @.full_path = @.outputfile, @.filename =
@.filename OUTPUT
if @.ret_status != 0
begin
select @.msg = formatmessage(90107, @.me, 'p_parse_filename', @.ret_status,
null)
exec master..xp_logevent 90107, @.msg, Error
return -500
end
exec @.ret_status = dbo.sp_MSexists_file @.full_path = @.outputfile, @.filename
= @.filename
if @.ret_status != 1
begin
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, 'File was not copied(it does not exist).')
exec master..xp_logevent 90108, @.msg, Error
return (-1)
end
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegr oups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>

Copy a text file to a different server

I have a stored procedure that makes a call to the BCP and creates a
text file. I then need to copy this text file over to a different
server in the same domain. What is the best way to do it?
Also, I would like to know what would be the best way to copy this same
file over to another server that is not in the same domain.
Users running this stored procedure will have minimal rights they will
not have SA rights.
Any help or suggestion in this reagrd will be greatly appreciated.
Thanks
ShubHi
From Haidong "Alex" Ji
CREATE proc usp_CopyFiles @.SourceDirectory varchar(200),
@.DestinationDirectory varchar(200) as
--Created by Haidong "Alex" Ji 05/15/03
--Given source and destination folder, this procedure copies
--files
--Usage: exec dbo.usp_CopyFiles 'SourceFolder', 'DestinationFolder'
declare @.DOSCommand varchar(150)
set nocount on
--Check whether the user supply \ in the directory name
if not (right(@.SourceDirectory , 1) = '')
set @.SourceDirectory = @.SourceDirectory + ''
--Check whether the user supply \ in the directory name
if not (right(@.DestinationDirectory, 1) = '')
set @.DestinationDirectory = @.DestinationDirectory + ''
--The following DOS command will copy files. The /D switch only copies new
files.
--If a file exists in the destination folder, it will not try to copy it
again.
set @.DOSCommand = 'xcopy /D ' + '"' + @.SourceDirectory + '*.*' + '"' + ' ' +
'"'
+ @.DestinationDirectory + '"'
print @.DOSCommand
exec master..xp_cmdshell @.DOSCommand
GO
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>|||Thanks for your response but when I run this i get the following
message...
xcopy /D "C:\source\*.*" "C:\dest\"
output
File not found - *.*
0 File(s) copied
NULL
If I run this same command from the dos promt it works fine. Any ideas?|||Hi,
Is the directory C:\Source and C:\dest available in the SQL Server machine?
Thanks
Hari
SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1126529298.347453.164080@.f14g2000cwb.googlegroups.com...
> Thanks for your response but when I run this i get the following
> message...
> xcopy /D "C:\source\*.*" "C:\dest\"
> output
> File not found - *.*
> 0 File(s) copied
> NULL
> If I run this same command from the dos promt it works fine. Any ideas?
>|||No actually I was just trying this on my local computer. I have not yet
tried the real test. In my real example C:\source will and C:\dest will
be in two different servers. In some cases both the servers will be in
the same domain and in others it may not be in the same domain.
Thanks|||Below is a file copy sp I created for a project a long time ago...The thing
I wanted to mention is xcopy might fail, the file may not get copied...
There is an undoc-d I think sp ms_file_exists, that I called after the copy
to see if the copy was successfulll..
--add p_copy_file
--Aug 25, 19999 13:30 kws added error message for failed ms_exists_File call
/*
declare @.ret_status int
exec @.ret_Status =p_copy_file 'C:\party.sql', 'c:\backup'
--exec @.ret_status = dbo.sp_MSexists_file 'c:\party2.sql', 'party2.sql'
select @.ret_status
--DOES NOT SUPPORT SPACES OR SPECIAL CHARACTERS IN DIRECTORIES< FILENAMES
*/
drop proc p_copy_file
go
create proc p_copy_file
(
@.inputfile udt_entire_filename = null
,@.outputfile udt_entire_filename = null
,@.debug int = 0
)
as
set nocount on
Declare @.trancount int
,@.msg varchar(4000)
,@.msg2 varchar(1000)
,@.me varchar(128)
,@.error int
,@.rowcount int
,@.filecopy_cmd nvarchar(1000)
,@.ret_status int
,@.filename udt_filename
set nocount on
select @.me = 'p_copy_file'
select @.debug = @.debug & isnull(dbg_prmtr_value,0) from debug_parameter
where dbg_prmtr_name = @.me
select @.trancount = @.@.trancount
/*
if @.debug &2 = 2 -- display input parameters
begin
-- select @.msg = 'Input parameters for ' + @.me + ' are:' + char(10) +
-- '@.param1:'-- + @.param1
select @.msg
end
*/
if @.inputfile is null or @.outputfile is null
begin
select @.msg = 'Usage: Exec ' + @.me + ' @.inputfile, @.outputfile' + char(10)
+
' All parameters are required.'
raiserror (@.msg,16,1)
return -500
end
select @.filecopy_cmd = 'copy ' + @.inputfile + ' ' + @.outputfile
if @.debug & 3 = 3 select @.filecopy_cmd
EXEC @.ret_status = master..xp_cmdshell @.filecopy_cmd, NO_OUTPUT
select @.error = @.@.error
IF @.ret_status <> 0 OR @.@.ERROR <> 0
BEGIN
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, null)
exec master..xp_logevent 90108, @.msg, Error
return (-1)
END
--maybe use this if needed --0 = not found 1 = found
exec @.ret_status = p_parse_filename @.full_path = @.outputfile, @.filename =
@.filename OUTPUT
if @.ret_status != 0
begin
select @.msg = formatmessage(90107, @.me, 'p_parse_filename', @.ret_status,
null)
exec master..xp_logevent 90107, @.msg, Error
return -500
end
exec @.ret_status = dbo.sp_MSexists_file @.full_path = @.outputfile, @.filename
= @.filename
if @.ret_status != 1
begin
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, 'File was not copied(it does not exist).')
exec master..xp_logevent 90108, @.msg, Error
return (-1)
end
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>