How to Backup and delete SQL Server database if the database created date is more than 3 months
-
3stackoverflow.com/questions/18876078/… gets you close to identify the databases. stackoverflow.com/questions/122690/… shows you how to backup.... dba.stackexchange.com/questions/34264/… shows you how to drop.... Please spend a few minutes searching or break the problem down and search for the components and put it together!xQbert– xQbert2017-05-01 20:29:17 +00:00Commented May 1, 2017 at 20:29
Add a comment
|
1 Answer
Be careful using something like this, this script could get you in to trouble. Notice I am only selecting the @tsql parameter, I commented out the EXEC so you can see what would be executing first.
/* Create a cursor to iterate through the databases you want to backup and delete */
DECLARE @tsql nvarchar(max)
DECLARE @dbname varchar(500)
DECLARE MyCursor CURSOR STATIC FORWARD_ONLY
FOR
SELECT [name]
FROM sys.databases
WHERE create_date < DATEADD (M, -3, GETDATE())
AND [name] NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN MyCursor
WHILE (1=1)
BEGIN
DECLARE
@Date varchar(20) = GETDATE()
FETCH NEXT FROM MyCursor INTO @dbname
IF @@FETCH_STATUS <> 0 BREAK
SET @tsql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = N''S:\Backups\' + @dbname + ' ' + @Date + '.bak'' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
SELECT @tsql;
-- EXEC sp_executesql @tsql
SET @tsql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [' + @dbname + ']'
SELECT @tsql
-- EXEC sp_executesql @tsql
END
CLOSE MyCursor;
DEALLOCATE MyCursor;
GO
1 Comment
Bharath Reddy Seelam
Awesome, Thank you so much.