Copying the database

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
UdayIf you have Enterprise Manager, right-click on the database, All Tasks -> Export Data.

The source should already be selected, Click next.

Set up the destination to a SQL Server that you have control over and create a <new> database. Type in a name, click OK, then click Next.

Select Copy objects and data, click Next.

Select run immediately, click Next. Then you'll need to click OK somewhere in there.|||Hi,

I did that but I am not able to copy the stored procedures and views, when I try to generate query it gives me

/****** Encrypted object is not transferable, and script can not be generated. ******/

How can I copy this stored procedures and views.

Thanks in advance,
Uday.

Copying a database

Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!

Vik!Hi,

Basically, you can't. Not in one fell swoop. What you can do, however, is to detach the database from one server, copy it--along with the log file--to the new server, and attach it. But you'll need to recreate the logins, because the internal SIDs--security IDs--on the new server won't match those on the old.

If permissions are assigned to database roles, those will be intact.

Don|||If you have Enterprise Manager, you can just create a sql script, and script that database, and it's users.|||What donkiely says in true. However, I'd say the best techniques are...
1. Backup database on server 1, restore it on server 2, re-wire logins
2. Use SQLs Transformation Wizards from Enterprise Server...ok for small databases but network intensive. Best to sort out the security issue first for this one, that way the transform will do the bulk (ahem) of the work for you.
3. As previous posters says, script all the meta data, then copy the data. Basically the same issues as (2) but at least you've got some nice scripts to reapply if your db gets into trouble.|||KraGiE,

Just keep in mind that scripting the database won't copy the data. You'd then have to use DTS or some other means to copy it.

Don

Copy tables to a new database

Hi All,
If I put tables in 'Model' database and creating new database will automatic
ally copy the system files along with the additional files that I created.
I want to do the same thing, but getting all the tables from a database call
ed 'Template'. How could I
achieve this by writing a stored procedure? Any help or suggestions is appr
eciated.Steve,
Create a script of everything in Template (you could use RedGate, Enterprise
Manager,etc.) then run that script in the newly created database.
Alternatively, you create your Template database and put whatever you want
into it. Then back it up. Create your new database and use a RESTORE ...
REPLACE of your Template backup to load your template into the new database.
Russell Fields
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9771EEDD-FF40-4509-9EDE-7A0E04233A61@.microsoft.com...

quote:

---

> Hi All,
> If I put tables in 'Model' database and creating new database will

---

automatically copy the system files along with the additional files that I
created. I want to do the same thing, but getting all the tables from a
database called 'Template'. How could I achieve this by writing a stored
procedure? Any help or suggestions is appreciated.

Copy tables to a new database

Hi All,
If I put tables in 'Model' database and creating new database will automatically copy the system files along with the additional files that I created. I want to do the same thing, but getting all the tables from a database called 'Template'. How could I achieve this by writing a stored procedure? Any help or suggestions is appreciated.Steve,
Create a script of everything in Template (you could use RedGate, Enterprise
Manager,etc.) then run that script in the newly created database.
Alternatively, you create your Template database and put whatever you want
into it. Then back it up. Create your new database and use a RESTORE ...
REPLACE of your Template backup to load your template into the new database.
Russell Fields
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9771EEDD-FF40-4509-9EDE-7A0E04233A61@.microsoft.com...
> Hi All,
> If I put tables in 'Model' database and creating new database will
automatically copy the system files along with the additional files that I
created. I want to do the same thing, but getting all the tables from a
database called 'Template'. How could I achieve this by writing a stored
procedure? Any help or suggestions is appreciated.

copy tables along with constraints in DTS

Hi All,

We are using DTS tool for importing data from one database which is present in one server to another present in another server. the structure and the data is getting copied where as the constraints are not getting copied. pls suggest me as to how should i go about to to complete the job.

waiting for ur reply.

TIA
regards
AdilWhat exactly are your requirements for this ? Are you copying tables/databases ... you need indexes, constraints ...|||In Enterprise Manager, right click on the database and select "All Tasks - Import/Export", ... , select "copy objects and data between SQL Server database", click on "next", you have the option to copy all database objects include constraints into destination database.|||Hi Adil,

Would encourage you to use a free tool for DTS, which copies tables along with constraints. Please try Vaman DataServer from the following link. http://www.vaman.net/vmndataserver.asp
You may need to login for downloading.
Hope this helps...

popeye.

Originally posted by aadil
Hi All,

We are using DTS tool for importing data from one database which is present in one server to another present in another server. the structure and the data is getting copied where as the constraints are not getting copied. pls suggest me as to how should i go about to to complete the job.

waiting for ur reply.

TIA
regards
Adil