October 17, 2011 at 3:44 pm
jared-709193 (10/17/2011)
Roust_m (10/16/2011)
I don't want to backup them, so to avoid any performance impact, so I want to script them out and in case of any failure restore the databases from the script and then re-initialise the replication.I'm trying to understand why you are worried about performance? I think that performance would be more affected by scripting out the data and restore data from that script. First, these are transactionally replicated, so the data is only as new/current as the publisher. Are you backing up the publisher database (I hope you are :)? Why not just restore from the publisher, or generate a snapshot (unless your data set is too large) and re-initialize?
Thanks,
Jared
Yes, of course I am backing up the publisher. The reason I don't want to restore the publisher database is because it has different schema: some tables are not replicated, it has different SPs and also there are some columns, that are not replicated together with some row filtering.
So, my plan is: script the database via an sql job (schema only), IF the disaster happens: re-create the empty database from the scripts and then re-initialise the replication.
October 18, 2011 at 5:59 am
Easy enough! 🙂 Use the wizard to script what you want, or script the entire database and then remove what you don't need. You may want to add in sp_change_users_login 'auto_fix', 'databaseUser' for each user to fix orphaned logins on creation.
Thanks,
Jared
Jared
CE - Microsoft
October 18, 2011 at 7:52 am
i use powershell to script the object definitions out to a .sql file on a nightly basis so that developers dont need any permissions on the live db.
couple of ways that I do it depending if I want it to one big file or each object in its own file
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
foreach ($tbl in Get-ChildItem SQLSERVER:\SQL\Win7NetBook\Default\DATABASES\ADVENTUREWORKS\TABLES )
{
$k="C:\Temp\" + $($tbl.Schema) + "." + $($tbl.name) + "_table.SQL"
$tbl.Script() > $k
}
#and repeat for each type of object e.g procs views etc
OR
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
#MAP NETWORK DRIVE
New-PSDrive -Name A -PSProvider FileSystem -Root TOSOMEUNCPATH
#REMOVE OLD COPIES
get-childitem A:\ -include *.sql -recurse | foreach ($_) {remove-item $_.fullname}
#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd-hh_mm
#SCRIPT
SL SQLSERVER:\SQL\SERVER\INSTANCE\DATABASES\DBNAME\Tables
dir | %{$_.Script() | Out-File "A:\FOLDER\Tables $timestamp.sql" -Append}
October 18, 2011 at 8:17 am
The failure may or may not happen. If it does happen, I will use scripts and re-initialise the data.
Will you not require having a FULL database backup or Snapshot (replication) to do this? If it’s a HUGE database it may take a lot of time to do so.
October 18, 2011 at 4:26 pm
Dev @ +91 973 913 6683 (10/18/2011)
The failure may or may not happen. If it does happen, I will use scripts and re-initialise the data.
Will you not require having a FULL database backup or Snapshot (replication) to do this? If it’s a HUGE database it may take a lot of time to do so.
No, I don't have a huge database, instead I have around 40 small and medium databases on different instances. I won't need snapshot replication to rebuild the data, just re-init the transactional replication having built an empty database from the scripts prior to that.
October 18, 2011 at 4:32 pm
jared-709193 (10/18/2011)
Easy enough! 🙂 Use the wizard to script what you want, or script the entire database and then remove what you don't need. You may want to add in sp_change_users_login 'auto_fix', 'databaseUser' for each user to fix orphaned logins on creation.Thanks,
Jared
No, I can't do it manually all the time, I need this to be run via an sql job on a regular basis. It looks like Powershell is the only option (I am not very good at it though), so I wish there was a way to run that wizard automatically.
October 18, 2011 at 4:41 pm
Roust_m (10/18/2011)
jared-709193 (10/18/2011)
Easy enough! 🙂 Use the wizard to script what you want, or script the entire database and then remove what you don't need. You may want to add in sp_change_users_login 'auto_fix', 'databaseUser' for each user to fix orphaned logins on creation.Thanks,
Jared
No, I can't do it manually all the time, I need this to be run via an sql job on a regular basis. It looks like Powershell is the only option (I am not very good at it though), so I wish there was a way to run that wizard automatically.
Have you looked at sys.schemas? Also, have you looked at Database Publishing Wizard?
Thanks,
Jared
Jared
CE - Microsoft
October 18, 2011 at 4:48 pm
Let me add a link to Pinal Dave for Database Publishing Wizard. If you scroll down, you will see some comments on 2008 and that this installs with 2008.
Thanks,
Jared
Jared
CE - Microsoft
October 18, 2011 at 5:03 pm
anthony.green (10/18/2011)
i use powershell to script the object definitions out to a .sql file on a nightly basis so that developers dont need any permissions on the live db.couple of ways that I do it depending if I want it to one big file or each object in its own file
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
foreach ($tbl in Get-ChildItem SQLSERVER:\SQL\Win7NetBook\Default\DATABASES\ADVENTUREWORKS\TABLES )
{
$k="C:\Temp\" + $($tbl.Schema) + "." + $($tbl.name) + "_table.SQL"
$tbl.Script() > $k
}
#and repeat for each type of object e.g procs views etc
OR
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
#MAP NETWORK DRIVE
New-PSDrive -Name A -PSProvider FileSystem -Root TOSOMEUNCPATH
#REMOVE OLD COPIES
get-childitem A:\ -include *.sql -recurse | foreach ($_) {remove-item $_.fullname}
#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd-hh_mm
#SCRIPT
SL SQLSERVER:\SQL\SERVER\INSTANCE\DATABASES\DBNAME\Tables
dir | %{$_.Script() | Out-File "A:\FOLDER\Tables $timestamp.sql" -Append}
Tried the second script and got few errors:
A job step received an error at line 3 in a PowerShell script. The corresponding line is 'Add-PSSnapin SqlServerCmdletSnapin100'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Add-PSSnapin' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
A job step received an error at line 2 in a PowerShell script. The corresponding line is 'Add-PSSnapin SqlServerProviderSnapin100'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Add-PSSnapin' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
After removing those lines, got this:
A job step received an error at line 4 in a PowerShell script. The corresponding line is 'SL SQLSERVER:\SQL\(local)\DEFAULT\Databases\MyDb\Tables'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A positional parameter cannot be found that accepts argument ''. '. Process Exit Code -1. The step failed.
October 18, 2011 at 5:05 pm
jared-709193 (10/18/2011)
Roust_m (10/18/2011)
jared-709193 (10/18/2011)
Easy enough! 🙂 Use the wizard to script what you want, or script the entire database and then remove what you don't need. You may want to add in sp_change_users_login 'auto_fix', 'databaseUser' for each user to fix orphaned logins on creation.Thanks,
Jared
No, I can't do it manually all the time, I need this to be run via an sql job on a regular basis. It looks like Powershell is the only option (I am not very good at it though), so I wish there was a way to run that wizard automatically.
Have you looked at sys.schemas? Also, have you looked at Database Publishing Wizard?
Thanks,
Jared
Yes, I have looked at database publishing wizard, so my question was how do you run it from a scheduled SQL job? I don't have the capacity to run in manually every night or every week for around 40 databases.
October 18, 2011 at 5:06 pm
jared-709193 (10/18/2011)
Let me add a link to Pinal Dave for Database Publishing Wizard. If you scroll down, you will see some comments on 2008 and that this installs with 2008.Thanks,
Jared
This may be the answer to my question, thanks.
October 18, 2011 at 5:13 pm
Roust_m (10/18/2011)
No, I can't do it manually all the time, I need this to be run via an sql job on a regular basis. It looks like Powershell is the only option (I am not very good at it though), so I wish there was a way to run that wizard automatically.
If it's got to be done via TSQL, i've got a solution that's been working for me.
i wrote a couple of procs, one which simply loops thru all the objects in dependancy order, adn the other which scripts any database object: Table, Proc, View, Function.
i stick the results into a table, and from there you could bcp out the table results.
the advantage of scripting the objects out in dependancy order resolves a lot of silly issues like disable and re-enableing constraints and stuff. You end up with a perfect, ready to run script to create teh database via scripting.
let me know if this works for you.
you also need this procedure as welll:
--note that this script has a dependancy to sp_getDDLa
--http://www.stormrage.com/SQLStuff/sp_GetDDLa_Latest.txt
CREATE PROCEDURE sp_export_schema
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #MyObjectHierarchy
(
HID int identity(1,1) not null primary key,
ObjectId int,
OBJTYPE int,OBJECTTYPE AS CASE
WHEN OBJTYPE = 1 THEN 'FUNCTION'
WHEN OBJTYPE = 4 THEN 'VIEW'
WHEN OBJTYPE = 8 THEN 'TABLE'
WHEN OBJTYPE = 16 THEN 'PROCEDURE'
WHEN OBJTYPE =128 THEN 'RULE'
ELSE ''
END,
OBJNAME varchar(255),
OBJSCHEMA varchar(255),
SEQ int
)
--our results table
CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
--our list of objects in dependancy order
INSERT #MyObjectHierarchy (OBJTYPE,ONAME,OOWNER,SEQ)
EXEC sp_msdependencies @intrans = 1
Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
--synonyns are object type 1 Function?!?!...gotta remove them
DELETE FROM #MyObjectHierarchy WHERE objectid in(
SELECT [object_id] FROM sys.synonyms UNION ALL
SELECT [object_id] FROM master.sys.synonyms)
--custom requirement: only objects starting with KLL
--DELETE FROM #MyObjectHierarchy WHERE LEFT(ONAME,3) <> 'KLL'
DECLARE
@schemaname varchar(255),
@objname varchar(255),
@objecttype varchar(20),
@FullObjectName varchar(510)
DECLARE cur1 CURSOR FOR
SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
OPEN cur1
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
WHILE @@fetch_status <> -1
BEGIN
SET @FullObjectName = QUOTENAME(@schemaname) + '.' + QUOTENAME(@objname)
PRINT @FullObjectName
IF @objecttype IN( 'TABLE','VIEW','FUNCTION','PROCEDURE')
BEGIN
INSERT INTO #Results(ResultsText)
EXEC sp_GetDDLa @FullObjectName
END
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
END
CLOSE cur1
DEALLOCATE cur1
SELECT ResultsText FROM #Results ORDER BY ResultsID
END
GO
Lowell
October 18, 2011 at 7:59 pm
Dev @ +91 973 913 6683 (10/17/2011)
via xp_cmdshell;
Please follow the discussion... I cringe every time I see a recommendation for xp_cmdshell....
http://www.sqlservercentral.com/Forums/Topic1186236-1526-1.aspx
Heh... yes, please DO follow the discussion... ALL of it. Especially the parts where I say that if you cringe at the idea of xp_CmdShell, then you might want to also cringe at how you don't have your server properly locked down from attack. 😉
Stop bashing the tool called "xp_CmdShell". It's fairly easy to use it correctly. The only thing wrong with it is that most people don't know how to use it safely. Cringe at the people not using it correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2011 at 11:54 pm
Roust_m (10/18/2011)
anthony.green (10/18/2011)
i use powershell to script the object definitions out to a .sql file on a nightly basis so that developers dont need any permissions on the live db.couple of ways that I do it depending if I want it to one big file or each object in its own file
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
foreach ($tbl in Get-ChildItem SQLSERVER:\SQL\Win7NetBook\Default\DATABASES\ADVENTUREWORKS\TABLES )
{
$k="C:\Temp\" + $($tbl.Schema) + "." + $($tbl.name) + "_table.SQL"
$tbl.Script() > $k
}
#and repeat for each type of object e.g procs views etc
OR
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
#MAP NETWORK DRIVE
New-PSDrive -Name A -PSProvider FileSystem -Root TOSOMEUNCPATH
#REMOVE OLD COPIES
get-childitem A:\ -include *.sql -recurse | foreach ($_) {remove-item $_.fullname}
#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd-hh_mm
#SCRIPT
SL SQLSERVER:\SQL\SERVER\INSTANCE\DATABASES\DBNAME\Tables
dir | %{$_.Script() | Out-File "A:\FOLDER\Tables $timestamp.sql" -Append}
Tried the second script and got few errors:
A job step received an error at line 3 in a PowerShell script. The corresponding line is 'Add-PSSnapin SqlServerCmdletSnapin100'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Add-PSSnapin' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
A job step received an error at line 2 in a PowerShell script. The corresponding line is 'Add-PSSnapin SqlServerProviderSnapin100'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Add-PSSnapin' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
After removing those lines, got this:
A job step received an error at line 4 in a PowerShell script. The corresponding line is 'SL SQLSERVER:\SQL\(local)\DEFAULT\Databases\MyDb\Tables'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A positional parameter cannot be found that accepts argument ''. '. Process Exit Code -1. The step failed.
Ok, worked that out by removing those lines with PSSnapin commands altogether and using double quotes for the SL command:
SL SQLSERVER:\SQL\"(local)"\DEFAULT\Databases\"MyDB"\Tables
Where can I find the full list of object types for this command? It worked with Tables, Views, StoredProcedures, but did not work with Functions.
I will also need ForeignKeys, Indexes, Users, Assemlies etc.
October 19, 2011 at 2:02 am
Assemblies and Users are under at the same level as tables and storedprocedures
Indexes and FK's I have yet to find myself as once your in tables you cant go any lower.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply