question

ecomma avatar image
ecomma asked

Sqlserver 2008 Backup restoration failing

I do Sqlserver 2008 database backup to the the C drive of my Central Server. On daily basis the system admin team do a full system backup of this server to Network Driver. I Then Copy the sqlserver database from the network drive to the C driver of Another Server and do the restoration. The Current Database size of the my Database is 118GB. When i trying to Restore, i browse to the location of database on the C Drive and Select the full backup file, it fails to read. i check the window task Manager it says DBXYZ is not responding. I but when i try to select the log it works. I suspect the problem has to do with the size of the database.(My sqslerver is an Enterprise edition. Please help.Tell me how you resolve yours.
sql-server-2008restoretimeout-expired
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Seems like either the backup path is not correct or the permissions are not correct.
4 Likes 4 ·
ecomma avatar image ecomma commented ·
am trying to restore the .bak file NB: The space in the destination is 300 GB. ErrorMessage i received:Time out expired.The period elapse prior to completion of the operation or the sqlserver is not responding
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
Did you tried using the RESTORE DATABASE statement?
0 Likes 0 ·
ecomma avatar image ecomma commented ·
I Tried this: RESTORE DATABASE [IDKit] FROM DISK = N'C:\BJL01_Replica\week4 IDKit_backup_2012_05_25_140004_2129764.bak' WITH FILE = 1, NOUNLOAD, REPLACE, CONTINUE_AFTER_ERROR, RECOVERY, STATS = 10 GO i got this error: Msg 3201, Level 16, State 2, Line 3 Cannot open backup device 'C:\BJL01_Replica\week4 IDKit_backup_2012_05_25_140004_2129764.bak'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
@Cyborg I have edited the comment. It was a format issue.
0 Likes 0 ·
Show more comments
ecomma avatar image
ecomma answered
With this Query i finally get to my Answer. RESTORE DATABASE [IDKit] FROM DISK = 'C:\BJL01_Replica\week4\IDKit_backup_2012_05_25_140004_2129764.bak' WITH MOVE 'IDKit' TO 'C:\Program Files\Microsoft SQL Server R2\MSSQL10_50.GIDREPLICA\MSSQL\DATA\IDKit.mdf', MOVE 'IDKit_Log' TO 'C:\Program Files\Microsoft SQL Server R2\MSSQL10_50.GIDREPLICA\MSSQL\DATA\IDKit_Log.ldf', NOUNLOAD, REPLACE, CONTINUE_AFTER_ERROR, NORECOVERY, STATS = 10 GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
***The Current Database size of the my Database is 118GB*** This does not mean that your backup file will be 118 GB. e.g : if your db size is 100GB and if your database has free space of 90GB then your full backup file will size 10GB only, but when you do a restore with this full backup file you should have 100GB of space in your destination drive otherwise the restore will fail. ***When i trying to Restore, i browse to the location of database on the C Drive and Select the full backup file, it fails to read*** Did you tried using SSMS? Can you post the error message here. Did you tried using RESTORE DATABASE command? ***but when i try to select the log it works*** Are you trying to restore the .bak file, or are you trying to attach the mdf and ldf files?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
This should not be happening unless your disk is too slow to respond. It would have make sense, if the source would have been a tape. But anyways we always use TSQL to restore our databases. I guess if you would use TSQL to restore the database, then it will be restored successfully. > These are the steps that take place in > the background when the restore is > performed in GUI > > Step 1: Enumerate the backup devices. > > Step 2: Fetch details about the > backup that we are trying to restore > > Step 3: Fetch the backup header (This > can take time depending on the size of > the backup and backup media/device) > > Step 4: Obtain the list of files > present in the backup set/media. > > Step 5: Perform the actual restore > operation. The above sequence have a built-in timeout of 20 seconds.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Can you post the results of RESTORE FILELISTONLY FROM DISK = N''
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image
basit 1 answered
Please check wether your backup file is corrupt or not.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.