Hot answers tagged smo
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 ...
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
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 ...
2
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 ...
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 ...
2
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.
...
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?
1
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
