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.
| Author |
Topic |
|
khurram
Starting Member
7 Posts |
 Posted - 2004-07-29 : 04:19:39
|
| Is there any way that we can create the TSql file having DDL and DML statements resulting creating the database schema and data.Something like mysqldump [1] does, so that during setting up the application at client end, the setup program connect to database and execute these statements.[1] http://dev.mysql.com/doc/mysql/en/mysqldump.html |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-29 : 05:48:45
|
| If you just BACKUP the database the backup file will contain all the structure AND data. You can then just restore that backup onto your new installations and away you go.Is that the sort of thing you are after?Kristen |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-07-29 : 06:19:30
|
| You can do a backup and restore like Kristen suggests or a detach and attach, both should work, but you would probably have to look at other options if you have rather large database and you want an installer that is downloadable. In that case you could use a third party product like SQLPackager (http://www.red-gate.com/sql/sql_packager.htm) to do this as scripts, which would probably be a bit lighter than a database backup.OS |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-29 : 06:47:11
|
| Any figures for how they compare? I was assuming that the clutter of puntuation etc. in a script of INSERT statements would offset the overhead in a Backup file ... but!Kristen |
|
|
|
khurram
Starting Member
7 Posts |
Posted - 2004-07-29 : 08:18:49
|
| Backup or Attach/Detach assumes preserve the security settings?Isnt mysqldump approach an elegant solution? One can easily update the statements if required. It would be nice to have something similar for MS-SQL |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-29 : 08:25:37
|
| Backup and detach/copy/attach will preserve the database level security settings. You will need to transfer the server level security settings using sp_help_revlogin though. This creates a permission script that is ran "after" the database is restored or attached.I think the backup or detach/copy/attach is actually more efficient and elegant than mysqldump. It's much less time consuming and processor intensive to just restore the backup or attach the database, then it is to completely populate it. Of course, it wouldn't be AS slow on mySQL because mySQL doesn't enforce RI properly. DOH!!!MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
khurram
Starting Member
7 Posts |
Posted - 2004-07-29 : 08:50:49
|
| So if we restore it on the server at the client end, that will not have the SQL logins, restoring database or mounting it will screw things up?Whats the best solution to create Application Installations that uses SQL (MSDE) as its data-store? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-29 : 08:57:57
|
| After you run the restore or attach, you just need to run a permissions script to create the users. If you run sp_help_revlogin on the source server's master database, it will give you the user permissions script you need. You just need to have your setup program run that script in the master database on the destination server "after" it restored or attaches the database. Make sense?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Sql dump (Schema + Data)