copying databases - SQL Server Forums

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

copying databases

Author

Topic

mdean9999
Starting Member

21 Posts

Posted - 2011-05-04 : 12:57:27

My database is hosted online in a shared environment by a commercial company.
How do I safely copy the whole database down onto sql server 2008 on my local computer?
I have copied the tables to my computer. I have backed up all the stored procedures, but not the indexes.
My objective is to then put the database back online but with a new service provider. What is best, and safest, way of doing this?
michael

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-04 : 13:20:49

Are you able to BACKUP DATABASE?

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

Subscribe to my blog

mdean9999
Starting Member

21 Posts

Posted - 2011-05-04 : 13:57:19

I can select the database, and select 'backup database' option.
But it would appear to be backing up the database on the external companies server.
And it soon fails with some permission problem on their computer. They were not able to work out what was wrong.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-04 : 14:04:38

Well you can provide a UNC path to get it to your destination:

BACKUP DATABASE db1
TO DISK = '\\someServer\someShare\db1.bak'
WITH INIT

The destination will need to have access wide open so that their service account can write to it. It can be just temporary access though.

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

Subscribe to my blog

mdean9999
Starting Member

21 Posts

Posted - 2011-05-04 : 18:41:36

Thanks Tara

Can that UNC path be my local SQL Server 2008?
If so, what would the path look like?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-04 : 18:56:07

Whether it can or not depends on the network settings. Can the host's database server see your client machine?

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

Subscribe to my blog

mdean9999
Starting Member

21 Posts

Posted - 2011-05-04 : 19:17:13

not sure how that works.
I can access the server using an IP number?
There are a couple of very small databases on the same server which I can use 'backup database' to my local machine without an problems.
There is obviously another issue here - because the database in question is quite large.

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-05-06 : 07:28:28

check this on SQL Server which have backup database

exec xp_cmdshell 'ping 192.168.1.6'

replace 192.168.1.6 to your IP and if you have a public IP then you will define here and tell to the network administartor route that public IP to a particular private IP in your network

if this work then check this

exec xp_cmdshell 'ping 192.168.1.6 >> \\192.168.1.6\SHF'

replace 192.168.1.6 to your IP as I mentioned above,if this work then you can take a backup as TARA mentioned on remote location

BACKUP DATABASE db1
TO DISK = '\\someServer\someShare\db1.bak'
WITH INIT

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources