February 24, 2011 at 12:41 pm
Sql 2008 R2 Enterprise 64 bit, no true DBA, my access = sa
The situation is that we have a web application prototype (Net MVC3) which in turn uses a prototype DB. What I am looking to do is to be able to replicate the DB schema into a new DB with a new user associated to that DB.
Ideally a management web-app would be able to pass the dbName, username, password to a sproc which would create the db and user, and then copy over the db structure to the newly created db.
I already have a procedure in place which creates the db and the user, my problem is with creating the objects within the new db. I have a procedure with all of the creation scripts located in another db, but found if I call it the new objects I created in its parent db and not the db calling it
Run SSIS packages from remote client with stored procedure but as have little knowledge of SSIS I do not know if which portions I would or would not need to implement this or if this is even the correct route
My ideal solution would be called similiar to this:
EXECUTE spc_DatabaseCreateNew @dbName, @dbNewUser, @dbNewUserPass
and that procedure would be similiar to
USE [MASTER]
CREATE DATABASE [@dbName]
EXEC sp_AddLogin @dbNewUser, @dbNewUserPass
USE [shared]
EXEC sp_GrantDbAccess @dbUser;
EXEC sp_AddRoleMember 'sharedUser', @dbUser
USE [@dbName]
EXEC sp_GrantDbAccess @dbUser;
EXEC sp_AddRoleMember 'db_owner', @dbUser
CREATE TABLE dbo.MyTableName(
ndxINTIDENTITY(1,1)
) ON [PRIMARY]
/* and so on and so forth*/
I do not work on the actual sql server, so my only real tools are limited to SSMS Express (no SSIS). I can get physical access to the server if needed to create a package if needed.
I do have the .sql files that I created the procedures with for all of the tables etc if that helps
February 24, 2011 at 12:50 pm
Have you tried simply creating a backup of the database as it's supposed to be when you create a new copy?
You can use the Restore command to give it a new name when you restore it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2011 at 1:46 pm
The application installing this would not have directory access to tell sql where to backup/restore/move the files, so I did not see this as viable
February 24, 2011 at 2:38 pm
Then you'll need to do it in two steps.
The first creates the database without any content (beyond whatever's in model).
The second opens a connection to the new database and runs the rest of the script in that context. For that, you might want to look into some command-line type interface, or PowerShell or some such.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 5:32 pm
Finally got the time to get back to this. I ended up using xp_CmdShell / SqlCmd, and it seems to work just fine. This is actually called from another procedure that creates the db and then user prior to execution. It seems to work fine but I am always open for suggestions
CREATE PROCEDURE [dbo].[gsp_InstallDatabaseTables](
@DSNNVARCHAR(100)
) AS
BEGIN
/*
===========================================================================
Install DB Schema Base
Version 1.0.0.1
1 March 2011
REQUIRES: external file "\\server\Share\Tables.sql"
===========================================================================
*/
SET NOCOUNT ON
DECLARE@SqlCmdNVARCHAR(4000)
SET@SqlCmd= 'xp_CmdShell ''SqlCmd -d ' + @DSN + ' -i "\\server\Share\Tables.sql"'''
PRINT@SqlCmd
EXECUTE sp_ExecuteSql @SqlCmd
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply