Solved

Restore SQL Server .bak file to another sql server

Posted on 2011-03-25
18
714 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:alphaau
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 142

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
 
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 30

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 30

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

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 30

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 30

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 30

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 30

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 30

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now