Restore error

Question

Post reply

Restore error

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

SkyBVI SSCoach Points: 15128 More actions · Answer 1

@ sushant

The .bak file is already compressed..

@Bhuvnesh

Maybe its called something else, but the general meaning of compression is compressing which is as the .bak file wrt the data files.

Regards
Sushant Kumar
MCTS,MCP

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

sushantkumar1984 (8/18/2010)
the general meaning of compression is compressing which is as the .bak file wrt the data files.

Something new to me :unsure:

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@Bhuvnesh

My database is 300GB and the .bak file(wen i do a backup) is 145GB only...

wat do u call this ?? Is it not compressed, the free space is not backed up...

Thanks,

Sushant

Regards
Sushant Kumar
MCTS,MCP

sushant.ranahandol 23461 SSC Rookie Points: 45 More actions · Answer 4

Bhubanesh,

introduce something new name instead of compression in sql server..:)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

🙂 for that Microsoft has to hire me. jokes apart , in sql 2008 there is feature of backup files compression

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SkyBVI SSCoach Points: 15128 More actions · Answer 6

@Bhuvnesh

How is that compression different from sql 2005?

Thanks,

Sushant

Regards
Sushant Kumar
MCTS,MCP

david.wright-948385 SSCarpal Tunnel Points: 4041 More actions · Answer 7

You need to grant (at least) read access to the shared drive to the user account the sql server instance is running under (default is Network Service), otherwise it won't be able to read the backup file hence (I suspect) the error.

If that isn't possible, create a compressed directory on your target server, copy the backup to that directory and when you restore the backup, specify that the database files are also moved to a compressed directory. It'll run really slowly, but it should work.

Another thing to think about is the source log file size. It will be restored to the original size on the target even if the space in the file isn't all being used. You may get an improvement by shrinking the log on the source before performing the backup.

sjimmo SSChampion Points: 11139 More actions · Answer 8

SQL Server 2005 and before does not compress it's dump files. In order to compress the dumps you need a 3rd party tool such as RedGate's SQL Backup. This can be downloaded and used for 14 days to test.

To restore across the network, the dump file needs to be in a directory that allows acces by the SQL Service account which is running the dump. This startup account must be a domain account.

Arbitrarily shrinking files will get you in the end. You will introduce fragmentation to your database, as well as on the physical drive in the files. Shrinking databases also means that at somepoint they will need to grow again, introducing more file fragmentation on the hard drives as well as IO resources which SQL Server will want.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

sushantkumar1984 (8/19/2010)
@bhuvnesh
How is that compression different from sql 2005?
Thanks,
Sushant

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/12/12/backup-compression-in-sql-server-2008.aspx

http://www1.unisys.com:8081/eprise/main/admin/corporate/doc/41371394.pdf

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)