Solved

Restore SQL Server .bak file to another sql server

Posted on 2011-03-25
18
720 Views
Last Modified: 2012-06-22
Hello All;

I am downloading my .bak files from my hosting provider via mylittlebackup
Now, within the coming months my business is going to be slowly changing over to host on our own servers.
So.
Question:
How to restore .bak from 1 sql server to another sql server.

This is a SQL Server 2005.

Thank You
Carrzkiss
0
Comment
Question by:Wayne Barron
  • 7
  • 5
  • 3
  • +2
18 Comments
 
LVL 7

Expert Comment

by:Alpha Au
ID: 35215856
i guess the bak file is a backup of your database.

you can follow the link to restore the db in your server

http://msdn.microsoft.com/en-us/library/ms177429.aspx
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 167 total points
ID: 35215868
STEP1: Backup Database on server 1 using "BACKUP DATABASE [DBNAME] TO DISK='C:\backupfile.bak'
STEP2: Copy backup file to server 2
STEP3: Restore back using "RESTORE DATABASE [DBNAME] FROM DISK='C:\backupfile.bak'

The restore command may need a few tweaks but this is just a simple example.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 333 total points
ID: 35215877
Well, to restore a .bak from one server to another, you use the t-SQL restore statement or the sql management studio's GUI.

the usual problems are:
* the path are not available:
  => in the GUI, you go to the advanced, and specify existing path, and eventually new file names so you don't clash with existing files for other database
  => in T-SQL, you use the WITH  .. MOVE ... technique to, for each logical file, specify a good path for the file
  [note: RESTORE ... FILELISTONLY will tell you the files in the backup for your db, both logical and physical name]
  http://msdn.microsoft.com/en-us/library/ms173778.aspx
  [note 2: ensure the path/folders are created before you restore

* the db exists already:
  => in the gui, choose the overwrite
  => in TSQL, REPLACE in the WITH clause
  [note: if the db does not exist yet, the option does not harm]

* after the restore, some users don't work. usually, this is because the logins had not been migrated/copied yet before
  you then use this stored prodedure to Report and eventually Fix the issues:
  http://msdn.microsoft.com/en-us/library/ms174378.aspx

* when you restore from a server to another, if the destination server has a version that is lower: won't work.
   you have to restore to a version that has at least the same version + SP ..

ok, once you did all of this, and still run into issues:
post the error messages along with some details of what you run into.

CHeers

 
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:edfreels
ID: 35215904
Hi Carrzkiss,

If this is a full backup that you want to restore, connect to the server you want to restore to.  If it is local on your machine, then copy the .bak file to your machine.  If you are connecting to a server from your machine, make sure the server has access to the files location.  Also, you want to make sure you are using the same version or greater than the server where it was backed up.  You will need to know the logical name of the database from where it was backed up.  Once you have confirmed these things, the following script should do the trick for you:

RESTORE DATABASE [LogicalDatabaseName] FROM  DISK = N'C:\YourLocalPath\YourBackupFile.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE
GO

1. Replace [LogicalDatabaseName] with the actual database name.  
2. Replace C:\YourLocalPath\YourBackupFile with the path where the file is located.  Again, it will be the path as the server sees it.  So if it is local, it will be your local path or if it is server, it may be a UNC path.

Good luck!  Let me know if you still have troubles.
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35216129
OK.

This is what I run, and I get the same error

RESTORE DATABASE [test] FROM  DISK = N'G:\Database_Backups\C_kb.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Open in new window


Error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'test' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 
LVL 1

Expert Comment

by:edfreels
ID: 35216164
do you know what the logical name of the database is?  You will need to restore it with the same name it had when it was backed up.  
0
 
LVL 1

Expert Comment

by:edfreels
ID: 35216192
If you don't know the logical name, you can do what Angel said and "RESTORE ... FILELISTONLY will tell you the files in the backup for your db, both logical and physical name]
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35216211
You may need to use the WITH REPLACE option.
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35219762
Running this

RESTORE FILELISTONLY FROM  DISK = N'G:\Database_Backups\C_kb.bak' WITH  FILE = 1,  NOUNLOAD
GO

gives me the results of the database.
What do I need to do now?
(LogicalName = This I already know, as I am the one who named it)
(PhysicalName = is the path of the original distination, mdf and ldf)
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 333 total points
ID: 35223564
you could post that output..

and for the restore, as said, you need to use the REPLACE option

RESTORE DATABASE [test] FROM  DISK = N'G:\Database_Backups\C_kb.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10, REPLACE

Open in new window

0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35223737
This is the output

C314167_kb	D:\MSSQL.1\MSSQL\DATA\C_kb.mdf	D	PRIMARY	1966080	1048574951424	1	0	0	EB748E3B-42AC-4A09-9949-F35A11FEB696	0	0	1966080	512	1	NULL	389000000004200037	4ACF15CD-31C2-4CD1-9DCE-A5C5C2298D92	0	1
C314167_kb_log	D:\MSSQL.1\MSSQL\DATA\C_kb_log.LDF	L	NULL	786432	1048576000000	2	0	0	F2408AA7-D8E0-4B51-8DC0-14A1CCCBFF6D	0	0	0	512	0	NULL	0	00000000-0000-0000-0000-000000000000	0	1

Open in new window

0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35245198
I supplied what was asked, any further assistance on this would be greatly appreciated.
(The above lost some data when I copied it out and put it in notepad then brought it over. The code supplied below is as it displays in SQL Management.)
C314167_kb	D:\MSSQL.1\MSSQL\DATA\C314167_kb.mdf	D	PRIMARY	1966080	1048574951424	1	0	0	EB748E3B-42AC-4A09-9949-F35A11FEB696	0	0	1966080	512	1	NULL	389000000004200037	4ACF15CD-31C2-4CD1-9DCE-A5C5C2298D92	0	1
C314167_kb_log	D:\MSSQL.1\MSSQL\DATA\C314167_kb_log.LDF	L	NULL	786432	1048576000000	2	0	0	F2408AA7-D8E0-4B51-8DC0-14A1CCCBFF6D	0	0	0	512	0	NULL	0	00000000-0000-0000-0000-000000000000	0	1

Open in new window

0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35294419
This did it.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE

I was looking around forever to find something that was not needed.
In then found this post Error 3154
Which is the same as what has been provided here, but with very little coding effort.
I just tested it out on one of my LIVE DB's with my DB here locally and received the message after running the script

Processed 240 pages for database 'kb', file 'C314167_kb' on file 1.
Processed 3 pages for database 'kb', file 'C314167_kb_log' on file 1.
RESTORE DATABASE successfully processed 243 pages in 0.659 seconds (3.017 MB/sec).

So all is very good now.

Thanks guys.
I am going to split the points with everyone here.
Take Care and have a great weekend.
(And this is REAL, this is not April Fools....)
Carrzkiss

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35294444
You may want to have a look at my comment made @ 25/03/2011 03:28 ;-)

"You may need to use the WITH REPLACE option. "
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35294643
Hey Evil
I awarded you points, I am sorry that it was not the exact post.
I still Accepted you as Answer.

Carrzkiss
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35294698
Sorry, was being sarcastic and didnt pull it off to well. :) Not complaining.
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 35294873
Oh.
OK, the April Fools is fooling around today I see.
Would go with the name very well..
The Evil April Fools Post It....

Have a good one.
Carrzkiss
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35294963
I will take that into consideration ;-D
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy data to New Year 9 33
How come this XML node is not read? 3 32
sql server cross db update 2 23
Problem Backingh Up Transaction Log on Old SQL000 Database 3 27
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question