November 20, 2008 at 12:43 pm
[font="Courier New"]I successfullybacked up my DB to 4 seperate files:
BACKUP DATABASE MyDB TO DISK =
'D:\MyDB_01of04.bak',DISK='D:\MyDB_02of04.bak', DISK='D:\MyDB_03of04.bak', DISK='D:\MyDB_04of04.bak' WITH INIT, NOFORMAT
Unfortunately, my RESTE IS NOT WORKING. I need to restore to 4 seperate drives. The error message is stating that the F: drive below does not have enough space. I flip the F: and O: drive letters below, then it says the O: drive does not have enough space.
RESTORE DATABASE MyDB
FROM DISK='D:\Backup\MyDB_01of04.bak',
DISK='D:\Backup\MyDB_02of04.bak',
DISK='D:\Backup\MyDB_03of04.bak',
DISK='D:\Backup\MyDB_04of04.bak'
WITH REPLACE,
MOVE 'MyDB_Data' to 'O:\Data\MyDB_data01.mdf',
MOVE 'MyDB_Data' to 'M:\Data\MyDB_data02.ndf',
MOVE 'MyDB_Data' to 'N:\Data\MyDB_data03.ndf',
MOVE 'MyDB_Data' to 'F:\Data\MyDB_data04.ndf',
MOVE 'MyDB_Log' to 'J:\Logs\MyDB_log.ldf'[/font]
November 20, 2008 at 1:42 pm
DBASkippack (11/20/2008)
[font="Courier New"]I successfullybacked up my DB to 4 seperate files:BACKUP DATABASE MyDB TO DISK =
'D:\MyDB_01of04.bak',DISK='D:\MyDB_02of04.bak', DISK='D:\MyDB_03of04.bak', DISK='D:\MyDB_04of04.bak' WITH INIT, NOFORMAT
Unfortunately, my RESTE IS NOT WORKING. I need to restore to 4 seperate drives. The error message is stating that the F: drive below does not have enough space. I flip the F: and O: drive letters below, then it says the O: drive does not have enough space.
RESTORE DATABASE MyDB
FROM DISK='D:\Backup\MyDB_01of04.bak',
DISK='D:\Backup\MyDB_02of04.bak',
DISK='D:\Backup\MyDB_03of04.bak',
DISK='D:\Backup\MyDB_04of04.bak'
WITH REPLACE,
MOVE 'MyDB_Data' to 'O:\Data\MyDB_data01.mdf',
MOVE 'MyDB_Data' to 'M:\Data\MyDB_data02.ndf',
MOVE 'MyDB_Data' to 'N:\Data\MyDB_data03.ndf',
MOVE 'MyDB_Data' to 'F:\Data\MyDB_data04.ndf',
MOVE 'MyDB_Log' to 'J:\Logs\MyDB_log.ldf'[/font]
You cannot alter db file separation using a restore.
You can only restore to the original "logical" filenames exact as they were during the backup.
What you can do is perform the normal restore, then add new files to the filegroup (i guess primary) which contains currently "Mydb_dta".
and then reorg your tables and indexes.
RESTORE DATABASE MyDB
FROM DISK='D:\Backup\MyDB_01of04.bak',
DISK='D:\Backup\MyDB_02of04.bak',
DISK='D:\Backup\MyDB_03of04.bak',
DISK='D:\Backup\MyDB_04of04.bak'
WITH REPLACE,
MOVE 'MyDB_Data' to 'O:\Data\MyDB_data01.mdf',
MOVE 'MyDB_Log' to 'J:\Logs\MyDB_log.ldf'
, recovery
go
alter database mydb
add file (
NAME = 'MyDB_Data02'
, FILENAME = 'M:\Data\MyDB_data02.ndf'
, SIZE = size [ KB | MB | GB | TB ] ]
, MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
, FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ]
)
.....
having all these files in the same filegroup, sqlserver will "balance" the data in these files.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 20, 2008 at 1:47 pm
Sorry -- didn't see any reply w/ your feedback.. Any thoughts?
November 20, 2008 at 9:55 pm
All you do by backing up to 4 files is stripe the backup over 4 devices. It speeds up backups, nothing more.
As Alzdba said
You cannot alter db file separation using a restore.
You can only restore to the original "logical" filenames exact as they were during the backup.
ie, you cannot, in a restore, split a file group up, no matter how many backup devices you specify. Number of backup devices and number of data files are completely unrelated.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply