SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create database using Backup file


Create database using Backup file

Author
Message
VRT
VRT
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 603
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
Elisabeth Rédei
Elisabeth Rédei
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 788
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
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20058 Visits: 17244
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" ;-)
VRT
VRT
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 603
Perry Whittle (11/18/2008)
venki

you can of course also use the management studio if you prefer GUI interraction


How can we do with SQL Server Management Studio?

Thank You
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4400 Visits: 7195
venki (11/19/2008)
Perry Whittle (11/18/2008)
venki

you 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..
VRT
VRT
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 603
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
VRT
VRT
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 603
steveb (11/19/2008)
venki (11/19/2008)
Perry Whittle (11/18/2008)
venki

you 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
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20058 Visits: 17244
click in the combo box and type a new database name Wink

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" ;-)
VRT
VRT
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 603
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
Adiga
Adiga
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2680 Visits: 21012
You cannot restore a SQL Server 2005 database backup to SQL Server 2000.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search