I want to make a copy of a largish SQL 2008 database (1TB). I only have 1.9TB of disk space free so I don't have enough to create a backup of the database and restore it into another.

Are there any other techniques I can use? How to I do it quickly as possible?

Is it ok to just make a copy of the MDF, LDF and NDF files - rename them and then just attach the database?

  • If you db size is 1TB and you have 1.9 TB free, why can't you create a copy on the same server. – Ash Burlaczenko Jun 12 '11 at 9:50
  • You have normal backups, right? – gbn Jun 12 '11 at 9:52
  • @Ash that is what I am asking, "Is it ok to just make a copy of the MDF, LDF and NDF files"? – Dan Jun 12 '11 at 9:54

Yes. You can detach, copy the .ldf, mdf and .ndf files to another location and attach them.

Are you using backup compression?

Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup.

| improve this answer | |
  • Thanks mitch, unfortunately we're not using R2. – Dan Jun 12 '11 at 9:46

Use a tool such as VS2010 Premium or SqlDelta that supports schema and data comparison and migration. I've used it to create a complete copy of database, when I haven't had possibilities to make and access a disk based backup.

| improve this answer | |

Not quite sure if you're saying "I don't have backups" but...

As a workaround:

  • Plug in an external 2TB backup drive for temporary backup space
  • Restore to a compressed folder: this will run like a dog though


  • Do you not separate data, log and backups onto different volumes?
  • How do you manage normal backups?
  • Is the backup 1TB (eg 1TB data) or is the DB 1TB on disk?
| improve this answer | |
  • I don't have a back up. And I dont have enough space to store a back up and the copy of the database. Data and logs are on separate volumes. – Dan Jun 12 '11 at 9:58
  • 1
    @Dan: is this production? – gbn Jun 12 '11 at 10:06
  • No its not production. – Dan Jun 12 '11 at 10:25

It sounds like you can just use the database copy wizard. Just use the same server for the source and destination.


| improve this answer | |

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.