October 19, 2011 at 2:05 am
Sorry I should of mentioned about the Add-PSSnapin parts, the server where the PS1 file was running from was a none SQL machine so it didnt have the SQLPS.dll's registered as and when it loaded powershell so we had to load them in manually, if you are running from a Job this shouldnt be needed as they should already be loaded
October 19, 2011 at 2:10 am
Indexes and FK's can be done by doing schema.tablename after tables
Might have to write some sort of loop when using GCI/DIR to get the list of tables to drill in and script indexes, foreign keys, triggers etc
eg
SQLSERVER:\SQL\SERVER\INSTANCE\DATABASES\REPORTSERVER\TABLES\DBO.EVENT\INDEXES
SQLSERVER:\SQL\SERVER\INSTANCE\DATABASES\REPORTSERVER\TABLES\DBO.EVENT\FOREIGNKEYS
October 19, 2011 at 10:03 am
I still think using sqlpubwiz is the easiest way to do this. It works on 2008R2 (as I've just tested it) and I would run it in a batch file as a windows task. You could also run it as a job in SQL and put it into an SP if you need to. Many possibilities without writing a lot of script.
I ran this:
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\sqlpubwiz" script -d AdventureWorks "C:\AdventureWorks.sql" -schemaonly -targetserver 2008
and it gave me exactly what I needed 🙂
We have found that the versioning is strange for database publishing wizard. If you have version 1.2, download the new one (1.3). If you have installed Denali CTP3, you may have version 1.4 already installed.
Jared
EDIT: Added "-targetserver 2008" to command
Jared
CE - Microsoft
October 23, 2011 at 10:30 pm
jared-709193 (10/19/2011)
I still think using sqlpubwiz is the easiest way to do this. It works on 2008R2 (as I've just tested it) and I would run it in a batch file as a windows task. You could also run it as a job in SQL and put it into an SP if you need to. Many possibilities without writing a lot of script.I ran this:
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\sqlpubwiz" script -d AdventureWorks "C:\AdventureWorks.sql" -schemaonly -targetserver 2008
and it gave me exactly what I needed 🙂
We have found that the versioning is strange for database publishing wizard. If you have version 1.2, download the new one (1.3). If you have installed Denali CTP3, you may have version 1.4 already installed.
Jared
EDIT: Added "-targetserver 2008" to command
The publishing wizard does not script something like this:
EXEC sp_addrolemember N'db_datareader', N'MyUser'
GRANT EXECUTE TO [MyUser]
The powershell on the other hand does, but it puts the "Grant execute" statement into the database script, which can not run untill the user script is run:
Create user ...
And the "create user" script can not be run, before the database script is run. Chicken and egg situation.
Also the role membership command comes without "exec":
Create user ...
sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'MyUser'
which causes an error.
Also powershell has issues with scripting assemblies: it scripts them in order, which does not take into account their dependencies, so an assembly may be created before another one, on which the first one depends on.
So pretty much everything I've tried required few little tweaks on the final scripts, before they can be run without errors, which is a manual operation and therefore does not allow full automation of the process.
It's sadness 🙁
October 24, 2011 at 7:37 am
Roust_m (10/23/2011)
jared-709193 (10/19/2011)
I still think using sqlpubwiz is the easiest way to do this. It works on 2008R2 (as I've just tested it) and I would run it in a batch file as a windows task. You could also run it as a job in SQL and put it into an SP if you need to. Many possibilities without writing a lot of script.I ran this:
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4\sqlpubwiz" script -d AdventureWorks "C:\AdventureWorks.sql" -schemaonly -targetserver 2008
and it gave me exactly what I needed 🙂
We have found that the versioning is strange for database publishing wizard. If you have version 1.2, download the new one (1.3). If you have installed Denali CTP3, you may have version 1.4 already installed.
Jared
EDIT: Added "-targetserver 2008" to command
The publishing wizard does not script something like this:
EXEC sp_addrolemember N'db_datareader', N'MyUser'
GRANT EXECUTE TO [MyUser]
The powershell on the other hand does, but it puts the "Grant execute" statement into the database script, which can not run untill the user script is run:
Create user ...
And the "create user" script can not be run, before the database script is run. Chicken and egg situation.
Also the role membership command comes without "exec":
Create user ...
sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'MyUser'
which causes an error.
Also powershell has issues with scripting assemblies: it scripts them in order, which does not take into account their dependencies, so an assembly may be created before another one, on which the first one depends on.
So pretty much everything I've tried required few little tweaks on the final scripts, before they can be run without errors, which is a manual operation and therefore does not allow full automation of the process.
It's sadness 🙁
Yes, good point. If permissions need to be granted, sqlpubwiz will not do that. We use this for versioning as we do not want to use a third party software. Since we don't use this for immediate disaster recovery, this may not be an option for you. However, if you know the users ahead of time, it would not be a hard thing to script out those users separately as they will not change permissions between your restore. Good luck!
Thanks,
Jared
Jared
CE - Microsoft
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply