SQL Server 2008 R2 – How to Temporarily Archive Databases

sql serversql-server-2008-r2

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!

Best Answer

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.

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';

Sql-server – Full and differential restore with two backup files – SQL Server 2008

You will need to set up a separate backup device for the FULL and the DIFF. When you create the Backup Device it is a single file. You can't save a FULL and a DIFF in the same Backup Device.

More information about backup devices can be found here: http://msdn.microsoft.com/en-us/library/ms179313.aspx

If you are trying to restore the database over an existing database and want to replace the data you will need to use the WITH REPLACE option in the restore. More information can be found here: http://msdn.microsoft.com/en-us/library/ms186858.aspx

REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.

The REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:

Restoring over an existing database with a backup taken of another database.

With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.

Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.

With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

Overwriting existing files.

For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.

Best Answer

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

Sql-server – Full and differential restore with two backup files – SQL Server 2008

Related Question