Backup->Restore SQLServer05 to ANOTHER machine

What is the simplest way to backup a SQLServer 2005 DB so that I'm able to restore it on ANOTHER machine and DB matching the same schema?

If I run the built in backup task, it won't restore to any other machine except the one that originated the backup...which is pretty useless if the harddrive crashes...

Why don't they offer a simply SQL dump of structure AND data like MySQL offers?
tradelineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
take a backup, copy it on the other server, restore from the above  copy

BACKUP DATABASE urDBName
TO DISK = 'C:\urDb.bak'
--now copy it to say  C:\  drive

RESTORE DATABASE NewDatabaseName
FROM DISK = 'C:\urDB.Bak'
0
rboyd56Commented:
aneeshattingal's solution is correct. However you may need to use the WITH MOVE parameter to changg the location of the files to an appropriate location on the new machine. The path to the data and log files are stored in the backup file and if the path on the original server does not exist on the new server you will get an error. So the WITH MOVE parameter allows you to overcome this

Example from Books on Line:

RESTORE DATABASE MyNwind
   FROM MyNwind_1
   WITH NORECOVERY,
      MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',
      MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'
RESTORE LOG MyNwind
   FROM MyNwindLog1
   WITH RECOVERY
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tradelineAuthor Commented:
rboyd56:

I accepted your comment too soon I'm afraid :).  Your solution yields the same results I've always experienced:

ERROR: The backup set holds a backup of a database other than the existing 'NewDBTest' database.

I used the following code, maybe I've misunderstood something?

RESTORE DATABASE NewDBName
   FROM DISK = 'C:\OldDB.bak'
   WITH NORECOVERY,
      MOVE 'OldDBName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewDBName.mdf',
      MOVE 'OldDBName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NewDBName_log.ldf'

Previously I wasn't restoring from SQL, but using the wizard inside SqlServer2005...the error is the same.  It is only allowing me to restore to the SAME database that was the source of the backup.

Thanks.
0
tradelineAuthor Commented:
Solved it:

I'm not sure what the SQL syntax is, but I achieved the same result in the options area of the restore UI, by selecting "Overwrite existing database", and then changing the paths from those on the webserver to those locally.

Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.