2

I have to restore a SQL database that is just over 5 TB; it is going from a single drive that is 7 TB, to another server that has split drives C:\ 4 TB and D:\ 3 TB and also has E:\Bak which is only 0.5 TB

Can this be done Without adding space?

If i split the full bak into parts can I copy and restore one at a time?

There are multiple data files and only one log

  • 1
    How big is your datafile in your source database? – Stijn Wynants Jan 9 '18 at 14:52
  • No, you cannot take a stripped backup and only copy one file at a time to restore back to SQL Server. SQL Server has to read from all the stripped backup files. – user507 Jan 9 '18 at 15:42
  • Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Every relational database is a "SQL database" – a_horse_with_no_name Jan 9 '18 at 16:22
1

You can backup to multiple files and restore these files with backup compression these files should be a lot smaller then your databasesize.

Example

BACKUP DATABASE [YourDatabase] TO  
DISK = N'\\YourDriveOrNetworkFolder\YourFOlder\MNGDB_2.bak',  
DISK = N'\\YourDriveOrNetworkFolder\YourFOlder\MNGDB_1.bak',  
DISK = N'\\YourDriveOrNetworkFolder\YourFOlder\MNGDB_3.bak',  
DISK = N'\\YourDriveOrNetworkFolder\YourFOlder\MNGDB_4.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 1
GO

You cannot restore one file at a time, you will need to have all files.

You can however restore from multiple backupfiles (if you have taken a backup to multiple files) which can be located on multiple disks or network locations.

Example

RESTORE DATABASE [YourDatabase] FROM  
DISK = N'\\YourDriveOrNetworkPath\YourFOlder\MNGDB_1.bak',  
DISK = N'\\YourDriveOrNetworkPath\YourFOlder\MNGDB_2.bak',  
DISK = N'\\YourDriveOrNetworkPath\YourFOlder\MNGDB_3.bak',  
DISK = N'\\YourDriveOrNetworkPath\YourFOlder\MNGDB_4.bak' 
WITH  FILE = 1,  
MOVE N'YourFile' TO N'\\YourDrive\YourFolder\YourFile.mdf',  
MOVE N'YourLogFile' TO N'\\YourDrive\YourFolder\YourFile.ldf',  
NOUNLOAD,  STATS = 5

It all depends on the size of your backupfiles & the size needed by the datafiles & logfiles to be able to say if it is possible or not.

| improve this answer | |
  • can I run restore TO 'C:\MyDB , wait for it to complete, delete that part of bak and then copy over next part and run retore to E:\MyDB... like that?, – user3191894 Jan 9 '18 at 16:06
  • You have to execute the statements with all .bak files at the same time, they however do not need to be on the same server. – Stijn Wynants Jan 9 '18 at 16:11
  • 1
    Thanks I had time to test an got it working, this was what I was looking for - "You cannot restore one file at a time, you will need to have all files." – user3191894 Jan 11 '18 at 14:24
2

Yes you can achieve that based on the size of the data files and log. You can do a restore with a move option to split the locations of the database files (mdf/ndf and ldf) to the mentioned drives based on the size of the files and available space on the drives. Sample script will perform this task:

RESTORE DATABASE [BigDatabase7TB] FROM disk = 'C:\backup.bak'    
WITH NORECOVERY, STATS=10,   
    MOVE 'BigDatabase7TB_Data' --logical file name of primary db      
        TO 'C:\MyData\BigDatabase7TB.mdf', --desired data file    
    MOVE 'BigDatabase7TB_Data1' --logical file name of primary db
        TO 'D:\MyData\BigDatabase7TB1.mdf', --desired data file   
    MOVE 'BigDatabase7TB_Data2' --logical file name of primary db     
        TO 'E:\MyData\BigDatabase7TB2.mdf', --desired data file     
    MOVE 'BigDatabase7TB_Log'
        TO 'F:\MyLog\BigDatabase7TB.ldf'  
GO

NOTE: You will face issues if any of the database file sizes is bigger than available space on any of the drives. So you probably want to pay attention to the sizes of the files first and match that to the drives based of available space.

| improve this answer | |
  • Can I run TO 'C:\MyDB – user3191894 Jan 9 '18 at 16:04
  • Yes you can change the file location to your desired location. You script will now look like this: RESTORE.... MOVE.... TO 'C:\MyDB\BigDatabase7TB.mdf'.... – samosql Jan 9 '18 at 16:08

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.