1

I am trying to combine this SQL backup script/query that I have with my power shell script and I'm not sure how to convert it as I don't know much about SQL only Powershell. I have been trying to use invoke-sqlcmd before every line in the script, but I don't think that's how you do it. I don't fully understand the syntax of invoke-sqlcmdMicrsoft was not helpful. This is the SQL database backup script I need to use: 

DECLARE @name VARCHAR(50) -- database name  

DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name


-- specify database backup directory
SET @path = 'F:\Backups\'  


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 


DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('dbname','dbname','dbname','dbname','dbname','dbname')  -- exclude these databases


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName
       WITH COMPRESSION


       FETCH NEXT FROM db_cursor INTO @name   
END   


CLOSE db_cursor   
DEALLOCATE db_cursor

Is it correct to just add invoke-sqlcmd in the front of every line of sql query? Also whatever solution there is to this needs to be compatible with Windows Server 2008 and up. I thought it might be as easy as just putting the whole script in quotes with invoke-sqlcmd, but I doubt it. Whenever I google this the answers are way too complex and don't really explain exactly how the SQL ties into Powershell. They just kind of assume you know. The only field in this script that needs to change is the path when you run it on SQL studio.

Improve this question

3 Answers 3

Reset to default
1

Sqlcmd is designed to be replacement for sqlcmd command-line tool.

Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.

Therefore you will execute your commands in one go. Either using external file or multi-line SQL string. See below for examples.

External File Example

Save your sql backup script to another file like backupDaily.sql. After that use following syntax. 

Invoke-Sqlcmd -InputFile "C:\backupScripts\backupDaily.sql" | Out-File -filePath "C:\backupScripts\backupDailyCmd.rpt"

Command is taken from Microsoft documentation.

The good thing is you can try this sql file in Sql Server Management Studio to see if it works.

Multi-line SQL String Example

$SQL_QUERY = @"
  YOUR QUERY HERE.
"@
Invoke-Sqlcmd -Query $SQL_QUERY -ServerInstance "MyComputer\MyInstance"

You may easily use string substitution here. 

$name = 'atilla'
$SQL_QUERY = @"
  SELECT * FROM Employees WHERE NAME LIKE $($name)
"@

Invoke-Sqlcmd -Query $SQL_QUERY -ServerInstance "MyComputer\MyInstance"
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Im trying to avoid referencing another file to get this accomplished i have to embed the code into the script i already have. otherwise yes this would work fine im sure.
that looks like exactly what i was looking for, ill test it and get back to you thanks!
one other question is there a way to put a variable from powershell into that sql query or no?
It may be possible to use sqlcmd variable substitution but using powershell to replace string in variables would be more easy.
@Dpw808 added example for string substitution.
|
0

I wouldn't use powershell for doing a backup.

I would recommend using a SQL stored procedure to schedule a job.

   USE msdb ;
GO
-- creates a schedule named NightlyJobs. 
-- Jobs that use this schedule execute every day when the time on the server is 01:00. 
EXEC sp_add_schedule
    @schedule_name = N'NightlyJobs' ,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 010000 ;
GO
-- attaches the schedule to the job BackupDatabase
EXEC sp_attach_schedule
   @job_name = N'BackupDatabase',
   @schedule_name = N'NightlyJobs' ;
GO

Hopefully this link will help. https://msdn.microsoft.com/en-GB/library/ms191439.aspx

Improve this answer

1 Comment

i cant do that as i need to use this sporadically in different client environments i need it to be on demand not scheduled.
0

You can use the sqlcmd to backup a database. for the default instance

SqlCmd -E -S MyServer –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

for a named instance

SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”
Improve this answer

6 Comments

that command works in versions of Powershell that aren't updated on windows server 2008? that seems too good to be true lol.
the commands above will work on SQL 2005 and higher (any edition). For SQL 2000 and earlier, replace ‘SqlCmd’ with ‘oSql’.
you can also restore like so:
SqlCmd -E -S MyServer –Q “RESTORE DATABASE [MyDB] FROM DISK=’D:BackupsMyDB.bak'”
where do i input the name of the database? where it says "MyDB"?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.