dev database name <> deployment database name

Topics: Database Publishing Wizard
Oct 30, 2006 at 3:24 AM

Hello,

thanks for providing this tool. I used it to take a backup of my hosted database (DotNetNuke running on SQL 2000). When I load the script in SQL Server Management Express it parses without issue, good so far. The trouble starts when I attempt to apply the script to a new empty database. The problem is that all objects are prefixed with their database name. Why do this? By definition the script is generated based on a single database already, right? Why put the database name in front of each table, procedure etc? Now the script can't be run without modification against a database that has a different name.

Now I could remove all the instances of "databasename." but that really shouldn't be needed. Have a look at mysqldump. This does what your utility aspires to do. The SQL that results from a dump can be executed against another database and tada! you have a copy.

Please consider at least adding an option to leave out the database name or just scrap them altogether. I don't see what use they are. If they are good for something, please let me know.

Thanks,

Sander
Oct 30, 2006 at 6:15 AM
Hi Sander -

Thanks for your comment. I believe the name you are referring to is the name of the schema containing the objects. Our current behavior is to explicitly place the objects in the the schemas they are created in, since application code can depend on these schema names.

Could you give me an example of a CREATE statement you are seeing so I can ensure this is the case?

Thanks,
alex
Nov 3, 2006 at 7:22 PM
Hi Alex,

sorry for the late reply. I guess I was waiting for a notification email but apparently those don't get sent out. In any case, here is a snippet from a file I just created:

/****** Object: StoredProcedure sanderp_ttech.GetUsersByRolename Script Date: 11/03/2006 15:17:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sanderp_ttech.GetUsersByRolename') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE sanderp_ttech.GetUsersByRolename

As you can see the script was generated based on the database sanderp_ttech. If I attempt to execute this script against a server which doesn't have a database by this name the script will fail. Now in this case it's not hard to replace each sanderp_ttech with a different string but it really shouldn't be needed.

I took a look at SQLyog which I use for my MySQL projects and it has a checkbox to include the database name in the script. In fact they simply add a 'use' statement at the top of the file to force the rest of the script to use that database. That sure saves a lot of bytes in the resulting file :) My DotNetNuke database isn't very big and already the script file is over 6 MB. Over cable modem that's not a big deal but I think script size is something you do need to keep in mind for the future.

All the best,

Sander
Nov 3, 2006 at 9:07 PM
Hi Sander -

Thanks for the reply.

I believe the string 'sanderp_ttech' that you see in the stored procedure is actually not a database.

If the source database you are using is SQL Server 2000, then this is either a role name or user name. Can you can confirm this by executing the following:

sp_helpuser 'sanderp_ttech'
or
sp_helprole 'sanderp_ttech'

If the source database is SQL Server 2005, then this name is a schema name. Can you can confirm this by executing the following:

select * from sys.schemas where name = 'sanderp_ttech'

In either event, we are looking at adding a switch that would allow you to strip out this name for the November release, since it does cause problems when migrating things between different databases.

About the size issue you mentioned: the T-SQL generated for a database does tend to be larger than the database itself, since the information is encoded in strings rather than in binary format. For the November release you will be able to specify to only script data or schema which should reduce the size if you do not need the entire database.

Thanks again for the feedback

Thanks,
alex
Nov 3, 2006 at 10:22 PM
Hi Alex,

you are correct, this was run against a 2000 server. At least I'm pretty sure that's what it is. sanderp_ttech is both the sql username and db name. That seemed like a good idea at the time but next time I'm giving them different names to avoid confusion :)

Having that switch available would be great!

Thanks,

Sander
Nov 3, 2006 at 11:18 PM
Hi Alex,

sorry, forgot to execute the commands. Good reason to brush up on my osql skills. Thankfully the version installed with 2005 Express allows access to remote servers.

sphelpuser 'sanderpttech'

Shows a bunch of stuff. Indeed sanderp_ttech is a user name and the server is 2000 flavor.

My confusion comes from a year of MySQL programming where id.table stands for database.table.

In any event, the user sanderp_ttech didn't exist on my target database so the script failed. I guess to execute a script you have to have owners for all objects eh?

Best,

Sander
Nov 4, 2006 at 12:27 AM
The current Publishing Wizard does not script users, which should solve this problem. The November release (coming out real soon now.. :) ) will support scripting users, so your database should work fine when this comes out.

Thanks,
alex