• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

backup

Database Delete vs. Detach vs. Offline

May 31, 2017 by Kevin3NF Leave a Comment

If you haven’t been asked to Delete a database in your DBA career…its just a matter of time:

“We don’t need database XYZ anymore…delete it.” — Some well meaning manager

Stop right now, before blindly following an order like that.

First…there are some questions to ask:

  • What environment is this? (Prod, Dev, etc.)
  • Are you sure we don’t need it?
  • Has anyone verified that?
  • When was it last used?
  • Did something replace it?
  • Is it backed up?

As a DBA, any time anyone asks you to delete anything, from data to an entire database, you need to question it.  Not doing so can be a CLM/RGE combo!  (Career Limiting Move/Resumé Generating Event)

The request may be perfectly valid, but in my experience most people that ask for a database to be deleted use the term to mean “I don’t want to see that database in SSMS anymore” or “I don’t want it on Server A, put it on Server B”, with very little regard for impact to other teams or applications.

There are three main ways to take a database “out of service”:

  • Take it Offline
  • Detach it
  • Delete it

Each has its own considerations and each is the proper choice in different circumstances.  I am going to walk you through each one, from least risky to most, in that order.

Offline:

Takes the database offline, but still visible in SSMS.  Cannot be accessed by applications, backups, etc.   Can be brought back online relatively quickly.

My list of databases before starting this post…we’ll be messing with the Rename2222 database (nothing in it…came from a rename script I was writing/testing):

Right-click the database in question, Tasks, Offline:

 

Dialog Box you get after clicking “Take Offline”:

Note 1: You do not get the below dialog in SSMS 2014 (and possibly earlier)…this was done using SSMS 2016.
Note 2:  If you click the Script button, it will take the database offline in SSMS2016, instead of generating a script.  I’m told this is resolved in SSMS 2017.

 

Results in SSMS after Offline Operation:

Upside to Offline method: Database is still in place,  visible in SSMS, Record in master database is still there, relatively simple.

Downside to Offline: May cause SQL Monitoring tools to throw alerts and create tickets, files are not in use by SQL Server, can be deleted.

Detach:

Removes the database from SSMS, deletes its meta information in the master database, but leaves the physical files intact on the drive.

The detach menu:

The detach dialog:

Choose “drop connections” here…if not, the detach may fail.  Or, leave it unchecked as a test to see if anyone is using it

After Detaching:

Note that the MDF/LDF files are still right where they were.  SQL Server no longer has a handle on them:

You can re-attach fairly quickly should the need arise (i.e. user comes screaming at you 5 minutes after the detach):

Right-click “Databases”, select Attach

 

Click ‘Add’ and browse for the .MDF file….should find and include the .LDF file on its own

 

Click ‘OK’ to attach

Upside to Detach method: Files are still in place, re-attach is fairly straightforward, Monitoring tools will generally not error. Can save your job by not having deleted a database someone else needed.

Downside to Detach: Files not manually deleted take up disk space…set reminders to delete after some time period.

Delete:

Drops the database, its meta data in Master, AND its associated physical files…you cannot reverse a Delete (DROP Database).

This is one of those “Stop right here” moments.  Before you Delete a database, make very sure you are following protocols.   Make sure you have a recent FULL backup:

 

If not, run one.  The simplest possible command to backup a database:

 -- backs up to default location

Backup Database Rename2222 
To Disk = 'Rename2222.bak'

Better:

-- backs up to specified location 
-- with date included in the file name:

Backup Database Rename2222 
To Disk = 'c:\MyBackups\Rename2222_20170531.bak' 

Once you have a backup, you also should get a ticket, change control, or at the very minimum an email from the requester with approval for the delete.  Depends on your process.   Especially important if this is a production environment.   This can save your job.  If you Delete a database that a system depends on and cause an outage…whoever told you to do may claim they were not involved.

To delete is way too simple:

Deleting backup/restore history is up to you.  Drop all connections to force everyone out.  
If anyone is connected, should you be deleting?

Remember…once you Delete a database…its gone.   The ONLY ‘undo’ is to restore from Backup.   You can call Microsoft, but they will tell you the same thing.  Worst case, you can spend tens of thousands of dollars on a data recovery service, assuming you can take the server down and nothing overwrote the space the the files were in on the drives.

EDIT: If you take a database into “Offline” state, then delete it – the physical files will still be on the drive. Not sure if this is accurate in ALL SQL Server versions, so detach first.

Wrap up:

Almost everywhere I’ve worked, Detach with a 2-4 week follow up to delete the files was the best choice.   But beware that some systems may only be useful for Quarterly or Annual reporting.  In an ideal situation, you have a complete inventory of every database on every server from Production down to Dev and know exactly why they are there, as well as who the stakeholders are for each one.  If you don’t have this for the O/S, SQL and DB level info, start now.

Thanks for reading!

Kevin3NF

My Pluralsight course for new DBAs

Follow @Dallas_DBAs

Filed Under: Accidental DBA, backup, Beginner, EntryLevel

SQL Saturday Houston, 2017

April 27, 2017 by Kevin3NF Leave a Comment

I will be presenting my session “Backups for non-DBAs…the Why, not the How” at SQL Saturday Houston on June 17.

Abstract:

Join me and get some new perspective on what your DBA is doing, and why!

Targeted at the non-DBA such as BI/DW folks, Application Developers, Managers, and System Admins, this session will go over the reasons we back up our databases, our systems, etc. Considerations such as Single points of failure, High Availability/Disaster recovery, Business Continuity and others will be discussed in this interactive conversation. It will be conversation heavy, with supporting slides to download, and one Database backup demo at the end if time permits.

Everyone involved in a technical role needs to at least know that the things they have created will be recoverable in the event of a disaster, or even just an “oops” moment. The CIO/CTO should know how long critical systems will be down when bad things happen.

Backups are everyone’s responsibility…whether asking the right questions or implementing the process.

If you come on Friday, there is a pre-conference full day of training on Performance Tuning by Brent Ozar…$199 as of this post for a full day of awesomesauce. Or, my friend Tim Mitchell (b|t) will teach you how to build better SSIS packages for $129.

Hope to see you there!

Kevin3NF

Filed Under: Accidental DBA, backup, Beginner, Speaking, SQLSaturday

T-SQL Tuesday: Connect item – Last Log Backup Time

January 10, 2017 by Kevin3NF 1 Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Then anyone who is interested blogs on that topic on the second Tuesday. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by Brent Ozar (b/t), who has challenged us to find our favorite Bug report or feature request on MS Connect and write about it.

I don’t have a lot of experience with Connect, other than landing there from Google a few times while researching stuff.   I’m pretty sure I’ve never filed a report or request.  So, I just went looking through the latest requests and found one concerning “Check the time for the latest log backup” from none other than Ola Hallengren (w).

Ola’s feature request is:

I would like to ask for a way to check when the last log backup was performed.

His reasoning for the request:

This enables you to have a backup strategy with more frequent transaction log backups when there is a lot of activity in the database.

And his proposed solution:

Add a new column to sys.dm_db_log_space_usage or sys.database_recovery_status called LastLogBackupTime.

I LOVE this idea…back up the T-log more frequently during busy times, less often during off hours.  At my current client, there is almost nothing happening outside of a 12 hour workday window, so this would be perfect here.

Now, I am possibly misunderstanding Ola’s request or the intent…and that’s ok.  This query from the msdb..backupset table already contains this info via a relatively short amount of code:

Use MSDB
go

Select
    [Database_Name] as [Database Name],
    DB_id([database_name]) as [DB ID],
    Max(backup_Start_date) as [Last log backup]
From
    msdb..backupset bs
Where 1=1
    and db_id([database_name]) is not null
    and type = 'L'
Group by 
    [database_name]
Order By 
    Max(backup_start_date)

This returns a result set of:

So…the info requested can already be gathered.  There may very well be other ways to do this, or querying this table may be deprecated.  It seems that Ola is looking for a cleaner solution by adding to an existing System View in MSDB in order to more easily tie it to log size.

I do know and respect Ola’s maintenance scripts, so I have posted a comment on the Connect item asking for some clarification. And, I have voted it up.  I encourage YOU to do the same….as this simple request is probably going to wind up in his scripts 🙂

Thanks for reading!

Kevin3NF

Filed Under: backup, TSQL2sday

SQL Injection sucks…

November 11, 2008 by Kevin3NF Leave a Comment

I hate it when that happens…

A good friend of mine has a fairly simple community site, with 30K members, about 7K active.

SQL Server 2000, ASP Classic. DB and site on separate servers.

KerBam! SQL Injected last Monday. Major trashing of data.

Problem 2: No backup since October 2, as the SQL Agent password had changed so the Agent wasn’t started.

Problem 3: Started the agent and it deleted the last full backup because it was older than 4 weeks.

SO: I get the call for help.

Time passes.

More time passes.

After a dozen uploads and downloads of .mdf/.ldf and backup files (from August), I am able to recover much of the data using the fantastic tools from Red-Gate software:

Log Rescue: Identified what got injected, into where and when
SQL Compare: enabled me to create a schema script to replace the relationships I had to remove to fix the data
SQL Data Compare: Helped me replace the trash data with what it looked like in August.

Also, a shout-out to Narayana Vyas Kondreddi for his Search and Replace code that at least got the bad URL out of the data we couldn’t fix.

The site is back up in read-only, and my buddy is reviewing all the code one page at a time.

Lessons learned:
Validate your inputs!
Back up your data, and verify it!

Not only does this one get fries, he gets 10+ hours of recovery effort at no charge, just for having a really cool site I want to see come back up 🙂

Kevin3NF

Filed Under: backup, red gate, sql injection

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...