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 .NET Assembly with UDFs to a particular SCHEMA

Is it possible to deploy .NET assemblies and the UDF functions therein from Visual Studio.NET into a particular schema?Unfortunately this is not possible through Visual Studio 2005. You would need to write your own script for doing this.
However, you can execute any SQL script during the post deploy process in your SQL Server project as:

1. Write your script into a file called postdeployscript.sql:

2. Add the file to your project by right clicking your project in Solution Explorer and Add à Existing Item.

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run this scripts after the deployment of your project.
Same holds for predeployscript.sql if you want to execute a script on the SQL server before deployment.

These scripts can be used to automate something that Visual Studio does not currently support.

Thanks,
-Vineet.

|||

postdeployscript.sql and predeployscript.sql are the things I dreamed about all the time . You should definitely include them into default template of SQL Server Project, because I was searching for this solution for two months until I found this post. I asked similar question on the TechNet and the answer was just "you cannot do it".

Deploy .NET Assembly with UDFs to a particular SCHEMA

Is it possible to deploy .NET assemblies and the UDF functions therein from Visual Studio.NET into a particular schema?Unfortunately this is not possible through Visual Studio 2005. You would need to write your own script for doing this.
However, you can execute any SQL script during the post deploy process in your SQL Server project as:

1. Write your script into a file called postdeployscript.sql:

2. Add the file to your project by right clicking your project in Solution Explorer and Add à Existing Item.

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run this scripts after the deployment of your project.
Same holds for predeployscript.sql if you want to execute a script on the SQL server before deployment.

These scripts can be used to automate something that Visual Studio does not currently support.

Thanks,
-Vineet.

|||

postdeployscript.sql and predeployscript.sql are the things I dreamed about all the time . You should definitely include them into default template of SQL Server Project, because I was searching for this solution for two months until I found this post. I asked similar question on the TechNet and the answer was just "you cannot do it".

DENY SELECT on SCHEMA issue

Hi,
I want to deny select on many views/table so that my excel users won't
access them.
1. USING DENY SELECT ON VIEWS/TABLE
deny select on sys.all_columns to loana
deny select on sys.all_objects to loana
...etc...
-> This works fine but takes ages for going throgh all the views/tables
2. USING DENY SELECT ON SCHEMA
deny select on SCHEMA::sys to loana
-> This would do what I want in a unique statement but it does not
work. What I have wrong with the deny select on SCHEMA statement?
Note:
I use SQL server 2005 that I want to connect from Excel 2002/2003 Pivot
TableI just posted the similar question in the sqlserver.security newsgroup...
Having the same problem here. We want to suppress INFORMATION_SCHEMA and
sys Schemas without having to issue Deny statements on each object within.
Bill Blakey
<christophe.leroquais@.gmail.com> wrote in message
news:1150295767.920118.51280@.f6g2000cwb.googlegroups.com...
> Hi,
>
> I want to deny select on many views/table so that my excel users won't
> access them.
> 1. USING DENY SELECT ON VIEWS/TABLE
> deny select on sys.all_columns to loana
> deny select on sys.all_objects to loana
> ...etc...
> -> This works fine but takes ages for going throgh all the views/tables
>
> 2. USING DENY SELECT ON SCHEMA
> deny select on SCHEMA::sys to loana
> -> This would do what I want in a unique statement but it does not
> work. What I have wrong with the deny select on SCHEMA statement?
>
> Note:
> I use SQL server 2005 that I want to connect from Excel 2002/2003 Pivot
> Table
>

Deny create schema still does not work in 2k8

You can't assign a default schema for a user that maps to a Windows group. OK fine. But if you create a user for that group anyway with no default schema, any objects created by members of that group will automatically cause a schema to be created for them, EVEN WHEN THAT USER HAS BEEN EXPLICITLY DENIED THE CREATE SCHEMA PERMISSION IN THE DATABASE. This is the same as it was in 2K5, but has still not been fixed in 2k8.

To reproduce, do the following steps:
Create a windows group, either in the doman or local to the box.
Add at least one Windows user to that group.
Create a SQL login for that Windows group.
Map that login to a new user in some database.
Explicitly 'deny create schema to ' that group/user.
Grant 'create procedure' to that group/user.
Log onto SQL with a Windows login that is a member of that Windows group
Use the database that we set the permissions in above.
Create a dummy stored procedure without qualifying the name with a schema ie: 'create prodedure test1 as select 'hello world').
Look to see what you ended up with. You will have a new schema named the same as the user who created the sproc. This will now be their default schema implicitly, and the test1 stored procedure will be in that schema, EVEN WHEN THAT USER WA EXPLICITLY DENIED THE CREATE SCHEMA PERMISSION IN THE DATABASE.

Could you please fix this?

Actually what you are describing is implicit user creation. It is working by design and as described in BOL (please refer to the remarks section in USER_ID topic http://msdn2.microsoft.com/en-us/library/ms181466.aspx). This functionality is there mainly to preserve backwards compatibility when a Windows user doesn’t have an explicitly created user.

During the implicit user creation, it is the system creating a Windows principal and schema (following the SQL Server 2000 behavior) for a user that is taking an action on the database that requires an owner (in your scenario creating a SP) in order to mark the appropriate owner. Even more, this newly created schema is the default schema for the implicit user (again, for backwards compatibility).

For example, if you look at the newly created SP you mentioned, you will notice that the proper owner is a newly created user (Windows user) that maps to the Windows principal who created it:

CREATE DATABASE [db_Demo_ImplicitUser]

go

USE [db_Demo_ImplicitUser]

go

CREATE USER [DOMAIN\windows_group]

go

DENY CREATE SCHEMA TO [DOMAIN\windows_group]

go

GRANT CREATE PROCEDURE TO [DOMAIN\windows_group]

go

-- Using execute as login to mimic the behavior of

-- connecting as this principal

--

EXECUTE AS LOGIN = 'DOMAIN\windows_user'

go

-- It will return 0 == public

-- but user_name() knows this principal got access via a group

-- and handles the scenario using the Windows name directly

--

SELECT user_id(), user_name( user_id() ), user_name()

go

CREATE PROC sp_demo

as

print 'test'

go

-- In which schema was it created?

--

EXEC [DOMAIN\windows_user].[sp_demo]

go

-- Try the user_id test again and see that now this principal

-- is mapped to a DB principal

--

SELECT user_id(), user_name( user_id() ), user_name()

go

SELECT * FROM sys.database_principals

SELECT * FROM sys.objects

go

-- This call should fail

--

CREATE SCHEMA [myOwnSchema]

go

One point that is important to emphasize is that the user doesn’t have the ability to create arbitrary schemas (i.e. call CREATE SCHEMA).

Hopefully this brief explanation will help to explain the reasons behind this behavior; but please let us know is you have any additional feedback on this topic, we really appreciate it.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Yes, I am aware that it is now documented since it was done so in part due to my opening an incident with MS that this behavior happened at all in 2K5. There "solution" was simply to document it and close the indicent. But that doesn't fix it. It's illogical and breaks security. I have no way to deny schema creation. That's broken and needs to be fixed. Period.