Restore database on different database files

[jbrajgor]

Hi,

I need to restore a database to different server.
Orignal .mdf is 16 G, Now on the new serever I have 16 G adn 12 G of drive space available.

Is there a way i can split the data of 1 mdf over to 2 or 3 data files on the new server ?

Thanks in advance.

[rmiao]

Create a db with two files on target server then try restore from backup. If doesn't work, use dts to transfer the db.

[jbrajgor]

Thanks Rmio,

But I tried this and it didn't work. I know DTS is an option. but I would like to avoid that route as this is moderate size database.

If it was oracle, I could have created different tablespaces and have loaded them with Schema level import.

But I am not sure how to handle this here...Any new idea ?

[Stephen]

SQL Server insists that you restore to the same number of files as when the backup was taken. Whilst you can change the location of the files when you do the restore, you cannot split one file into 2 files.

Could try creating a second data file on the source server and using DBCC SHRINKFILE to move data around. I am not sure if this will work though. And if it does, it is quite likely to take many hours to do it.

DTS might well be a good option but it will take quite a while to copy 16 Gig though