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
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.
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.
