Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 8 '10 at 2:33

This question came from our site for professional and enthusiast programmers.

    
You should try this question on ServerFault. –  David Stratton Mar 7 '10 at 23:15

4 Answers 4

up vote 1 down vote accepted

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.

share|improve this answer
    
Redgate's SQL tools had the required functionality. The Trial version could not restore backups to a remote server, but Redgate SQL Compare had a synchronization tool that effectively did the same thing. –  MatsT Mar 7 '10 at 23:53

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 :-(

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.