Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 12 '11 at 14:02

This question came from our site for professional and enthusiast programmers.

    
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
add comment

4 Answers 4

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

http://msdn.microsoft.com/en-us/library/ms188664.aspx

share|improve this answer
add comment

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

Notes:

  • 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?
share|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
add comment

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.

share|improve this answer
add comment

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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