SQL snippets

sql

Copy all rows from one table to another

insert into stateNew ([state], [abbrev])
select * from States

Getting identity column from last row added with a stored procedure

Stored procedure:

CREATE PROCEDURE [dbo].[qInsert]
    @Id int,
    @Name nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO [dbo].[artist]
    (
        [Name]
    )
    VALUES(
        @Name
    );
    RETURN SCOPE_IDENTITY();
END
GO

EXEC @rp = qInsert 0, 'Bobby Fuller Four'
SELECT @rp as 'identify'

Backup SQL Server with PowerShell

To work with SQL Server from PowerShell, install the PowerShell SqlServer module:

Install-Module -Name SqlServer

Do this with an admin session if you want to install the module for all updateLanguageServiceSourceFile.

get-sqldatabase -serverinstance WIN10RP-1809                | where { $_.Name -ne 'tempdb' } | backup-sqldatabase
get-sqldatabase -serverinstance "WIN10RP-1809\MSSQLSERVER01" | where { $_.Name -ne 'tempdb' } | backup-sqldatabase

Set SQL Server instance log on account (necessary do batch back operations)

Open Windows Services applet
Find SQL Server (instance-name)
Right-click and select “properties”
Click the Log On tab
Select the “Local System Account” radio button

Run a PowerShell script at Windows shutdown

Launch `gpedit.msc'

osql.exe -E -Q “BACKUP DATABASE crm TO DISK=‘C:\users\thumb\documents\sql-server-backup\db.bak’ WITH FORMAT”

C:\WINDOWS\System32\GroupPolicy\Machine\Scripts\Shutdown\backup-sql-server.ps1