question

MNR avatar image
0 Votes"
MNR asked SeeyaXi-msft commented

powershell script to get database list with no FULL backup or FULL backup older than 7 days in sql server

Please provide powershell script to get database list with no FULL backup or FULL backup older than 7 days in sql server

sql-server-general
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We are not here to provide homework :-)

Please explain what you tried to do and what is not working well or missing.

This can be gain using simple T-SQL queries. You can query the sys.databases and the msdb.dbo.backupset to get the information. Do you know how to execute queries using PowerShell?

0 Votes 0 ·

Thank you for your reply.
You are correct and I had required T-SQL query , but not in PowerShell . Please help me if you have any PowerShell script to get details

0 Votes 0 ·

@NavaneeshwarReddy-8681 , if you already have the SQL script you need, add the SQL to your question and we can show you how to execute it via PowerShell and display the results.

0 Votes 0 ·
Show more comments

Hi @NavaneeshwarReddy-8681 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

Hi,

You are correct and I had required T-SQL query , but not in PowerShell . Please help me if you have any PowerShell script to get details

According to your new information in the comment you know hw to get the information via queries.

This mean that you only need to learn how to execute queries from PowerShell.

This is done using the command Invoke-Sqlcmd

You can get more information in the relevant documentation here:

https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

Note! This command is a utility which is part of the SqlServer Module. It is not installed by default. Therefore, you might encounter the following error message:

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program.

You can install the module using the command Install-Module sqlserver

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @NavaneeshwarReddy-8681,

This link may could give you some greater ideas about sql scripts:
https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

How to execute SQL Script using windows powershell

Yes. You can use invoke-sqlcmd.
Here is a similar thread: https://social.msdn.microsoft.com/Forums/en-US/b811c1b7-1fda-4027-ab97-9ecb1fabff36/how-to-execute-sql-script-using-windows-powershellusing-invokesqlcmd-or-any-if?forum=sqlsecurity


Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnthonyAxlen-1458 avatar image
0 Votes"
AnthonyAxlen-1458 answered AnthonyAxlen-1458 edited

Hello,

I agree with the previous responses, that usage of Invoke-Sqlcmd cmdlet (part of SQLPS module) helps you with direct SQL query (with -Query param) or query saved in file (with -InputFile param).

The second option I would recommend is DBAtools PS module with Get-DBABackupHistory function - description with examples of usage are well presented on docs.dbatools.io.

Thank you.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.