Is there a way to clone an entire database, without the data? Thanks for
any tips.Backup / restore + Clean-up the tables through scripts '
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||right click on the database in enterprise manager and generate sql script,
from there you can choose your options and get as detailed as you want.
thanks,
nivek
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Also, you can right-click on the database in the Query Analyzer and choose
Script Object to New Window As -> Create to generate a script for the schema
.
Changing options in the Query Analyzer will allow you to modify the amount
of detail generated (Tools -> Options -> Script Tab).
"Neil W." wrote:
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>
>|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||Thanks for the reply. Sorry to be dense, but which tool are you referring
to? I want to clone all aspects of the schema including triggers and
contraints.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message
news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Is there a way to clone an entire database, without the data? Thanks for
> any tips.
>|||The article lists several tools, quite simply. Use the one which serves your
purpose and does what
you want it to do. If you want to do this from code, for instance, consider
writing an app that uses
DMO. Etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Neil W." <neilw@.netlib.com> wrote in message news:%23WTtmWAJFHA.2852@.TK2MSFTNGP09.phx.gbl.
.
> Thanks for the reply. Sorry to be dense, but which tool are you referring
> to? I want to clone all aspects of the schema including triggers and
> contraints.
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eBc$ax$IFHA.2852@.TK2MSFTNGP09.phx.gbl...
>
>
Showing posts with label entire. Show all posts
Showing posts with label entire. Show all posts
Saturday, February 25, 2012
Clone SQL DB (using VB?)
Hi all,
I'm having a SQL Server database called X. For testing and training, I
would like to make a copy of the entire DB to Y. This copy should
include all data, properties, indexes, views, etc. Unfortunately, all
my attempts to automate this failed...
Server = MSDE2a
Original database is a database marked for replication so I can't make
an easy copy...
App = VB6
- SQL job: fails on certain tables due to large 'memo' fields (ntext >
10Mb).
- DAO: trying to copy the tables from dbX to dbY fails while
getting/setting properties. Even when getting the fields (for each
srcField in srcTable.Fields; set destField = srcField) gives errors...
- ADOX: Haven't been able to get all the properties and indexes across.
ADOX doesn't seem to have all methods available to accomplish that.
Current idea is to take a script from the DB, adjust the paths and
names and use that to generate the DB. Afterwards, loop through all
tables and copy the contents from dbX to dbY.
While I haven't looked into it, I might run into a problem because
probably not all tools are installed on the application PC and without
them so I can't run SQL scripts from the command line... Not sure if I
can distribute all tools (MSDE) to make that working.
Another thing might be that I need to find out what the relative path
is to the server in order to create the files at the correct locations.
They are different because the app is on PC1 and the SQLserver is on
PC2. So I need to update the script (that wouldn't be the big problem)
but I'm not sure on how this would be interpreted in the above
scenario...
Is this the correct way to copy a DB including indexes and properties
(like default values, etc.)? Or is there something easier?
Many thanks,
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Couldn't you simply backup the database on the source server and the copy
the backup file to a share on the destination server and do a restore?'
Jim
"Gijs Beukenoot" <Gijs.DOT.Beukenoot@.AT@.Wanadoo.DOT.nl> wrote in message
news:mn.ba4e7d52bd68e489.23024@.Wanadoo.DOT.nl...
> Hi all,
> I'm having a SQL Server database called X. For testing and training, I
> would like to make a copy of the entire DB to Y. This copy should include
> all data, properties, indexes, views, etc. Unfortunately, all my attempts
> to automate this failed...
> Server = MSDE2a
> Original database is a database marked for replication so I can't make an
> easy copy...
> App = VB6
> - SQL job: fails on certain tables due to large 'memo' fields (ntext >
> 10Mb).
> - DAO: trying to copy the tables from dbX to dbY fails while
> getting/setting properties. Even when getting the fields (for each
> srcField in srcTable.Fields; set destField = srcField) gives errors...
> - ADOX: Haven't been able to get all the properties and indexes across.
> ADOX doesn't seem to have all methods available to accomplish that.
> Current idea is to take a script from the DB, adjust the paths and names
> and use that to generate the DB. Afterwards, loop through all tables and
> copy the contents from dbX to dbY.
> While I haven't looked into it, I might run into a problem because
> probably not all tools are installed on the application PC and without
> them so I can't run SQL scripts from the command line... Not sure if I can
> distribute all tools (MSDE) to make that working.
> Another thing might be that I need to find out what the relative path is
> to the server in order to create the files at the correct locations. They
> are different because the app is on PC1 and the SQLserver is on PC2. So I
> need to update the script (that wouldn't be the big problem) but I'm not
> sure on how this would be interpreted in the above scenario...
> Is this the correct way to copy a DB including indexes and properties
> (like default values, etc.)? Or is there something easier?
> Many thanks,
> GB
> --
> Your eyes are weary from staring at the CRT. You feel sleepy. Notice how
> restful it is to watch the cursor blink. Close your eyes. The opinions
> stated above are yours. When I snap my fingers, you cannot imagine why you
> ever felt otherwise. <snap>
>
|||Jim Young drukte met precisie uit :
> Couldn't you simply backup the database on the source server and the copy the
> backup file to a share on the destination server and do a restore?'
> Jim
>
<<cut>>
Jim,
Both databases are on the same machine so that makes it a little easier
to do that. And, I've done that a few times (using Enterprise Manager
from another machine). The only problems I saw then is in the (odd)
naming of the databasefiles. I can't remember exactly, but I remember
the filenames where correct (dbY) but in some of the properties, the
internal name was still <path>\dbX. I'll admit, so far, this seems to
be the easiest way to accomplish all this. If I code it, the users
shouldn't be able to mess things up (like using EM to do this; it
really makes you read everything twice before you restore it the wrong
way...)
I'll guess I'll hop along that path then, thanks
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
I'm having a SQL Server database called X. For testing and training, I
would like to make a copy of the entire DB to Y. This copy should
include all data, properties, indexes, views, etc. Unfortunately, all
my attempts to automate this failed...
Server = MSDE2a
Original database is a database marked for replication so I can't make
an easy copy...
App = VB6
- SQL job: fails on certain tables due to large 'memo' fields (ntext >
10Mb).
- DAO: trying to copy the tables from dbX to dbY fails while
getting/setting properties. Even when getting the fields (for each
srcField in srcTable.Fields; set destField = srcField) gives errors...
- ADOX: Haven't been able to get all the properties and indexes across.
ADOX doesn't seem to have all methods available to accomplish that.
Current idea is to take a script from the DB, adjust the paths and
names and use that to generate the DB. Afterwards, loop through all
tables and copy the contents from dbX to dbY.
While I haven't looked into it, I might run into a problem because
probably not all tools are installed on the application PC and without
them so I can't run SQL scripts from the command line... Not sure if I
can distribute all tools (MSDE) to make that working.
Another thing might be that I need to find out what the relative path
is to the server in order to create the files at the correct locations.
They are different because the app is on PC1 and the SQLserver is on
PC2. So I need to update the script (that wouldn't be the big problem)
but I'm not sure on how this would be interpreted in the above
scenario...
Is this the correct way to copy a DB including indexes and properties
(like default values, etc.)? Or is there something easier?
Many thanks,
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Couldn't you simply backup the database on the source server and the copy
the backup file to a share on the destination server and do a restore?'
Jim
"Gijs Beukenoot" <Gijs.DOT.Beukenoot@.AT@.Wanadoo.DOT.nl> wrote in message
news:mn.ba4e7d52bd68e489.23024@.Wanadoo.DOT.nl...
> Hi all,
> I'm having a SQL Server database called X. For testing and training, I
> would like to make a copy of the entire DB to Y. This copy should include
> all data, properties, indexes, views, etc. Unfortunately, all my attempts
> to automate this failed...
> Server = MSDE2a
> Original database is a database marked for replication so I can't make an
> easy copy...
> App = VB6
> - SQL job: fails on certain tables due to large 'memo' fields (ntext >
> 10Mb).
> - DAO: trying to copy the tables from dbX to dbY fails while
> getting/setting properties. Even when getting the fields (for each
> srcField in srcTable.Fields; set destField = srcField) gives errors...
> - ADOX: Haven't been able to get all the properties and indexes across.
> ADOX doesn't seem to have all methods available to accomplish that.
> Current idea is to take a script from the DB, adjust the paths and names
> and use that to generate the DB. Afterwards, loop through all tables and
> copy the contents from dbX to dbY.
> While I haven't looked into it, I might run into a problem because
> probably not all tools are installed on the application PC and without
> them so I can't run SQL scripts from the command line... Not sure if I can
> distribute all tools (MSDE) to make that working.
> Another thing might be that I need to find out what the relative path is
> to the server in order to create the files at the correct locations. They
> are different because the app is on PC1 and the SQLserver is on PC2. So I
> need to update the script (that wouldn't be the big problem) but I'm not
> sure on how this would be interpreted in the above scenario...
> Is this the correct way to copy a DB including indexes and properties
> (like default values, etc.)? Or is there something easier?
> Many thanks,
> GB
> --
> Your eyes are weary from staring at the CRT. You feel sleepy. Notice how
> restful it is to watch the cursor blink. Close your eyes. The opinions
> stated above are yours. When I snap my fingers, you cannot imagine why you
> ever felt otherwise. <snap>
>
|||Jim Young drukte met precisie uit :
> Couldn't you simply backup the database on the source server and the copy the
> backup file to a share on the destination server and do a restore?'
> Jim
>
<<cut>>
Jim,
Both databases are on the same machine so that makes it a little easier
to do that. And, I've done that a few times (using Enterprise Manager
from another machine). The only problems I saw then is in the (odd)
naming of the databasefiles. I can't remember exactly, but I remember
the filenames where correct (dbY) but in some of the properties, the
internal name was still <path>\dbX. I'll admit, so far, this seems to
be the easiest way to accomplish all this. If I code it, the users
shouldn't be able to mess things up (like using EM to do this; it
really makes you read everything twice before you restore it the wrong
way...)
I'll guess I'll hop along that path then, thanks
GB
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>
Subscribe to:
Posts (Atom)