Creating a copy of a large database

Question

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. — , Jun 12 '11 at 09:50
@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 09:54

score 4 · Answer 1 · answered Jun 12 '11 at 09:36

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.

answered Jun 12 '11 at 09:36

Mitch Wheat

466
3
10

Thanks mitch, unfortunately we're not using R2. – Dan Jun 12 '11 at 09:46

score 1 · Answer 2 · answered Jun 12 '11 at 09:43

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.

answered Jun 12 '11 at 09:43

Anders Abel

569
3
7

score 1 · Answer 3 · answered Jun 12 '11 at 09:54

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?

answered Jun 12 '11 at 09:54

gbn

6,079
1
18
21

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 09:58
1

@Dan: is this production? – gbn Jun 12 '11 at 10:06
No its not production. – Dan Jun 12 '11 at 10:25

score 0 · Answer 4 · answered Jun 13 '11 at 02:14

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

answered Jun 13 '11 at 02:14

John

101

Creating a copy of a large database

4 Answers4