Home / server / Questions / 120186
Accepted
MatsT
Asked: 2010-03-08 15:05:57 +0800 CST

How do I restore a database on a remote SQL server 2005 from a local backup?

  • 772

I have been given access to (parts of) a remote SQL Server 2005 with SQL Server authentication in order to be able to make changes to a database without involving other people who is not working on the project. The database have been created on my local machine. Is there any way to restore the remote database from a backup file on my local computer? I do not currently have access to the filesystem on the remote server.

EDIT: To clarify, the access I have is that i can log in to the server via the SQL Server Management Studio. I have one connection to my local database server and one connection to the remote server. What I basically want to do is copy the database from one connection to the other.

sql-server sql-server-2005 database-backup

4 Answers

  1. Best Answer

    Not that I'm aware of. You might want to look at the Redgate SQL Compare/SQL Data Compare tools to see if they do what you need. They come with a 14 day free trial.

    • 1

  2. If the server can access your ip address, try something like:

    RESTORE DATABASE Data FROM DISK = '\\your.ip.address.number\share\backup.bak'

    It should work as long as you can correctly setup the ip link and the security. Check TSQL reference:

    If using either a network server with a UNC name or a redirected drive letter, specify a device type of disk. The account under which you are running SQL Server must have READ access to the remote computer or network server in order to perform a RESTORE operation.

    • 1

  3. Without the *.mdf and *.ldf, you can't restore it. You can however try and copy data manually (depending how big your database is), or try exporting it to a location where you can access via web.

    Failing any of those, you will need to request access to the server I'm afraid :-(

    • 0

  4. Try creating a SSIS package to copy everything over, assuming you dont have access to create or access a backup file of the database. Depending on your local version of SQL, you could right click the database, choose tasks, and export data to copy everything over through the SQL Server Import and Export Wizard.

    • 0