Marcus Aurelius
asked on
Can I RESTORE only selected DATABASES from a SQL Server BACKUP that contains MANY DBs?
I have a production environment that is managed by a National Team, NOT my region. This Database is updated as often as needed in our PRODUCTION environment. I need to have about 3 select databases that are located on my DEVELOPMENT Server UPDATED with the DATABASE BACKUP from my PRODUCTION environment.
I'm NOT a DBA, but have worked with SQL Server doing DBA tasks. I know how to copy and restore an entire backup, but I don't do it often enough to know the details.
Question: If I have a copy of the entire PRODUCTION Server Database Backup, can I select ONLY specific Databases on that backup and ONLY restore those DBs? What about Specific TABLES??
I have a SPACE issue and so how much FREE SPACE, above and beyond the DB size does a RESTORE require? The DB in question is already on the DEV server, but I need to refresh the entire DB with new tables/data from PROD.
Also, is there a short concise STEP BY STEP instruction list available somewhere?
I'm NOT a DBA, but have worked with SQL Server doing DBA tasks. I know how to copy and restore an entire backup, but I don't do it often enough to know the details.
Question: If I have a copy of the entire PRODUCTION Server Database Backup, can I select ONLY specific Databases on that backup and ONLY restore those DBs? What about Specific TABLES??
I have a SPACE issue and so how much FREE SPACE, above and beyond the DB size does a RESTORE require? The DB in question is already on the DEV server, but I need to refresh the entire DB with new tables/data from PROD.
Also, is there a short concise STEP BY STEP instruction list available somewhere?
A sql server db backup and restore is, as far as I have seen, always per single db....
ASKER
We have a Development "Server" that has a setup something like this:
MasterDB
DBName1
DBName2
DBName3
...
DBName25
etc
So for example, I need to restore DBName1 and DBName3 ONLY from the Server Backup...
This is possible correct?
What about the FREE SPACE required...? Is it like DOUBLE the space of the backup ...or something like that?
MasterDB
DBName1
DBName2
DBName3
...
DBName25
etc
So for example, I need to restore DBName1 and DBName3 ONLY from the Server Backup...
This is possible correct?
What about the FREE SPACE required...? Is it like DOUBLE the space of the backup ...or something like that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,... what about the SPACE AVAILABLE requirement? How much extra space will I need on my DEV SERVER ?? I have an issue with lack of disk space on my DEV,...
Well, you only need the space for the databases you restore and normally the backup file.
You cannot restore single tables. The only thing you can do: you can restore single file groups, but this requires a sophisticated database design to avoid inconsistencies
You cannot restore single tables. The only thing you can do: you can restore single file groups, but this requires a sophisticated database design to avoid inconsistencies
ASKER
Ok,... so if my DEV server has 50 gb of free space remaining,.. and the DB being restored has a size of 60 gb,.. then I'll probably need to delete the data off my DEV server DB first, right? Or will the RESTORE program itself handle the delete itself,.. so that the restore process won't crash die to running out of space on Dev Server.
My concern is how much free space is required since I seem to recall that SQL Server saves some files in disc UNTIL the restore has been competed.... ????
My concern is how much free space is required since I seem to recall that SQL Server saves some files in disc UNTIL the restore has been competed.... ????
No, the restore can overwrite the existing database, then it may work. You just have to specify to use the existing database files.
On the other hand, you can drop the database first and remove its files and restore the database then.
On the other hand, you can drop the database first and remove its files and restore the database then.
btw, you should only work with scripting on your development server. Using backups from production, thus using data from production is part of testing and quality management. It's imho a separate process.
ASKER
I need to do both,.. structure and data.
The RESTORE does both,.. right??
The RESTORE does both,.. right??
Sure.
ASKER
THANKS for the assistance..!
You're welcome.
ASKER
What are some possible action items for me to do,....IF, my Dev Server is too full at present??
For example, my free space available for DATA on my Dev server is about 225gb. The database in-question is currently at 500gb.
What are my options?
Can I delete an OLDER block of data from my DEV server?
(ie. All data prior to 2010, 2011...etc???)
If so, HOW do I do this..?
For example, my free space available for DATA on my Dev server is about 225gb. The database in-question is currently at 500gb.
What are my options?
Can I delete an OLDER block of data from my DEV server?
(ie. All data prior to 2010, 2011...etc???)
If so, HOW do I do this..?
Storage is cheap nowadays.
ASKER
Gov't project, must go through many channels for approval and at present don't have time,... need immediate solution,... without increase in Storage.....
Any more recommendations....??
see: https://www.experts-exchange.com/questions/28456397/SQL-Server-How-to-limit-the-amount-of-data-captured-within-a-backup-file.html
Any more recommendations....??
see: https://www.experts-exchange.com/questions/28456397/SQL-Server-How-to-limit-the-amount-of-data-captured-within-a-backup-file.html