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

deploy sql server express to ISP

To deploy sql server express to an ISP that supports Sql Server, I backed up the .mdf and .ldf files.

I can connect to the ISP database with Sql Server Mangement Studio Express. That connection requires a password.

When I try to execute 'restore', I get an error for no permission.

Any ideas why I have no permission after a password has already been entered?

I sure appreciate any help.

You will need to contact your ISP to discover what permissions you have on thier server. If you're getting a no permission error, it means that you don't have permission to perform a RESTORE on the server. SQL security is not all or nothing, you can be assigned a whole range of permissions that each imply certain rights.

Is RESTORE the way your ISP recomends putting databases on thier system? I'd actually be kind of surprised if it was, most ISP do not give this level of permissions to thier users.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Finally --

I found a solution.

The problem was the ISP. They were not helpful at all. I quit and went to discountasp.net (yea) -- they have a tool to convert sql server express over to sql server 2005 and they have answered all of my questions within just an hour or so.

Thanks for your help Mike.

|||

Glad that it worked out for you CK. I'm also glad the discountasp.net has a tool to upload your database easily.

I'll make one minor nit just so it's mentioned in the thread. I think it's important for folks to understand that SQL Express is SQL Server 2005. There is not difference between the database files, all the same data types are supported, etc. SQL Express has fewer functions, but that's all. This is pretty normal, think about all the applications that offer a "lite" or "personal" edition with reduced functionaltiy, and then when you pay, you get additional functionality, same thing here.

You don't need to convert the SQL Express database, it's already exactly the same. The confusion stems from two issues:

Some Hosters allow you to move your database to the site using the Import/Export wizard, which isn't currently in SQL Express. This makes it hard to get your database on the server and we're working with Hosters to get the whole upload story working for SQL Express users.|||

Hi Mike, very pleased to find your post. I have a website built in VWD express, where deployment to an isp fails with this:

... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...

After a rather frustrating 2 days I've arrived at the same conclusion as you suggest, that isp's do not really support sql express, and they annoyingly avoid saying so in their blurb. Yet the VWD Express deployment went just fine, so I just have to fix the SQL stuff.

From your post are you saying that SQL Express is Enterprise ManagerLite, but under the hood, its still system tables like sysobjects and so on, just inaccessible. Or is SQL Express reduced in actual scope and reach, regardless of accessibilty. I cannot find a SQL express query window on its own, it seems that the only interface is from within VWD which is sufficient, although much reduced from Enterprise Manager. This must be Lite.

Reason I ask is so that I can understand better what happens when i transfer my database (at least its contents) from my local machine to my isp. Following your logic, do my SQL Express .mdf (with or without .ldf) files attach/detach seamlessly between SQL Express and SQL 2005 and back. In which case I just have to find an isp who will allow attach/detach. Then I alter the connection string in web.config to use the server/database on the isp's machine as per MSDN. Then it should work?

Hope this brightens your day, then maybe you can shed some light ...

Charlie

|||

Charlie,

I'm sorry about your 2 days of frustration. I know exactly what you are going through. The ISP that caused me 3 weeks of frustration (1) told me on the phone before I signed up that VWD/SQL Server Express would work fine (2) sent me solutions from multiple techs that required Enterprise Manager - which is NOT available from SQL Express and (3) would not admit they didn't know.

Of course I bought into the idea that VWD is for hobbyists and lite-weights, forcing me to pass my frustrations on to this forum(!), but now that is behind me and I am a happy camper.

Here's an article that helped me: http://aspnet.4guysfromrolla.com/articles/040506-1.aspx