December 1, 2008 at 10:49 am
I have created a cursor based on an example that goes through and detaches all my archive databases shown below. However, I am now trying to detach these databases based on how they are named. Our archive process creates the databases with the date created appended to the end. For example, an archive created 12:15 AM last night (this morning) would be named Main_Application_Archive_20081201. Well, I need to detach databases that are X number of months old or older.
I have tried using substring to pull out the date and insert name and datepart into a temporary table and then use datediff to evaluate the dates but I keep getting errors (conversion errors among others) and I am frustrated because I am over my head at this point.
Any direction would be helpful and appreciated.
SQL to detach all archives
-- Declare variables to store database name returned by FETCH and the cmd.
DECLARE @dbname sysname, @cmd varchar(1000)
-- Declare a cursor to iterate through the list of databases
DECLARE detach_old_archives CURSOR FOR
SELECT name from master..sysdatabases where name like 'Main_Application_Archive_%'
-- Open the cursor
OPEN detach_old_archives
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM detach_old_archives INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
-- create the detach command for each database
SET @cmd = 'sp_detach_db '+@dbname+';'
-- run
EXEC(@cmd)
PRINT @cmd + 'Yes I did it.'
-- fetch the next database name
FETCH NEXT FROM detach_old_archives INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE detach_old_archives
DEALLOCATE detach_old_archives
December 1, 2008 at 11:16 am
What are the errors? The script looks OK here. Conversion errors usually mean you're not extracting the data correctly.
December 1, 2008 at 11:54 am
Made a few slight changes. If you have a test server, you might give the following a spin:
-- Declare variables to store database name returned by FETCH and the cmd.
DECLARE @dbname sysname, @cmd varchar(1000), @dbdate datetime;
-- Declare a cursor to iterate through the list of databases
DECLARE detach_old_archives CURSOR FOR
SELECT name from master.sys.databases where name like 'Main_Application_Archive_%'
-- Open the cursor
OPEN detach_old_archives
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM detach_old_archives INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
set @dbdate = right(@dbname, 8);
if datediff(dd, @dbdate, getdate()) > 30
begin
-- create the detach command for each database
SET @cmd = 'sp_detach_db '+@dbname+';'
-- run
EXEC(@cmd)
PRINT @cmd + 'Yes I did it.'
end
-- fetch the next database name
FETCH NEXT FROM detach_old_archives INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE detach_old_archives
DEALLOCATE detach_old_archives
December 1, 2008 at 11:56 am
Hey Steve!
I have found your site to be very helpful and always sign up wherever I work. Thank you!
Okay, so my bad syntax...
I am trying to put the database name and date portion into a temp table so I can do the datediff function in the next step. I realize I can't assign a variable in a select statement but I am not sure how to do what I am attempting to do.
declare @date datetime
select [name], @date = cast((substring(name,21,8)) as datetime)
into #temp
from master..sysdatabases
Here's how I was trying to insert the db name into the temp table which says "Main_Application_Archive_20081201" in not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
CREATE table #temp (
dbname varchar(100),
date varchar(100))
go
DECLARE @dbname sysname, @date varchar(100), @cmd varchar(1000)
DECLARE get_names_parse_date CURSOR FOR
SELECT name from master..sysdatabases where name like 'Main_Application_Archive_%'
OPEN get_names_parse_date
FETCH NEXT FROM get_names_parse_date INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'Insert into #temp('+'''dbname'+''','+'''date'+''') values ('+@dbname+')'
EXEC(@cmd)
PRINT @cmd
FETCH NEXT FROM get_names_parse_date INTO @dbname
END
CLOSE get_names_parse_date
DEALLOCATE get_names_parse_date
As you can tell, I am way off track.
Thank you!
Michael
December 1, 2008 at 12:00 pm
If you run this as a select without the CAST, what comes back. Chances are you're missing something there.
I might use CHARINDEX to find the right spot, maybe with a starting point the ensures you skip the first 2 underscores.
And thanks for the kind words.
December 1, 2008 at 12:07 pm
I modified it to look at months and it worked perfectly! Thank you so much to both of you!
So much simpler than where I was headed.
Here's the script I am now using:
-- Declare variables to store database name returned by FETCH and the cmd.
DECLARE @dbname sysname, @cmd varchar(1000), @dbdate datetime;
-- Declare a cursor to iterate through the list of databases
DECLARE detach_old_archives CURSOR FOR
SELECT name from master.sys.databases where name like 'Main_Application_Archive_%'
-- Open the cursor
OPEN detach_old_archives
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM detach_old_archives INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
set @dbdate = right(@dbname, 8);
if datediff(mm, @dbdate, getdate()) > 10
begin
-- create the detach command for each database
SET @cmd = 'sp_detach_db '+@dbname+';'
-- run
EXEC(@cmd)
PRINT @cmd + 'Yes I did it.'
end
-- fetch the next database name
FETCH NEXT FROM detach_old_archives INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE detach_old_archives
DEALLOCATE detach_old_archives
December 1, 2008 at 12:10 pm
I think I like my solution better. You seem to be making it harder than it really is. You can extract the date from the end of the database name directly and then test for exclusion at that point. Why go through the trouble of building and populating tables to identify what needs to be detached or not?
Edit: Should have waited a few more minutes! :w00t:
December 1, 2008 at 12:16 pm
Oops, sorry for the confusion! Yes, I think your solution is much, much better than my original thought and I am using it. All I changed was for the script to look at months instead of days.
December 1, 2008 at 12:20 pm
I was being cautious, datediff(mm, '2008-11-30', '2008-12-01') returns 1. Since it looks like you are going back over 10 months, not too much to worry about (I hope).
December 1, 2008 at 12:28 pm
Ahh, that makes sense. In this case, that is okay (phew!). Thank you for the follow up explanation.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply