-3

How to Backup and delete SQL Server database if the database created date is more than 3 months

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bharath Reddy Seelam
  • 1
  • 3
    http://stackoverflow.com/questions/18876078/list-all-the-databases-on-one-sql-server-in-the-order-they-were-created gets you close to identify the databases. http://stackoverflow.com/questions/122690/what-is-a-simple-command-line-program-or-script-to-backup-sql-server-databases shows you how to backup.... https://dba.stackexchange.com/questions/34264/how-to-force-drop-database-in-sql-server-2008/34265 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 May 01 '17 at 20:29

1 Answers1

0

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
user7593937
  • 545
  • 1
  • 5
  • 16