2

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?

3
  • 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
    Commented Jun 12, 2011 at 9:50
  • You have normal backups, right?
    – gbn
    Commented Jun 12, 2011 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
    Commented Jun 12, 2011 at 9:54

4 Answers 4

4

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.

1
  • Thanks mitch, unfortunately we're not using R2.
    – Dan
    Commented Jun 12, 2011 at 9:46
1

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.

1

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?
3
  • 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
    Commented Jun 12, 2011 at 9:58
  • 1
    @Dan: is this production?
    – gbn
    Commented Jun 12, 2011 at 10:06
  • No its not production.
    – Dan
    Commented Jun 12, 2011 at 10:25
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .