Sign in to follow this  
ranakor

[web] sql server 2005 express database copy question

Recommended Posts

i'm looking for a way to copy a database over to another server but googling is leaving confused , there seems to be a ton of way to do this , for a ton of diferent reason , and i'm not sure wich one i should pick what i need is to have a copy (not for backup , for actual use on a offline server that doesn't have access to the real db but needs updated with it once a week or so) of my main db i made a lot/product management software for a company and there is a server that holds the db with all the lots/products/employees etc and gets updated from employees computers , i would like to get all those db copied over to another server that also has sql server in a programatical way (there is no one to administer there so it must be as close as possible to a deploy & forget app & as idiot proof as possible) for now i've seen 2 main solutions offered : 1)stop the actual server programatically & do a raw copy of the db files (possible & my favorite for now but still would rather have a way to do it without shutting down the service) 2)do a backup of it but it sounds overly complex for my need and i don't need to backup everything (logs etc) as i only need the raw data since it's not actually for backup (& even if it was , for my app , i don't care for backing that up) now the thing is , while it's not such a big deal if the server is down & someone actually uses the client app and it crashes , everyone will be confused & i know i can't count on the people there to avoid doing that wich is why i want to know if there is a way to do a simple DB backup with the server being online (the receiving server can be shutdown if needed tho) , having data up to date to the last second is not a big deal either so anything that allows me to do all of this simply be great (idealy i just really want some kind of copydbfromserverAtoBanddontbugme()) i'm using C# and there will be .net 3.5 deployed so anything in .net framework is fine

Share this post


Link to post
Share on other sites
Disclaimer:

a) I left the lovely world of database development shortly after MSSQL 2005 came out, so I'm not sure how compatible the following suggestions are.
b) The little I have worked with MSSQL 2005 involved they 'professional' edition, not the express edition. The latter may lack certain features.

What I used to do was make a backup of the entire database, copy the file to a new server, and restore it 'from device'. This was generally the fastest way of doing it, and it made sure that everything (including users) were copied. Just be aware you don't force a restore over an already existing database (unless you created it for that very purpose) and make sure you change the path to the mdf and ldf files in case the engine was installed in a different directory. To keep the the log files small, truncate them first. As far as I remember, this would typically reduce its size to 1,024 KB. In case 'auto-shrink' is turned on and the 'simple recovery method' is set, this may not even be necessary.

Another way of doing it is by using a DTS script, which can be generated by a import/export wizard. This is slower, and it will fail if the database is not 'correct' (i.e. stored procedures for non-existing tables, conflicting users, views on remote tables without a connection made etc.).

Thirdly, you can set up a replication. This is probably not what you want, since you do not need the database to be up to date all the time, setting it up involves more steps, and it certainly isn't faster.

None of the options require the database to be off line.

Now, I do vaguely remember and option 'copy database' in MSSQL 2005, but I'm not sure if this can be used over several servers.

Again, I'm not sure whether any of this advice translates to the 2005 express version. Good luck and happy new year! I'm on my way to a party...

Share this post


Link to post
Share on other sites
any clue how all of this could be done programatically? the backup & restore from device that is? does it use external tools or is are there raw sql statements to do this? (i really don't know anything about sql server hehe)

Share this post


Link to post
Share on other sites
thanks! that sample code seems to be working great althought it is for user database (& , considering i have no clue what that is , i don't think that's what i want) the backups i did with this api work just fine & restoring them manually did too . however, i have doubts about the restore code , i always need to restore the db fully (because i can't trust the pc it is being restored to to have some part of the db or even an empty db of the name) however i can pre choose the name that's np , i don't need to figure it out programmatically , how could i do this? the connection string already asks for the db name , can i just omit it for this purpose?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this