create database from another backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

create database from another backup

Author

Topic

mysuja
Starting Member

3 Posts

Posted - 2009-10-06 : 11:16:22

Hi,
I have two servers each installed with SQL 2005. In server1 I have a database db1. I have taken a backup of this db1 say bckpdb1.bak I want to create or replicate another this db1 in another server server2. How can I do that?

I tried to do replication, but It expects .dat and .log files. All I have is .bak file.

I am brand new to SQL server. So please guide me.

Thanks,

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 11:44:05

[code]
-- Check the Back up

RESTORE FILELISTONLY FROM DISK = '\\<file path>\<file name>'
GO

-- If the DB Exists, Kick everyone out

ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- RESTORE The database...this will overlay an existing one, OR Create a new one if not in existence
-- Get the Logical file names from filelistonly

RESTORE DATABASE <db_name>
FROM DISK = '\\<file path>\<file name>'
WITH MOVE '<logical data file name>_data' TO '\\<file path>\<file name>.MDF'
, MOVE '<logical log file name>_log' TO '\\<file path>\<file name>.LDF'
, REPLACE
GO

ALTER DATABASE <db_name> SET READ_WRITE
ALTER DATABASE <db_name> SET MULTI_USER
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mysuja
Starting Member

3 Posts

Posted - 2009-10-06 : 12:10:33

Is there a way I could do this same thing with the Studio using the GUI?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-06 : 12:30:51

Yes just go to the RESTORE wizard.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 12:42:15

quote:
Originally posted by mysuja

Is there a way I could do this same thing with the Studio using the GUI?

There's a much larger world out there

Avoid the GUI

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mysuja
Starting Member

3 Posts

Posted - 2009-10-06 : 14:37:42

In the restore window, it allows you to restore only from the database backup which was in the same machine. But I created the backup in machine1(server1). I copied the db1.bak file. I copied that file to another machine (Server2).

In the restore window, it asks two options

1) From database (which lists the databases in server2)
2) from device (which lists the backup devices names.) But since I created the backup in server1 I don't have any corresponding backup device in server2 for this .bak file. So From where do I restore it?

Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 01:06:14

The restore window does allow you to specify a path, so you can navigate to the location of your backup file.

You can specify a new database name in the restore window.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-07 : 02:58:20

- Copy the .bak-file to server2
- start the restore wizard
- check the device radio-button
- click the [...] button
- choose File as backup media
- click Add
- choose your backup file
- click OK twice
- check Restore for the backup set in the backup file you wish to restore
- change the database name to your liking
- check the other restore options
- click ok

- Lumbago
http://xkcd.com/327/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources