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 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 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 tables with script!

Hello!

I cant figure out how to copy data from tables in my default database to my new automate-generated database.

I can generate Store Procedures, I can generate the Tables but are there anyone one who knows how to copy the data from tables and put it into my new automate-generated shell. The automate-generate funktion i have built just creating a shell/empty database. How to fill it with a sql-script or similar?

Anyone have any tip?

Regards

If you mean you want to script out the data into the same script with the DDL for the tables try the Database Publishing Wizard:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

|||

It worked perfectly!

Thanks!

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
>

Coping Stored Procedures

I have and ASP.NET and VB.NET application that needs to copy selected stored procedures from one SQL Server to another SQL Server. The scenerio is that the user will select a source server from a drop down list then the source database from another dropdown listbox which then populates a listbox with the stored procedures in the database. They can select multiple stored procedures from the listbox.

Once selected, they select a target SQL Server from a dropdownlist and then a target database from another dropwdown list. I have a command button that they need to do the actual move but I am not sure how to get the selected source stored procedures copied to the target database. Any ideas would be greatly appreciated.Do you want copy store procedures at run time?
you may try sp_helptext 'sp-Name' . it ruturns the source code.
Then connect to second server and run the code generated from sp_helptext.|||Loads of ways DTS, sp_helptext to name but two methods.|||How can I use DTS to do this?|||Remember stored procs are nothing more than text in a database table. Literally.

Converting varchar to int (or numeric)

In one of my stored procedures, a varchar is input that is assumed to be an int, and I need to validate that it is an int before I CAST it as an int. I'm currently using ISNUMERIC() to eliminate non-numeric values, but how do I recover gracefully if values such as '7.5e3', '$334', or '45.9943' sneak through? They all pass the ISNUMERIC() test, but cause errors when being cast to int. Similar problems if casting to numeric, etc.You can use the case statement - for example:

declare @.test varchar(20)
select @.test='123.3'
select case when charindex('.',@.test) > 0 then cast(@.test as decimal(10,2))
when charindex('e',@.test) > 0 then cast...
else cast(@.test as int) end|||Thanks. That should work. On retrospect, it's obvious enough I should have come up with that myself!

Converting Stored Procedures back to MS Access Queries

I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.

We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?

Any advice would be greatly appreciated.

Many thanks
SmillaYou can't convert any but the simplest sprocs into Access queries. Sprocs can be mult-step, while Access queries are simple statements like SQL Views. You would have to write VB code to simulate the sprocs, and there is no utility I know of that will do this for you.

Converting SQL2000 database to SQL2005

Are there any stored procedures that have to be run on a database when
restoring from SQL2000 to SQL 2005? If so, which ones?
Many Thanks!
No. But you should probably run the Upgrade Advisor (found on the SQL 2005
distribution media) before moving the database over, in order to ensure that
at least most of your code is compatible (the Advisor won't find
everything).

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve" <stevex2@.directcon.net> wrote in message
news:CD8A547F-7754-4FBB-9B6E-29C3B4AECB5C@.microsoft.com...
> Are there any stored procedures that have to be run on a database when
> restoring from SQL2000 to SQL 2005? If so, which ones?
> Many Thanks!
|||And also up date compatibility mode when you are ready for it (see sp_dbcmptlevel).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:53A849D2-97E5-4ACE-A1DB-5EFC798026AF@.microsoft.com...
> No. But you should probably run the Upgrade Advisor (found on the SQL 2005 distribution media)
> before moving the database over, in order to ensure that at least most of your code is compatible
> (the Advisor won't find everything).
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Steve" <stevex2@.directcon.net> wrote in message
> news:CD8A547F-7754-4FBB-9B6E-29C3B4AECB5C@.microsoft.com...
>

Converting SQL2000 database to SQL2005

Are there any stored procedures that have to be run on a database when
restoring from SQL2000 to SQL 2005? If so, which ones?
Many Thanks!No. But you should probably run the Upgrade Advisor (found on the SQL 2005
distribution media) before moving the database over, in order to ensure that
at least most of your code is compatible (the Advisor won't find
everything).
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve" <stevex2@.directcon.net> wrote in message
news:CD8A547F-7754-4FBB-9B6E-29C3B4AECB5C@.microsoft.com...
> Are there any stored procedures that have to be run on a database when
> restoring from SQL2000 to SQL 2005? If so, which ones?
> Many Thanks!|||And also up date compatibility mode when you are ready for it (see sp_dbcmptlevel).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:53A849D2-97E5-4ACE-A1DB-5EFC798026AF@.microsoft.com...
> No. But you should probably run the Upgrade Advisor (found on the SQL 2005 distribution media)
> before moving the database over, in order to ensure that at least most of your code is compatible
> (the Advisor won't find everything).
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Steve" <stevex2@.directcon.net> wrote in message
> news:CD8A547F-7754-4FBB-9B6E-29C3B4AECB5C@.microsoft.com...
>> Are there any stored procedures that have to be run on a database when
>> restoring from SQL2000 to SQL 2005? If so, which ones?
>> Many Thanks!
>

Converting SQL2000 database to SQL2005

Are there any stored procedures that have to be run on a database when
restoring from SQL2000 to SQL 2005? If so, which ones?
Many Thanks!No. But you should probably run the Upgrade Advisor (found on the SQL 2005
distribution media) before moving the database over, in order to ensure that
at least most of your code is compatible (the Advisor won't find
everything).
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve" <stevex2@.directcon.net> wrote in message
news:CD8A547F-7754-4FBB-9B6E-29C3B4AECB5C@.microsoft.com...
> Are there any stored procedures that have to be run on a database when
> restoring from SQL2000 to SQL 2005? If so, which ones?
> Many Thanks!|||And also up date compatibility mode when you are ready for it (see sp_dbcmpt
level).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:53A849D2-97E5-4ACE-A1DB-5EFC798026AF@.microsoft.com...
> No. But you should probably run the Upgrade Advisor (found on the SQL 200
5 distribution media)
> before moving the database over, in order to ensure that at least most of
your code is compatible
> (the Advisor won't find everything).
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Steve" <stevex2@.directcon.net> wrote in message
> news:CD8A547F-7754-4FBB-9B6E-29C3B4AECB5C@.microsoft.com...
>

Converting SP to SQL Server 2005

I have a SQL Server 2000 database that is approximately 100 GB in size with
200 stored procedures and 30 views.
I would like to convert this SQL Server 2000 database to a SQL Server 2005
database.
Have are the differences that I will have converting this database with
triggers, indexes, stored procedures, and views.
Thank You,You can run the SQL 2005 upgrade advisor against your database to identify
items you need to address before/after the conversion. In any case, it's a
good idea to test before upgrading production databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:CDB6A87F-2BA7-4292-A7E8-3EA987E52006@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 100 GB in size
> with
> 200 stored procedures and 30 views.
> I would like to convert this SQL Server 2000 database to a SQL Server 2005
> database.
> Have are the differences that I will have converting this database with
> triggers, indexes, stored procedures, and views.
> Thank You,

Converting Profiles to Stored Procedures

New to SQL Server. I am considering converting my Profiles that I run to
Stored Procedures. I am listening for long running querys and some other
things. If I use just the Profiler, I can look at the all the databases in
the server at the same time. However, if I convert the profile to a stored
procedure, the scope of that profile is ONLY the database the stored
procedure is in. It appears I would need to place the stored procedure into
each database on that server!
Can I run a stored procdure at the server level?Not sure what you mean by profiles.
There is a way to create stored procedures once "at the server level," have
them interact with user objects, and have the proc's code resolved in the
local database; but it's undocumented and unsupported with all that implies.
You create a special procedure (sp_, in the master database), and mark it as
a system object with the undocumented sp_MS_MarkSystemObject procedure.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C99ADF63-1E76-402B-AA0A-46AF69211769@.microsoft.com...
> New to SQL Server. I am considering converting my Profiles that I run to
> Stored Procedures. I am listening for long running querys and some other
> things. If I use just the Profiler, I can look at the all the databases
> in
> the server at the same time. However, if I convert the profile to a
> stored
> procedure, the scope of that profile is ONLY the database the stored
> procedure is in. It appears I would need to place the stored procedure
> into
> each database on that server!
> Can I run a stored procdure at the server level?|||Oh, sorry for being unclear. The Tool Profiler is what I mean. Instead of
running my traces in the Profiler application, I am converting them to store
d
procedures. That way I do not need to open Profiler at all and can place th
e
scan into a Job controled By the SQL Server Agent.
"Itzik Ben-Gan" wrote:

> Not sure what you mean by profiles.
> There is a way to create stored procedures once "at the server level," hav
e
> them interact with user objects, and have the proc's code resolved in the
> local database; but it's undocumented and unsupported with all that implie
s.
> You create a special procedure (sp_, in the master database), and mark it
as
> a system object with the undocumented sp_MS_MarkSystemObject procedure.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:C99ADF63-1E76-402B-AA0A-46AF69211769@.microsoft.com...
>
>|||In that case, you only need to create them as the fully supported special
procedures as the trace related procedures themselves are special
procedures, and they interact with system objects.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C576AF29-CA75-45B4-A3CF-BB730C72FEC2@.microsoft.com...
> Oh, sorry for being unclear. The Tool Profiler is what I mean. Instead
> of
> running my traces in the Profiler application, I am converting them to
> stored
> procedures. That way I do not need to open Profiler at all and can place
> the
> scan into a Job controled By the SQL Server Agent.
> "Itzik Ben-Gan" wrote:
>|||do you have a web address I can do some reading on that?
"Itzik Ben-Gan" wrote:

> In that case, you only need to create them as the fully supported special
> procedures as the trace related procedures themselves are special
> procedures, and they interact with system objects.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:C576AF29-CA75-45B4-A3CF-BB730C72FEC2@.microsoft.com...
>
>|||Sure. Go to www.sqlmag.com, and look for the articles about sql trace, and
tracing with stored procedures. Kalen, Brian and I wrote many articles on
the subject.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F7B4ED88-8E81-4501-8008-5477B6D6EEF9@.microsoft.com...
> do you have a web address I can do some reading on that?
> "Itzik Ben-Gan" wrote:
>|||One more question Itzik; because it appears that I DO NOT need to place a
trace stored procedure into each of the databases I wish to record activity
on, I could place all the stored procedures I wish to run into a "dumby"
database.
The only purpose of this database is to hold stored procedures (my trace
profiles). This way the production databases are not cluttered with my
stored procedures used to monitor things with. this would be a great time
saver!
"Rich" wrote:
> do you have a web address I can do some reading on that?
> "Itzik Ben-Gan" wrote:
>