November 18, 2008 at 6:12 am
Hi,
I have backup file of database in my local disk. I want to create a that database in another SQL Server. How can I do that?
Please send the steps to create the database using backup file?
Thank You
November 18, 2008 at 8:23 am
Hi Venki,
You just have to do a RESTORE of the backup on the other server. Let's say the name of your backup file is "myBackup.bak" and you copy it to the root of c:.
First off, you need to find the logical names of the files:
RESTORE FILELISTONLY FROM DISK = 'c:\myDB.bak'
Make a not of the LogicalName for both datafile and logfile (let's say it's myDB_Data and myDB_log).
Next you RESTORE the database with the WITH MOVE clause so you can specify where on the second server you want to put the database.
RESTORE DATABASE myDB
FROM DISK = 'C:\myDB.bak'
WITH MOVE 'myDB_Data' TO 'C:\DATA\myDB.mdf',
MOVE 'myDB_Log' TO 'C:\DATA\myDB_log.mdf'
GO
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 18, 2008 at 4:59 pm
venki
you can of course also use the management studio if you prefer GUI interraction
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 19, 2008 at 7:47 am
Perry Whittle (11/18/2008)
venkiyou can of course also use the management studio if you prefer GUI interraction
How can we do with SQL Server Management Studio?
Thank You
November 19, 2008 at 7:53 am
venki (11/19/2008)
Perry Whittle (11/18/2008)
venkiyou can of course also use the management studio if you prefer GUI interraction
How can we do with SQL Server Management Studio?
Right Click on databases node on SSMS.
Click Restore Databases..
November 20, 2008 at 6:00 am
Thanks a lot to all of you for your suggestions.
I know how to create new databases. But I was first affraid when I got this requirement that I have to transfer the databases from one SQL Server to another SQL Server. I asked the DBA and he sent the back up files and he don't have the access to the other Server. so I did it successfully using SSMS.
Thank You
January 9, 2009 at 5:41 am
steveb (11/19/2008)
venki (11/19/2008)
Perry Whittle (11/18/2008)
venkiyou can of course also use the management studio if you prefer GUI interraction
How can we do with SQL Server Management Studio?
Right Click on databases node on SSMS.
Click Restore Databases..
After resotre databases, it is showing the present databases list in Databases combo box and how can I give the new name?
Thank You
January 9, 2009 at 5:52 am
click in the combo box and type a new database name 😉
if you right click at the "databases" node of the tree view the combo box should be empty when the restore window appears.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 16, 2009 at 7:16 am
I am getting the following error while doing restore operation through SSMS.
Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE HEADER ONLY is terminating abnormally (Microsoft SQL Server, Error: 3205)
In net, I found that this is due to the version problem i.e. the back up file is from SQL Server 2005 and My development Server is 2000. So the above error is coming. In microsoft site, they mentioned that to fix the problem, we have to upgrade the SQL Server Service Pack to latest Service Pack. In some other site, They mentioned that you have to upgrade your SQL Server 2000 to SQL server 2005/2008.
Now my question is that, is it not possible to restore through any commands(not with SSMS) like RESTORE DATABASE...etc?
If there is a way please tell me? What happens if I install the SPs? will they delete the data and upgrade the SQL Server to another SP? Basically I am a developer and I don't know much about restores and creation of DBs. So kindly help me please?
Thank You
March 17, 2009 at 2:44 am
You cannot restore a SQL Server 2005 database backup to SQL Server 2000.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 17, 2009 at 6:23 am
Venki
you didnt indicate in your first post that the backup was a sql2005 database going into sql2000. This cannot be done. Only option is to DTS the data across to a sql2000 structure database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 17, 2009 at 6:54 am
You can never downgrade your database. it is not possible to restore a 2005 backupfile onto a 2000 server, same thing with the 2005 and 2008.
March 17, 2009 at 8:07 am
Adiga (3/17/2009)
You cannot restore a SQL Server 2005 database backup to SQL Server 2000.
Pradeep Thanks again for your clarifications.
Is it possible to transfer the database directly from 2005 server to 2000 server?
How can I use the database? In the mean time I ask my admin team to upgrade the my development Server to 2005.
Thank You
March 17, 2009 at 8:14 am
Is it possible to transfer the database directly from 2005 server to 2000 server?
No, not through backup and restore method, instead you can script out your 2005 database and then run it against your 2000 server.
March 17, 2009 at 8:14 am
Sorry,
I haven't seen the next page.
Yes, you are right, it is my mistake, not to tell the back up file is from 2005. But I thought this versioning won't be a problem for restoration.
So I can create tables manually and do the DTS from 2005 db to 2000 db.
Thank You
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply