Restore database to hosted server

I am using an SQL hosted service which runs Microsoft SQL Server 2005

To access the hosted server I use Microsoft SQL 2012 Server Management Studio.

Today I accidentally cleared all the data on hosted server and then asked the provider to restore the database for me.

The provider provided me with a .BAK file.

I have tried many suggestions found on blogs to restore the file from Microsoft SQL 2012 Server Management Studio to hosted SQL Server 2005.

Any suggestions ?
Errol FarroAsked:
Who is Participating?
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Start with:

RESTORE FILELISTONLY
FROM DISK = '<FullPathToBakFile.BAK>'

Now you know the backupfile is readable, i e permissions and soforth.

Now run:
SP_HELPDB <DBname>

This will show you the location of the database. Note the TWO logical names, you'll need them later in step below.

To restore
RESTORE DATABASE <DBname>
FROM DISK = '<FullPathToBakFile.BAK>'
WITH
MOVE <RESTOREFILELISTONLYINFO_LogicalName>' TO '<SP_hELPDBINFOPhysicalPath.MDF>',
MOVE <RESTOREFILELISTONLYINFO_LogicalName>' TO '<SP_hELPDBINFOPhysicalPath.LDF>',
STATS = 5,
REPLACE,
RECOVERY

This should do it.

If you cant figure it out, show me what
Restore filelistOnly and SP_HelpDB gives as output. And I can assembly the restore script for you.

(Stats = Shows every 5%, Replace = Do not bother about tailbackup [your choice, but DB is unusable as it is so...], Recovery = put DB Online after restore)

Regards Marten
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
What's the error that you are facing? If the backup was taken from SQL Server 2005, there should be no issues in restoring it again on a SQL 2005 instance.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
note: when you use ssms connected to the server and you select task restore database, it's looking for that file ON the server, not your local machine.
0
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.

All Courses

From novice to tech pro — start learning today.