Sql-server – Restore database to a instance in network

backuprestoresql serversql-server-2008-r2

We are creating a application which requires sql database as backend to store data. So when user install my application he chooses a sql instance and its details. There we plan to restore the database which is required for storing the users data etc.

So now if the user selects a sql instance of a another pc in the network then, how i should restore the database? Giving network path in backup database t-sql command is not a good idea. So in this cases how the database is restored to a network path? What is the best practice for this.

Best Answer

So are there multiple backups that may be selected? I'm having a hard time following that portion of the question.

If there are a large number of backups that you don't want to port with the application installation, then you would be storing them on a network share accessible by all client machines that would be installing your application. If there was only one backup, and it was reasonably sized, or multiple backups that are reasonably sized, why wouldn't you just have those ship with the installation?

Provided that's not an option, what you could do is create logic to first copy the backup file from the share to a local directory. Think of it like this:

Copy backup file from "\yourShareName\Dir1\backup_file.bak" to "C:\Some\Local\Dir\backup_file.bak"
Restore your database from the local copy of the backup file

Related Solutions

Sql-server – Restore a Database to a New database (T-SQL)

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.

Sql-server – Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.

You should verify the directory you are trying to place the database in. The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".

Edit

If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:


SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1

You should get results similar to this, highlighted the path for my instance: enter image description here

You can also verify this path through the restore prompt in SSMS, clicking on the "files": enter image description here However also noting that the error message operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like

The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'

Best Answer

Related Solutions

Sql-server – Restore a Database to a New database (T-SQL)

Sql-server – Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

Related Question