Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
then transfer the data). You find this in Integration Services, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hnteknik" <Henrik.F.A.Nielsen@.gmail.com> wrote in message
news:1166181741.158144.213330@.n67g2000cwd.googlegroups.com...
>I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.
Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(
|||Hi
Even if you can't restore the backup, I would expect your ISP to do it for
you.
John
"hnteknik" wrote:

> I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>
|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

Deployment/copy of Db from remote server

I need to copy both schema and data from a remote db server at my ISP.
I used to use a Red Gate solution but dont have access to the proggie
anymore.
I then tried to use the import/export tool for the SQL 2005 server, but
view turned into tables and primary keys autoinc were not included.
What should I have done in order to get at complete copy. I cannot use
the backup function at the ISPs SQL server.The import/export wizard in 2005 doesn't expose the SQL Server specific ("sc
ript all objects and
then transfer the data). You find this in Integration Services, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hnteknik" <Henrik.F.A.Nielsen@.gmail.com> wrote in message
news:1166181741.158144.213330@.n67g2000cwd.googlegroups.com...
>I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Tibor Karaszi skrev:
> The import/export wizard in 2005 doesn't expose the SQL Server specific ("
script all objects and
> then transfer the data). You find this in Integration Services, though.
>
Thanks - I will give it a try.
Found out later that :
'The SQL Server Import and Export Wizard provides minimal
transformation capabilities. Except for setting the name, the data
type, and the data type properties of columns in new destination tables
and files, the SQL Server Import and Export Wizard supports no
column-level transformations.' :-(|||Hi
Even if you can't restore the backup, I would expect your ISP to do it for
you.
John
"hnteknik" wrote:

> I need to copy both schema and data from a remote db server at my ISP.
> I used to use a Red Gate solution but dont have access to the proggie
> anymore.
> I then tried to use the import/export tool for the SQL 2005 server, but
> view turned into tables and primary keys autoinc were not included.
> What should I have done in order to get at complete copy. I cannot use
> the backup function at the ISPs SQL server.
>|||Hi John
I ran into this tool, which did the job of restoring my ISP DB as a
local DB on my server as it works both ways:
The Database Publishing Wizard enables the deployment of SQL Server
2005 databases (both schema and data) into a shared hosting environment
on either a SQL Server 2000 or 2005 server.
The tool supports two modes of deployment:
It generates a single SQL script file which can be used to recreate a
database when the only connectivity to a server is through a web-based
control panel with a script execution window.
It connects to a web service provided by your hoster and directly
creates objects on a specified hosted database
The Database Publishing Wizard provide both a graphical and a
command-line interface. In addition, it can integrate directly into
Visual Studio 2005 or Visual Web Developer 2005.
http://www.codeplex.com/sqlhost/Wik...0Wizar
d

Deployment Copy/Paste Problems

Hi all,
We maintain our CAB (meta-data only) in a source control system. The
process for deployment is simply to restore the CAB on the production
server and then copy/paste it to the production database name where it
will have it's final resting place.
By doing this we allow for scalability and robust management since the
same CAB can be deployed to many places. (In some places we process
different sets of cubes than in other places but the same meta-data
can be re-used.)
The problem we are finding is that one of our production servers is a
64 bit box that is (necessarily) remotely managed from a 32 bit
instance of Analysis Manager. When copy/pasting the database remotely
it takes a very, very long time to complete. Keep in mind that only
meta-data is being copied. When copy/paste is happening, it looks
like DSO code is executing to rebuild an entire database from scratch.
Does anybody have a faster method of copy/pasting an Analysis Services
database into place for deployment purposes?
Thanks,
DavidDavid Beavon wrote:

> Hi all,
>
> We maintain our CAB (meta-data only) in a source control system. The
> process for deployment is simply to restore the CAB on the production
> server and then copy/paste it to the production database name where it
> will have it's final resting place.
> By doing this we allow for scalability and robust management since the
> same CAB can be deployed to many places. (In some places we process
> different sets of cubes than in other places but the same meta-data
> can be re-used.)
>
> The problem we are finding is that one of our production servers is a
> 64 bit box that is (necessarily) remotely managed from a 32 bit
> instance of Analysis Manager. When copy/pasting the database remotely
> it takes a very, very long time to complete. Keep in mind that only
> meta-data is being copied. When copy/paste is happening, it looks
> like DSO code is executing to rebuild an entire database from scratch.
>
> Does anybody have a faster method of copy/pasting an Analysis Services
> database into place for deployment purposes?
> Thanks,
> David
http://www.solgenia.com/usa/Solutio.../SheetRubik.htm

deploying reports database to multiple servers

Hi,
i have some 35 customers, all of whom need a copy of the reports i have designed
how do i quickly and simply roll out my reports. the databases are generally , but not
always given the same name, and the user credentials will be different
but the reports need to be the same.

is this possible?

Regards,

Russ

You have two basic options:

1.) Script out creating the reports in the database and provide the scripts to your users.

2.) Detach your database, and give it to your users. They can then attach the database, and configure RS to use that database via the config tool. If they do this, they have to be sure to leave the name of the databases that same.

|||

Cheers John,

im wondering how easy it is to do option 1. is there an example available that you know of?

Regards,

Russ

|||

Try rdlscripter, it is very easy to do this.

http://www.sqldbatips.com/showarticle.asp?ID=62

deploying reports database to multiple servers

Hi,
i have some 35 customers, all of whom need a copy of the reports i have designed
how do i quickly and simply roll out my reports. the databases are generally , but not
always given the same name, and the user credentials will be different
but the reports need to be the same.

is this possible?

Regards,

Russ

You have two basic options:

1.) Script out creating the reports in the database and provide the scripts to your users.

2.) Detach your database, and give it to your users. They can then attach the database, and configure RS to use that database via the config tool. If they do this, they have to be sure to leave the name of the databases that same.

|||

Cheers John,

im wondering how easy it is to do option 1. is there an example available that you know of?

Regards,

Russ

|||

Try rdlscripter, it is very easy to do this.

http://www.sqldbatips.com/showarticle.asp?ID=62

Deploying IS properly

Hi,

How do i deploy IS package properly? can i just copy the .dtsx and assign that in an sql jobs?

thanks a lot

cherriesh

With DTExecUI you can build command line that can be executed using DTExec in a job see

Here another article about it|||

cherriesh wrote:

Hi,

How do i deploy IS package properly? can i just copy the .dtsx and assign that in an sql jobs?

thanks a lot

cherriesh

A short answer could be yes.

You can deploy SSIS packages to a file system folder or store them in a DB. You could also use the 'deployment utility' that is part of BIDS, but there are some know issues with that (e.g. deploying a config file used by multiple packages).

If you are going to deploy the packages to multiple environments; make sure you use 'package configurations' to set some property' values at run time dynamically. For more info:

http://technet.microsoft.com/en-us/library/ms137900.aspx

deploying changes to cub

Hi,

I have a cube in production server and i want to update that with my latest copy. Is the only way to update it is generate an .xmla again through the deployment wizard? what if i just added a new cube on top of my existing ones. do i need to replace all of them again?

cherriesh

From the underlying XMLA perspective this is definitely possible. The Deployment Wizard uses an Alter command with the AllowCreate option set to true, so aslong as you have not altered the structure of any of the existing cubes and have not chosen the option to do a process operation as part of the deploy, then you should be able to deploy using the Wizard without loosing any data. You would then only need to process the new cube.

It would also be an option to build a CREATE script for just the new cube, but I don't know of any tools that would build this for you. If you have a dev server you could deploy the database to it and connect to it using SSMS and then right click on the new cube and script out the CREATE command.

Note that a cube is dependant on other objects in the database like the dimensions and if any of these have been changed or added then the create script on its own might not work.

Deploying a database to multiple servers - smooth sailing?

Hello,
I need to copy a database to multiple servers, all of this using SQL Server
2000. -- this will enable the testing of an application with identical
databases. I'm concerned here with the original installation of the database
on the target servers and subsequent updates using restoration of the
database from the master server
I'm contemplating having the master database on one server and using data
management objects to make a database backup on that machine. That backed-up
database would be placed in a share with some scripts that would perform the
restore on the target servers - to be initiated by the users.
This works well on my master server but can I expect a smooth distribution
using this approach or is there a better approach. I like this method as
it's simple. The target servers will all be SQL Server 2000, possibly with
different service patches. The target servers would has the same local
administrators account.
Is there anything internal to the back up or the target server that would
prevent this operation? There's no dependencies such as a database can only
be restored to a server it was backed up on, is there?
TIA,
Rod
> This works well on my master server but can I expect a smooth distribution
> using this approach or is there a better approach. I like this method as
> it's simple. The target servers will all be SQL Server 2000, possibly
> with
> different service patches. The target servers would has the same local
> administrators account.
Your servers should all be at the same patch level. Occassionally, there
are differences in the structure of the backup that are not backward
compatible.

> Is there anything internal to the back up or the target server that would
> prevent this operation? There's no dependencies such as a database can
> only
> be restored to a server it was backed up on, is there?
No.
|||Thank you
"Scott Morris" wrote:

> Your servers should all be at the same patch level. Occassionally, there
> are differences in the structure of the backup that are not backward
> compatible.
>
> No.
>
>

Deploying a database to multiple servers - smooth sailing?

Hello,
I need to copy a database to multiple servers, all of this using SQL Server
2000. -- this will enable the testing of an application with identical
databases. I'm concerned here with the original installation of the database
on the target servers and subsequent updates using restoration of the
database from the master server
I'm contemplating having the master database on one server and using data
management objects to make a database backup on that machine. That backed-up
database would be placed in a share with some scripts that would perform the
restore on the target servers - to be initiated by the users.
This works well on my master server but can I expect a smooth distribution
using this approach or is there a better approach. I like this method as
it's simple. The target servers will all be SQL Server 2000, possibly with
different service patches. The target servers would has the same local
administrators account.
Is there anything internal to the back up or the target server that would
prevent this operation? There's no dependencies such as a database can only
be restored to a server it was backed up on, is there?
TIA,
Rod> This works well on my master server but can I expect a smooth distribution
> using this approach or is there a better approach. I like this method as
> it's simple. The target servers will all be SQL Server 2000, possibly
> with
> different service patches. The target servers would has the same local
> administrators account.
Your servers should all be at the same patch level. Occassionally, there
are differences in the structure of the backup that are not backward
compatible.
> Is there anything internal to the back up or the target server that would
> prevent this operation? There's no dependencies such as a database can
> only
> be restored to a server it was backed up on, is there?
No.|||Thank you
"Scott Morris" wrote:
> > This works well on my master server but can I expect a smooth distribution
> > using this approach or is there a better approach. I like this method as
> > it's simple. The target servers will all be SQL Server 2000, possibly
> > with
> > different service patches. The target servers would has the same local
> > administrators account.
> Your servers should all be at the same patch level. Occassionally, there
> are differences in the structure of the backup that are not backward
> compatible.
> > Is there anything internal to the back up or the target server that would
> > prevent this operation? There's no dependencies such as a database can
> > only
> > be restored to a server it was backed up on, is there?
> No.
>
>

Deploying a database to multiple servers - smooth sailing?

Hello,
I need to copy a database to multiple servers, all of this using SQL Server
2000. -- this will enable the testing of an application with identical
databases. I'm concerned here with the original installation of the databas
e
on the target servers and subsequent updates using restoration of the
database from the master server
I'm contemplating having the master database on one server and using data
management objects to make a database backup on that machine. That backed-u
p
database would be placed in a share with some scripts that would perform the
restore on the target servers - to be initiated by the users.
This works well on my master server but can I expect a smooth distribution
using this approach or is there a better approach. I like this method as
it's simple. The target servers will all be SQL Server 2000, possibly with
different service patches. The target servers would has the same local
administrators account.
Is there anything internal to the back up or the target server that would
prevent this operation? There's no dependencies such as a database can only
be restored to a server it was backed up on, is there?
TIA,
Rod> This works well on my master server but can I expect a smooth distribution
> using this approach or is there a better approach. I like this method as
> it's simple. The target servers will all be SQL Server 2000, possibly
> with
> different service patches. The target servers would has the same local
> administrators account.
Your servers should all be at the same patch level. Occassionally, there
are differences in the structure of the backup that are not backward
compatible.

> Is there anything internal to the back up or the target server that would
> prevent this operation? There's no dependencies such as a database can
> only
> be restored to a server it was backed up on, is there?
No.|||Thank you
"Scott Morris" wrote:

> Your servers should all be at the same patch level. Occassionally, there
> are differences in the structure of the backup that are not backward
> compatible.
>
> No.
>
>