Tag Info

Hot answers tagged

10

Shouldn't be an issue, assuming your I/O subsystem can keep up. ("Different locations" may mean different things to different people - it's not just about drive letters.)


7

Try this $db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};


5

You cannot loop through objects in the way you have coded. Here's the working code: Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; $ServerName = 'xyzabc123' $DatabaseName = 'test1' $TableName = 'main' $TableSchemaName = 'dbo' $Server = New-Object ...


5

There's nothing built-in from the command line. If you have Red Gate SQL Compare you can do it: sqlcompare /s1:MySQLInstance /db1:MyDB /mkscr:MyDB_Schema /q SSMS scripting functions are just wrappers for SMO. I know you mention it, but you could write a powershell script to use SMO. This is adapted from code found on this Simple Talk post. ...


4

The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results: $assemblies = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } I'm not sure whether you need to use the owner property in the filter instead or ignore ownership entirely.


4

To just feed off of squillman's answer this is to show a sample of what SQLPS can do for you...You can browse each "directory" under the database and just do a get-member -MemberType Method, looking for Script(). Most of the directories have it I believe. Add-PSSnapin *SQL* # Note my hostname of the server is "SQLSERVER" # To show object names to be ...


4

I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine. #SQL 2008 R2 #add-type -AssemblyName ...


3

I wrote a command line utility named schemazen that does this. It's much faster than scripting from management studio and it's free and open source. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database from scripts run: schemazen.exe create --server localhost --database db ...


3

You can use the SQL Server Database Publishing Wizard. If you don't have it installed then you can grab version 1.2 from here The installer doesn't give any indication that it has installed but if you open up a command window and navigate to: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2 the SqlPubWiz.exe should be there. You can ...


3

If you are running your backups as per the posted example code, they will not overlap. An EXEC() statement will complete before the next statement is executed, which you can demonstrate easily: DECLARE @SQL VARCHAR(8000); SET @SQL = 'WAITFOR DELAY ''00:00:10:00'''; SELECT GETDATE() AS [Start] EXEC (@SQL); SELECT GETDATE() AS [End] BACKUP, as with all ...


3

One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name. You could either do this by accessing the Server.ConnectionContext property: $ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;" $SqlServer = ...


3

Remmber you need the following assemblies: Microsoft.SqlServer.ConnectionInfo Microsoft.SqlServer.Management.Sdk.Sfc Microsoft.SqlServer.Smo Microsoft.SqlServer.SmoExtended More info http://technet.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx using System; using System.IO; namespace ConsoleApplication2 { using ...


2

Read up here before you shrink your log file. You dont have to change the recovery model ... Update: you can use Invoke-sqlcmd cmdlet. eg. $query = "DBCC SHRINKFILE(db1_log)" Invoke-Sqlcmd -ServerInstance $instanceName -Query $query EDIT: Below is an excerpt from Powershell V3 cookbook ... Not all DBCC commands are wrapped in SMO methods. For other ...


1

You can only get data from a SQL Server that you are logged into. The metadata of table definitions, stored procedures, and so forth is also data. So, your login through the linked server must be successful. This can be done by granting your login rights on the other server. Or, the security of the linked server could be through a specific login that has ...


1

@elijah, SMO does have function to shrink file. The PowerShell script below shows the log.shrink method is used to shrink ONLY the log file. The shrink with default or truncateonly option work for me with full recovery model. [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null $server = Read-Host 'Enter your SQL Server ...


1

I'm assuming Microsoft SQL Server here based on the sample code. There is no need to include a WAITFOR statement in the middle of the process. Also you don't need to use dynamic SQL. The BACKUP DATABASE statement accepts variables for the database name. When you are seeing the delays what is the wait type of the spid running the backup?



Only top voted, non community-wiki answers of a minimum length are eligible