Tranfer of a SQL Server Express Database

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

Tranfer of a SQL Server Express Database

Author

Topic

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-31 : 20:55:05

Hi All,

Please help me in planning a database being tranferred from one location to another location.

Currently the Database is functioning at the Datacentre, and have been asked to be kept internal, going forward.

How can I do this, please can anyone help me in this doing the homework before I proceed.

For instance, how would be the backup plan executed, and how could I automate.

the other task that I would like to ask about the indexing, is there a possibilit in doing the indexing as well.

PS : SQL Server Express 2005

Thanks,

AB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 21:00:46

can you afford downtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-31 : 21:21:58

could you elaborate on that please - downtime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 21:49:54

is it ok if db becomes unavailable during the transfer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-31 : 21:52:10

during the tranfer is fine, but for sure, not after the transfer..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 21:54:26

then you can use attach detach method.
detach the db from datacenter server and attach it to local server set up in your place

see below for more details

http://msdn.microsoft.com/en-us/library/ms190794(v=sql.100).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-31 : 22:00:22

Thanks for that, why cant I bakcup the database from the datacentre, and then restore it to the client site. (are those 2 diffent processes, 1) detach and attach 2) backup and restore.

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-31 : 22:12:18

and finally how do I automate the backup process..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 22:31:22

quote:
Originally posted by benildusmuerling

Thanks for that, why cant I bakcup the database from the datacentre, and then restore it to the client site. (are those 2 diffent processes, 1) detach and attach 2) backup and restore.

backup restore will create a copy. I thought intention was move db itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-31 : 22:36:48

intention was to move itself for your information, but would want to like processes of those 2 approaches

1) I take a backup from the Datacentre, and then restore it to the local or the client site, and the continue working - (whats the process or the outcome)
2) Detach and attach the database, and then continue working (whats the process or the outcome)
3) Finally, how to automate the backups as the GUI does not support for the express editioin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 22:56:52

both 1 & 2 will do job for you but in 1 you'll be getting a copy of db with main copy still in datacenter server as specified before

for automating you should be doing them via t-sql scripts rather than using GUI

for backup/restore use BACKUP and RESTORE commands

see

http://msdn.microsoft.com/en-us/library/ms186865.aspx

http://msdn.microsoft.com/en-us/library/ms186858.aspx

for attach detach see

http://blog.sqlauthority.com/2007/08/24/sql-server-2005-t-sql-script-to-attach-and-detach-database/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-19 : 20:17:21

Hi

I was able to detach and attach, but in some or rather way, it does not seem like succceeding.
but ODBC does seem like connecting this time.

should I install with default instance or named instance when it comes to SQL server express edition.

Thanks,

AB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 20:25:48

what do you mean by it does not seem like succeeding?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-06-19 : 20:56:11

earlier infrastructure [Application inhouse, Database different locaton, this was working really fine]
newer aim [Application inhouse, bring the database too inhouse, did not work as I could not pass the hurdle of ODBC connection]

while working towards the newer aim, I found below

The detach and attach was successful, and I was able to talk to the database via the SQL editor by hitting a query, SELECT * FROM tablename, the problem was, I am unable to talk to the user application via the ODBC.

Whats your advice.

Thanks,

AB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources