0

We tried to copy 500GB data to local drive using copy option in SQL management studio but it getting failed after 2 or 3 hours...is there any size limitation to copy?

How much size script file should be if we take for 500 GB database? Please let me know other way to take backup since server have insufficient storage space in c drive?

bhuva nesh
  • 1
  • 1

1 Answers1

0

In SQL Server Management Studio, try the following:

  1. Right-click database > Tasks > Backup...
  2. Check the box that say "Copy-only backup"
  3. On the left pane, select Backup Options and set backup compression to "Compress Backup"

With compression enabled, the database backup file should be much smaller than the original 500 GB. For reference, my 5GB database backed up with compression was 1 GB.

If you have nothing to lose, you can also try reducing the database log file size before proceeding with the backing up:

  1. Right-click database > Properties > Options
  2. Change database Recovery model from Full to Simple
  3. Right-click database > Task > Shrink > Files
  4. Select File Type Log and click OK

Hope this helps!

Peter
  • 309
  • 2
  • 11
  • Thanks....In above scenario,minimum free space in server should have 250GB. But current server have less then 200GB.. is it possible to make scripts in less size? – bhuva nesh May 10 '20 at 01:36
  • You can backup the database into multiple parts by adding multiple paths to the backup destination. I normally add 5 parts, for example (C:\backup1.bak, C:\backup2.bak, D:\backup3.bak, D:\backup4.bak, D:\backup5.bak) where D:\ is my external hard drive. When you restore the database, all files must be added when you restore, otherwise you can't restore. The point is, you'll still need free space somewhere, it doesn't have be all on 1 drive. – Peter May 10 '20 at 02:10
  • Ok.Thanks. will check and get back if any doubts – bhuva nesh May 10 '20 at 04:28
  • Thanks.... It's successfully restored.... Hope all data should be there – bhuva nesh May 11 '20 at 14:00