July 12, 2010 at 1:58 pm
I want to restore a new database. The backup file is on other server. So, I am sharing the backup files and try to create a new database by directly restoring it.
When I do it, it gives an error:-
A transport level error has occured when receiving results from server(provider: TCP provider, error:0 The specified network name is no longer available)
Microsoft SQL server Error:64
Any help will be appreciated.
Sushant
DBA
Virgin islands.
Regards
Sushant Kumar
MCTS,MCP
July 12, 2010 at 2:32 pm
This is a network issue - there is something that is causing that system to lose connection with the network or that other system.
Copy the backup files local and restore from there.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2010 at 4:41 am
i think there is some access restriction, copy that backup file to the local drive of server (where your want to restore it) then do the restoration.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 13, 2010 at 3:23 pm
hi,
When i tried to restore the file from locally, it says there is insufficient space on the drive. I have 271 GB free space left on the drive and the database to be restored is 144 GB. The database to be restored is not on the server , I am creating it by restoring it directly..
Any ideas...
Sushant
DBA
Virgin islands.
Regards
Sushant Kumar
MCTS,MCP
July 13, 2010 at 5:34 pm
Hi Sushanth,
How many data files (.mdf, .ndf) do you have?
What are the sizes of the .mdf, .ndf and .ldf files?
If you don't have enough space on the required Drive, try to
1. Free the disk space by deleting old and unused files if any on that drive and do a restore.
OR
2. Restore the DB using MOVE option by separating the Data and Log files onto seperate drives.
http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/
Thank You,
Best Regards,
SQLBuddy
July 14, 2010 at 1:28 am
When i tried to restore the file from locally, it says there is insufficient space on the drive.
When you restore a full database backup, it will restore the database files to the same directories it was originally stored in. You can see what directories those are, and the size of those files, by using the RESTORE FILELISTONLY command e.g.
RESTORE FILELISTONLY FROM DISK = '<your backup file name>'
In your case, it's most likely the directories that are required do not exist, or do not have sufficient space. In that case, you need to tell SQL Backup to restore the files to other directories by using the MOVE option. You need to use the logical file names returned by the RESTORE FILELISTONLY command to relocate your files e.g.
RESTORE DATABASE mydb FROM DISK = 'g:\backups\mydb.bak' WITH MOVE 'mydb' TO 'h:\sqldata\mydb.mdf', MOVE 'mydb_log' TO 'h:\sqldata\mydb.ldf'
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 14, 2010 at 7:25 am
@sqlbuddy....
The data files for that database are on other server 307GB(mdf) and 10GB(ldf)..
locally i just have the .bak file which is 144GB and free space left is 271GB..
1) I cant free the disk space as there are no old or unused files.
2) MOVE will just copy the database from that server or will cut hte database from that server.
i just want to copy that databse from that server to this server.
Regards
Sushant Kumar
MCTS,MCP
July 14, 2010 at 8:10 am
Hi Sushant,
As you are restoring the database from the backup, backup and the database on the server from which the backup is being restored will remain fine there . Database will be restored on the local server.
If you 144 + 271 GB of free space (Without the .bak file on that drive) on the drive then you can go ahead and restore the database directly.
If not, as there is only 271 GB Free space and as .mdf requires atleast 307 GB try to make the free space on the drive so that you can put the .mdf there and .ldf on to another drive.
If you don't have atleat 307 GB restore will fail. You may get some free space on that drive by doing a shrink on the databases.
If that is not possible you have to find another drive that can accomodate that .mdf or atleast make some provison to increase the disk space on that drive.
Thank You,
Best Regards,
SQLBuddy
July 14, 2010 at 8:33 am
That explains why you originally wanted to restore over the network, as you don't have enough space to store both the bak file and the restored database.
Some options:
- shrink the source database
Your backup file is 144 GB in size, and your data file is 307 GB. That suggests that there are a lot of unused extents in the database, which you could get rid of by shrinking the data files. However, you need to determine if this size of 307 GB is the 'usual' size, because if the data file is going to grow to that size again in its daily operations, you will end up with a very physically fragmented file. If shrinking the file is acceptable, and the mdf file size after that is < 271 GB, then make a backup and restore the database.
- detach the source database
You can detach the source database, copy the database files over to this other box, then reattach the databases on both servers. You'll need to delete the existing .bak file on this other server first, since its occupying 144 GB. You'll also be taking your source database offline for the duration of the copy.
- read data directly from the backup file
A couple of companies offer products that allow you to use a backup file as if it was an online database, one of which is SQL Virtual Restore from Hyperbac (see here).
- add an additional disk to hold either the .bak file or the database files
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 14, 2010 at 8:35 am
@ sql buddy..
The drive has 420GB free space if i delete that 144GB .bak file...but thing is that
when i delete that .bak file and then restore it through SSMS by restore database, the.bak file is shared through network and then again i get hte same error message as i wrote when i started this post...
So i am stuck,
IF i share the .bak file through network, den i get tht error and if i copy that .bak file locally, then the disk space becomes 410gb-144gb =270gb approx and then restore cant be possible.
The other drive on this network is only 50GB...so i can restore that .bak file on dat 420GB drive only...
I heard that copy database wizard can do this stuff but it too has many bugs,,
Please advise me
Sushant
DBA
Virgin islands.
Regards
Sushant Kumar
MCTS,MCP
July 14, 2010 at 8:48 am
@ ray mond..
ya u r right, thtsy i wanted to restore it over hte network as i didnt had enough space.
...
I dont know if its advisable to do as its the producation database, many users are using it
i cant detach it as many people are workin on it, it has to be online everytime.
I will see for that product ...sql virtual restore..
Ya, we are looking for an additional disk to hold .bak file separately..
Sushant
DBA
Virgin Islands
Regards
Sushant Kumar
MCTS,MCP
July 14, 2010 at 9:50 am
Hi Sushant,
Try to shrink the database and then take a backup which should reduce the size of the Data and Log files.
Since it is a production DB you can use
1. DBCC SHRINKDATABASE (DBName, TRUNCATEONLY) and then take a backup and restore
OR
2. After the business hours , use
DBCC SHRINKDATABASE (DBName, 10) which shrinks the files by reorganizing the data and leaves a free space of 10%
This should result in significant reduction in the size of files.
Resort to Copy DB Wizard only if nothing else works
Thanks
Best Regards,
SQLBuddy
July 15, 2010 at 6:01 am
how long does the command run before it gives error?
how are you sharing the file? Normal windows share?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
July 15, 2010 at 7:06 am
@ oomboom
how long does the command run before it gives error?
how are you sharing the file? Normal windows share?
The restoring starts with 10%,20%.....around 50-60% it throws the error
as mentioned while i started the post..
I am sharing...for ex...went to that server, righ click the folder, sharing and security,
giving hte full permissions, and then going to SSMS of hte other server(where i want to restore),
restore database and then mentioning the path of that shared folder, and also locating new place for restoring file as mdf,ldf files ...
Regards
Sushant Kumar
MCTS,MCP
July 16, 2010 at 6:31 am
@ all
I got a new drive 410 gb on my server, Now when i copied the .bak file locally, and then trying to restore it, then also I am receiving the same error as mentioned while i started th post....
wats the problem??
Sushant
DBA
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply