March 1, 2006 at 8:56 pm
I have a PROD database with multiple filegroups and it’s datafiles in diff drives. The data files are pre-allocate to 300GB in diff drive in view of expected grow size. I need to take the PROD backup and restore to a DR server with same drive configuration but with much lesser disk space.
Obviously, I can’t restore because not enough disk space in DR to fit for PROD data files. I know I can use DBCC SHRINKFILE to shrink the PROD database to size that can fit into the DR disk space, take a “shrinked” database backup, restore in DR and lastly manually resize back to the previously allocated size in PROD.
I just want to hear is this the only way and any likely problem in the method.
March 2, 2006 at 10:51 am
Another solution will be to bring a box of chocolates to another DBA, ask him/her to let you to use his/her server for an hour, restore on this third-party server, shrink, set recovery model to Simple, shrink log as well, backup and restore on your small test server. Remove all traces of your activity from the third-party server. Say Thanks to this DBA.
Next step is to write a memo to your manager asking for more hardware. That is exactly what we did several years ago in the similar situation.
I also used DTS to pump all Database Objects and Data while waiting for my new disks. I was not sure my database was copied 100% to the small server and there are always some issues with identity columns unless you are familiar with DTS real well, but it did give me an opportunity to continue with development while waiting for the bigger disks.
Regards,Yelena Varsha
March 2, 2006 at 11:00 am
It's probably the only way if you are using backups. Another possibility....take the original database offline (either by detaching it or stopping SQL Server services). Copy JUST the .mdf. Move the COPIED files to the new server and attach the file using sp_attach_single_file_db. On the original server, attach the db or start the services.
Using the single file attach on the copied database will create a log file from scratch.
One question - why have a disaster recovery system that has LESS space than the original?
-SQLBill
March 2, 2006 at 11:07 am
SQLBill,
Garrick has several filgroups and files, I am not sure sp_attach_single_file_db will work in this case. He probably has to copy all files?
Regards,Yelena Varsha
March 2, 2006 at 12:38 pm
If that's true, attach_single_file_db won't work. He's pretty much out of luck unless he can get a new backup after the database has been shrunk.
-SQLBill
March 3, 2006 at 2:47 am
You really need a DR server with similar disk capacity to your main server. The staff cost of working out how (and proving it works) or doing your DR in any other way is almost certainly greater than the extra disk cost.
More importantly, you need to go back to the Business Requirements for DR. These will drive how long you can take in performing a DR, which in turn will give a mandate for the hardware you need to have. If the requirements are not documented you can be sure your end user management will have higher expectations for DR availability than your IT team can provide.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 3, 2006 at 12:42 pm
Shrinkfile/Shrinkdatabase might take quite some time on 300GB database...
March 5, 2006 at 6:56 pm
Looks like shrinking, backup, restore is only way here.
: (. Like to know any precaution/catch to shrink a 300GB datafile to under 100GB.
My PROD is a 2-node clustering with a SAN disk. Mgmt can't affort to but a SAN box for DR. For this case, it's correct to say that the DR server is purely a standby for swaping of server hardware in the event of failure?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply