December 17, 2014 at 2:02 pm
Trying to restore 3 BAK files to multiple MDF's, NDF's on separate disk drives to accommodate load balancing of I/O subsystem.
We have a multi-TB mdf on a single drive ( G: ) I successfully backed up that DB to 3 BAK files:
[font="Courier New"]DECLARE @str01 as nvarchar(100), @str02 as nvarchar(100), @str03 as nvarchar(100)
SET @str01 = 'K:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak'
SET @str02 = 'K:\SQLBackups\SAFETY_Backups\MyDB\MyDB_02of03.bak'
SET @str03 = 'K:\SQLBackups\SAFETY_Backups\MyDB\MyDB_03of03.bak'
BACKUP DATABASE MyDB TO DISK=@str01, DISK=@str02, DISK=@str03 WITH INIT, NOFORMAT[/font]
How can I restore the DB to multiple MDF's, NDF's across our 3 drives ( E: F: G: )
December 17, 2014 at 2:38 pm
The number of database files is unrelated to the number of backup files. The database will be restored with the same file structure it had when backed up.
If the original database really had three data files, they could be placed on separate drives with MOVE clauses:
RESTORE DATABASE MyDB FROM DISK=@str01, DISK=@str02, DISK=@str03 WITH INIT, NOFORMAT,
MOVE 'LogicalName1' TO 'E:\path\MyDB.MDF',
MOVE 'LogicalName2' TO 'F:\path\MyDB.NDF',
MOVE 'LogicalName3' TO 'G:\path\MyDB.NDF'
If the database had only one MDF file and you'd like to break it up, you must restore it as one file and then use ALTER DATABASE commands to add two more files on the desired drives. If you added two more 300+ MB files to the PRIMARY file group, data pages would be allocated in the new files automatically over time. You could speed this process along by trying to shrink the original file, or possibly with some index rebuilds. I would be careful what I did with a multi-TB database however.
For more control, you could add new file groups with new data files. Then you can use ALTER INDEX or CREATE INDEX WITH(DROP_EXISTING=ON) to move individual tables to the new groups.
http://technet.microsoft.com/en-us/library/ms175905(v=sql.110).aspx
December 18, 2014 at 1:24 am
Express12 (12/17/2014)
How can I restore the DB to multiple MDF's, NDF's across our 3 drives ( E: F: G: )
You can't.
A backup, no matter how many files it's striped across, when restored will restore the DB exactly as it was at the time of backup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2014 at 2:25 am
Hi all i have worked on this post if any corrections suggest me.....
We can see how the Database is being Backed up in multiple files and then its being Restored in to another Drive from the actual path to another new path 1 and so on..........................to path 3
Here if we can run the scripts individually as mentioned below we can see how the Files are Transferring from one place to another.
i have created a new database with MyDB also created the respective paths
in my D:drive ""D:\SQLBackups\SAFETY_Backups\MyDB
And another paths
D:\SQLBackups\Path1
D:\SQLBackups\Path2
D:\SQLBackups\Path3
---Run this --- first
DECLARE @str01 as nvarchar(100), @str02 as nvarchar(100), @str03 as nvarchar(100)
SET @str01 = 'd:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak'
SET @str02 = 'd:\SQLBackups\SAFETY_Backups\MyDB\MyDB_02of03.bak'
SET @str03 = 'd:\SQLBackups\SAFETY_Backups\MyDB\MyDB_03of03.bak'
BACKUP DATABASE MyDB TO DISK=@str01, DISK=@str02, DISK=@str03 WITH INIT, NOFORMAT
---D:\SQLBackups\SAFETY_Backups\MyDB
--Run this second-----
RESTORE FILELISTONLY FROM DISK = 'd:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak' WITH FILE = 1
GO
RESTORE DATABASE [MyDB] FROM DISK =N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_02of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_03of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
-- Run this third---
---step1-- Restore the Backups and its logical names files will move from one folder path to other path 'D:\SQLBackups\Path1---
RESTORE DATABASE [MyDB] FROM
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_02of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_03of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak' WITH
MOVE 'MyDB' TO 'D:\SQLBackups\Path1\MyDB1.Mdf',
MOVE 'MyDB_log' TO 'D:\SQLBackups\Path1\MyDB_log1.ldf',
FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Go
--Run this fourth---
---step2-- Restore the Backups and its logical names from 'D:\SQLBackups\Path1 to 'D:\SQLBackups\Path2---- files will move from path1 to path2
RESTORE DATABASE [MyDB] FROM
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_02of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_03of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak' WITH
MOVE 'MyDB' TO 'D:\SQLBackups\Path2\MyDB2.Mdf',
MOVE 'MyDB_log' TO 'D:\SQLBackups\Path2\MyDB_log2.ldf',
FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Go
---Run this Fifty
---step3-- Restore the Backups and its logical names from 'D:\SQLBackups\Path2 to 'D:\SQLBackups\Path3----files will move from path2 to path3
RESTORE DATABASE [MyDB] FROM
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_02of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_03of03.bak',
DISK = N'D:\SQLBackups\SAFETY_Backups\MyDB\MyDB_01of03.bak' WITH
MOVE 'MyDB' TO 'D:\SQLBackups\Path3\MyDB3.Mdf',
MOVE 'MyDB_log' TO 'D:\SQLBackups\Path3\MyDB_log3ldf',
FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Go
Thanks
Naga.Rohitkumar
Thanks
Naga.Rohitkumar
December 29, 2014 at 2:33 am
Why are you suggesting restoring the database multiple times with the data and log files in different places each time? That's going to have the same effect as if you just ran the last of the restores. Each time you restore, you're overwriting the existing database.
Your three restores will just, at the end, result in a database with a single mdf and a single ldf in D:\SQLBackups\Path3.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2014 at 4:38 am
yes Gail i too agree with you i just want to test this will work or not so that only tried
Thanks
Naga.Rohitkumar
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply