0

I've got a client that has approximately 100 databases across 10 servers. We're attempting to create a standardized process for removing old and unnecessary databases. In the process, we'd like a step where we can temporarily 'archive' a database in case it is still in use. This way, we can easily tell if someone is attempting to access it but can not, and if they are we can turn it back on. Otherwise, we'll remove it.

We have backups, so obviously one option would be to create the backup and delete the database. Then, if it needs to be restored, restore from a backup. However, my client is looking for a simpler method if at all possible.

I've googled for awhile and I can't find any method of temporarily archiving a database in SQL Server. So, I was considering recommending that the client rename the database for approximate a week, and then, depending on the response from clients, delete the database. This method would still include the backups.

My question boils down to: What are the potential pitfalls with temporarily renaming a database? Does SQL Server remove any logins or authentication information when a database is renamed and then renamed back? Does anyone have a more standardized recommendation for this process, or a direction to point me to get me researching further?

Thank you!

3

To answer your questions first:

What are the potential pitfalls with temporarily renaming a database?

At this scale, it'd be bothersome and just potentially messy to be renaming this many databases multiple times in the case some are needed. It may be a pain cutting off all connections when re-naming and could confuse other users as well.

Does MSSQL remove any logins or authentication information when a database is renamed and then renamed back?

Nope! Not an issue.

Does anyone have a more standardized recommendation for this process, or a direction to point me to get me researching further?

In this case, I'd probably just take the databases OFFLINE temporarily to see if they are needed.

You can do this via the GUI by right-clicking a database, Task -> Take Offline, or via TSQL:

ALTER DATABASE [dbname] SET OFFLINE;

When a database is offline, all connections are closed and no new connections can be made to it. It's quick to bring it back online if required:

ALTER DATABASE [dbname] SET ONLINE;

Plus, when the database is offline it's using less resources than an online database, so it may be better for performance in the mean time as well.

  • Superb! Exactly what I was looking for. – Mikel Bitson Nov 11 '15 at 18:35
  • I have one more small question. If you think this is a reasonable new question for this stackexchange network I'll go ahead and post a new question. I'm not sure if this is common knowledge, but I can't find anything when searching.. When a Maintenance Plan runs a full DB backup, does it backup the logins that database is linked to as well, or will I need to run an export on the logins when removing a database so that I can restore that database with the same authentication credentials? – Mikel Bitson Nov 11 '15 at 18:44
  • 1
    A database backup will include the database users. When restoring to the same server it should be fine, if it's a new server then make sure that the server logins and database users are mapped. – LowlyDBA Nov 11 '15 at 18:46
  • You're the man! Cheers! – Mikel Bitson Nov 11 '15 at 18:46
0

Normally, I do this:

I Check databases that wasn't used since the last server restart:

select [name] from sys.databases 
    where database_id > 4
    AND [name] NOT IN 
        (select DB_NAME(database_id) 
            from sys.dm_db_index_usage_stats
            where coalesce(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970') > 
        (select login_time from sys.sysprocesses where spid = 1)
)

You can check the last server restart with:

select sqlserver_start_time from sys.dm_os_sys_info

Or, you can check the last time the database was used:

SELECT name, last_access =(select X1= max(LA.xx)
from ( select xx =
max(last_user_seek)
where max(last_user_seek)is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan)is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx =max(last_user_update)
where max(last_user_update) is not null) LA)
FROM master.dbo.sysdatabases sd 
left outer join sys.dm_db_index_usage_stats s 
on sd.dbid= s.database_id 
group by sd.name
order by 1

With this informations, I rename the databse with a simple '_' in front of the name:

USE master;
GO
ALTER DATABASE YourDatabaseName
Modify Name = _YourDatabaseName ;
GO

If I don't get calls, I just drop the database. Just make a backup of the database and save it for x months.

EDIT1:

You can kill all connections of a database with this:

-- Create the sql to kill the active database connections  
declare @execSql varchar(1000), @databaseName varchar(100)  
-- Set the database name for which to kill the connections  
set @databaseName = 'YourDatabaseName'  

set @execSql = ''   
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + char(10)  
from    master.dbo.sysprocesses  
where   db_name(dbid) = @databaseName  
     and  
     DBID <> 0  
     and  
     spid <> @@spid  
print(@execSql) --use exec to execute it 
GO
-------------------------------------------------------------------------

alter database YourDatabaseName 
    set SINGLE_USER with rollback immediate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.