transfering a db from localserver to a remote one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

transfering a db from localserver to a remote one

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2010-05-17 : 07:24:11

What is the correct/easy/recommended way to put a db i created locally using visual studio 2010, to a remote server?

my local db is an aspnet_db, and i added to it some tables.
I use SSMS

quote:
Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Data Access Components (MDAC) 6.0.6002.18005
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18904
Microsoft .NET Framework 2.0.50727.4200
Operating System 6.0.6002

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-17 : 08:09:06

Take a backup
Copy the backup to the other server
Restore the database from backup on the remote server.

--
Gail Shaw
SQL Server MVP

elic05
Yak Posting Veteran

62 Posts

Posted - 2010-05-17 : 08:45:46

I did a backup.

but when i connect to the remote server and try
to restore, I dont get an option to browes my own system for the .bak file.

task -> restore -> database

from device ...
how can i browes for my backup?

thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-17 : 09:06:37

Copy the backup file to the remote server.
Then restore and browse to the file on that server, not your own.

--
Gail Shaw
SQL Server MVP

elic05
Yak Posting Veteran

62 Posts

Posted - 2010-05-17 : 13:26:32

there is no chance he will give me to do such a thing.
it's a shared server.

is there another option?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-17 : 13:39:47

You can restore via unc:

RESTORE DATABASE db1
FROM DISK = '\\SomeServer\SomeShare\SomeFile.bak'
WITH REPLACE, RECOVERY

Depending upon the paths of the source and remote server, you may have to also specify the MOVE option in the WITH portion of the command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-18 : 03:24:32

To do that you need to ensure that the service account that SQL is running under has access to that shared folder.

--
Gail Shaw
SQL Server MVP

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-18 : 03:30:17

How big is the database? How much data do you need to move? If it's not that much you can script the tables/procedures/etc using the script database wizard and then use another script (i.e. this: http://www.nigelrivett.net/DMO/s_ScriptData.html) to generate insert-statements for all your data.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources