how to move database through script

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to move database through script

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2009-11-23 : 23:33:39

hi,
i have one server name xyz1 for which i am connecting remotely from my system. it has database userdb. i want to move this entire databse to another server which is in another machine through script. please suggest me a way to accomplish it

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-23 : 23:35:07

You can use BACKUP/RESTORE or detach/attach.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

rammohan
Posting Yak Master

212 Posts

Posted - 2009-11-23 : 23:36:32

hi,
i have to perform this using script only. please suggest me an exmaple.that will help lot for me

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-23 : 23:38:02

You can put those into scripts. That's how I move databases. Look up BACKUP/RESTORE in SQL Server Books Online and then let us know what issues you are having.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

rammohan
Posting Yak Master

212 Posts

Posted - 2009-11-23 : 23:39:26

and one thing. i have to connect two those servers from my machine remotely only. so i need to peform this by entering remotely to one server and by opening sqlserver there i have to copy this database to another sqlserver which is in another machine. i mean is there any way to run a script from this server,that can create database in another server by mentioning credentials?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-23 : 23:42:36

Yes you can do it via script. You can even compile them into one batch file so you only have to run one thing. The batch file would contain the calls to your other scripts.

To get you started, here's a sample:

batch file:
sqlcmd -Sserver1\instance1 -E -iC:\temp\SomeScript1.sql -oC:\temp\SomeScript1.out
xcopy \\server1\someshare\db1.bak \\server2\someshare
sqlcmd -Sserver2\instance1 -E -iC:\temp\SomeScript2.sql -oC:\temp\SomeScript2.out

SomeScript1.sql:
BACKUP DATABASE db1
TO DISK = 'E:\Backup\db1.bak'

SomeScript2.sql:
BRESTORE DATABASE db1
FROM DISK = 'E:\Backup\db1.bak'
WITH MOVE 'db1_Data' TO 'E:\Data\db1_data.mdf', MOVE 'db1_Data' TO 'E:\Log\db1_log.ldf', REPLACE

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

rammohan
Posting Yak Master

212 Posts

Posted - 2009-11-23 : 23:52:11

thank u ,i will try with it. if i got any issues, i will let u know.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-24 : 00:00:05

You're welcome.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources