January 31, 2009 at 9:53 am
I presume that isn't only me that have this kind of needing. I develop all my application, database, tables, stored procedures and so one for one host provider, and a few months later, my client get into his head that he will change to another service provider to host his website.
As a small developer, I don't have a full server to install a complete set of the sql server; just the minimum of the Visual Studio & SQL Express.
Is there a way to copy all contents of one database to my local machine or for other remote server, on an easier way than making a giant script to recreate all objects and data to perform this?
I don't intend to buy a third part program because it would be difficult to repass the coast for my clients.
February 1, 2009 at 3:46 am
dude i dont know what you're smoking but i'd sure like some LOL
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2009 at 7:50 pm
The commands you need are BACKUP and RESTORE.
BACKUP will take the entire contents of a database (table definitions, stored procs etc plus any data) and "save" it to a file. You can then copy this file to another server and RESTORE it.
e.g. BACKUP DATABASE mydatabase TO DISK = 'C:\MyDatabaseBackup.bak'
RESTORE DATABASE mydatabase FROM DISK = 'C:\MyDatabaseBackup.bak'
The details of these commands can be found in SQL Server Books Online and at MSDN.
February 1, 2009 at 9:41 pm
I've dealt with a couple of shared hosting providers.(ihostasp.net,godaddy.com)
They always give you an interface so you can run SQL statements, batches, etc...you just have to realize, in a shared environment, you get some limitations as far as permissions go.
they give you a user that has rights just to a single database. you can backup and restore the database that is on your server, but backups from another server will give you problems.
if there was a user in that hosts' database named rkbsys, and you restore the database from a different server, your login would become orphaned, and you'd have to call them anyway to get them to fix/add your host sql username they gave you...
the user rkbsys on your local server is not the same rkbsys on the host server...they just happen to have the same name, but not the same unique Id (sid)
When first starting up, it's much easier to put your database on the folder they provide for your backups, and then have them restore it and fix/add your host's sql username to the database.
alternatively, you can script out all your objects, and script out the data for your tables with the script for generating insert statements from here:http://vyaskn.tripod.com
rkbsys (1/31/2009)
I presume that isn't only me that have this kind of needing. I develop all my application, database, tables, stored procedures and so one for one host provider, and a few months later, my client get into his head that he will change to another service provider to host his website.As a small developer, I don't have a full server to install a complete set of the sql server; just the minimum of the Visual Studio & SQL Express.
Is there a way to copy all contents of one database to my local machine or for other remote server, on an easier way than making a giant script to recreate all objects and data to perform this?
I don't intend to buy a third part program because it would be difficult to repass the coast for my clients.
Lowell
February 2, 2009 at 6:11 am
happycat59 (2/1/2009)
The commands you need are BACKUP and RESTORE.BACKUP will take the entire contents of a database (table definitions, stored procs etc plus any data) and "save" it to a file. You can then copy this file to another server and RESTORE it.
e.g. BACKUP DATABASE mydatabase TO DISK = 'C:\MyDatabaseBackup.bak'
RESTORE DATABASE mydatabase FROM DISK = 'C:\MyDatabaseBackup.bak'
The details of these commands can be found in SQL Server Books Online and at MSDN.
I don't have access to the server that is installed the SQL, just by FTP where my programs are. I can access the database by the url/login/password.
February 2, 2009 at 6:48 am
Lowell: Super! I got the script to generate the inserts, and it worked just fine. Now, I must to find another one that could generates other clauses for the stored procedures, tables and functions that I have.
Having those, I could correct the schema/owner to run them on the new server.
Thank you very much for the tip!
February 2, 2009 at 6:53 am
search the scripts section for "generate scripts"; there are plenty of examples there;
however, teh builtin stuff works great: rightclick on your db...tasks...generate scripts.
a wizard will appear and just tell it to script everything
Lowell
February 2, 2009 at 7:03 am
Unfortunately, on Scripting Resources of that site, there is only the message "Coming soon...". But I'll try to find other ones. Thanks once again. 🙂
February 2, 2009 at 8:01 am
i meant here on SqlServerCentral.
Teh script ssections contains thousands of script contributions to do a variety of things; there's at least half a dozen to help you scritp out your objects.
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply