DB2OLEDB Query Parameters in OLE DB Sources

Hi

I'm using Microsoft DB2 OLE DB Driver to access a DB2 database, and I have a problem when I create an OLE DB Source using a parameterized query. Everytime I push the "Parameters" button, I get this error:

TITLE: Microsoft Visual Studio

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

ADDITIONAL INFORMATION:

El proveedor no puede derivar la información del parámetro, no se llamó a SetParameterInfo. (Microsoft DB2 OLE DB Provider)

I would really appreciatte any help.

Thanks.

Well, the message looks pretty clear to me. The provider you are using does not support an interface used to derive parameters from queries so parametrized queries cannot be used in that source adapter.

The workaround is to use "Sql command from variable" access mode and build your query in a single variable.

Thanks,

Bob

|||

Hi

First of all, thanks for your answer. I know I can use the "Sql command from variable" workaround, for that matter I could also use a DataReader Source instead of an OLE DB Source and use an expression to build the query. The thing is, I was wondering if there was a more recent version of the driver that would allow me to build parametrized queries the same way I build them when accessing a MS SQL Server database. I know I should have been more specific when I posted my question, for that I apologize.

Right now I'm evaluating Microsoft's and IBM's DB2 providers, and both have certain limitations. For instance when I use MS provider and the code page of the DB2 database is improperly configured, the development studio simply vanishes when I try to create a Data Flow. I know is not that big of a problem, I just need to be more careful when setting up the connections properties, but still it took me a while to realize what was going on, probably because I have little experience with MS SQL Server.

My point is, I was hoping to access DB2 the same way I access any other database, instead of having to change the way I do things depending on the provider used to set up the connection, but I supposse this is the way things are.

Thanks again for your answer.

Cheers.

|||

Hi

It looks like it is possible to use parametrized queries. I just had to change the value of "Derive parameters" to true in the "All" tab.

Thanks again.

sql

DB2OLEDB issue - possible bug

I have several SSIS packages where I am connecting to a DB2 catalog to grab data on a nightly basis. These packages drop/create and reload data from DB2 to both my 2005, 32 bit and 64 bit servers. In the Connection Properties of the packages that reside on my 64 bit server, I am noticing a quirk in setting the password for the DB2 catalog. If I go in and manually type in the password and test the connection, everything is fine. The package will run either right there through Debug Mode, or at the next run time, without any issues. However, as soon as it runs the very next time, the password for the DB2 catalog reverts back to a blank password and the package will fail when it runs the next/second time. I have unchecked the Blank Password box and checked the Allow For Saved Password box on BOTH the Data Link page and the Connection Properties page. But, this does not resolve the issue. It is almost as if it is ignoring the Allow For Saved Password.

The packages are running fine in the 32 bit environment, by the way. I am only experiencing this issue on the 64 bit server.

Thnx in advance, for any suggestions or answers.

ScottyeOK... never mind... I figured it out. You have to set the RetainSameConnection value from False to True in the properties of the connection.

DB2OLEDB issue - possible bug

I have several SSIS packages where I am connecting to a DB2 catalog to grab data on a nightly basis. These packages drop/create and reload data from DB2 to both my 2005, 32 bit and 64 bit servers. In the Connection Properties of the packages that reside on my 64 bit server, I am noticing a quirk in setting the password for the DB2 catalog. If I go in and manually type in the password and test the connection, everything is fine. The package will run either right there through Debug Mode, or at the next run time, without any issues. However, as soon as it runs the very next time, the password for the DB2 catalog reverts back to a blank password and the package will fail when it runs the next/second time. I have unchecked the Blank Password box and checked the Allow For Saved Password box on BOTH the Data Link page and the Connection Properties page. But, this does not resolve the issue. It is almost as if it is ignoring the Allow For Saved Password.

The packages are running fine in the 32 bit environment, by the way. I am only experiencing this issue on the 64 bit server.

Thnx in advance, for any suggestions or answers.

ScottyeOK... never mind... I figured it out. You have to set the RetainSameConnection value from False to True in the properties of the connection.

DB2 ODBC Connection Problems

Hello,

I've recently run across a couple of problems trying to connect to DB2 using the .NET ODBC Provider.

1) I can create a DataSource using the DataSource designer, and when I edit the connection, the User Name and Password text boxes are empty. If I fill in the correct information and hit "Test Connection", I am able to connect. However, this information is not saved in the DataSource the next time I open it or try to run a Data Flow task that uses it! Is there some property I can set to fix this?

2) When I open up a solution, the DataSource appears to be trying to connect to the DB2 server, and perhaps there is no valid User Name and Password, the connection is refused and I eventually get locked out of my account. This could be related to the issue above.

Does anyone have any experience connecting to DB2 using either an ODBC driver or the perhaps the IBM DB2 UDB iSeries OLE DB providers?

Thanks in advance,

Mark

Mark,

The same problem occurs with Progress Database using .NET ODBC Provider and a didn′t find a solution. Problably is a .NET ODBC Provider problem but I don′t know how to fix. If you find a solution, please post here.

Question: Do you receive this error message ?

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Thanks.

Rodrigo Chagas

|||

I found this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=93045&SiteID=1

Maybe will be useful for you. It seems ODBC connections are having a lot of problems in SQL 2005.

My hope is getting smaller...

|||This is not a problem with .Net, but rather the way in which the ODBC driver builds the connection string or handles security. Some drivers including the CA ODBC driver do not append the password property to the connection string. So when you attempt to connect it will prompt for a password. Depending on how you invoke the program, you may not even see the prompt.
One way to resolve the issue is to append the password to your connection string in the form of "Password=MyPass;"
I've used the OLEDB provider for DB2 before, but there are some limitations to it. Per IBM, OLEDB does not provide the same level of functionality as their ODBC drivers. One thing that OLEDB does not provide is scrollable cursors.
I haven't used DB2 in almost a year, so IBM may have some updated drivers. Also MS just released their version of an OLEDB provider for DB2, so you could try it. You can get it at the following url.
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
Larry

db_owner problem

I recently registered my hosted database server on my pc. When I ran aspnet_regsql.exe the table created have dbo as schema, but when I create a news table throught stored procedure it has my username as schema.
When I buildt my site on my pc all tables have dbo as schema.
How do I make all tables on my hosted server db have same schema?

Thanks

The very Newbie

The schema of new objects is determined by the default schema of current database user. So just make sure all database users have the same default schema (for example). You can easily do this in Management Studio.|||

Thanks for your reply Iori_Jay.

Where and how in Management Studio do I make sure that default schema is MyUsername(current database user) instead of getting schema "dbo" for some tables and schema "MyUsername" for other tables?
When I ran aspnet_regsql.exe all tables and procedures have schema dbo allthough I use my username to create them.

Thanks again

The Very Newbie
We learn from masters

|||You can check the defaut schema of a database user by checkin the Properties of the user under SQL instance->Databases->your database->Security->Users.|||

Thanks again Iori_Jay,

In the security folder of my database there are 2 users: dbo and myself. I need to transfer schema, but don´t know how to make it work. My hosted server uses sql server 2000.

Thanks

|||

In SQL 2000 SHCEMA is not a seperated object, it equals to the owner of the object. So you need to change to owner of the object using such command:

EXEC sp_changeobjectowner 'myself.tbl1', 'dbo'

|||

Thanks a lotIori_Jay.

It works fine.

sql

DB UpDate Error

The clients are able to connect and pull data on existing accounts. However when users try to create a new version of an existing account or an actual new account, they get:

Db UpDate Error

ERROR 40002 01000: [Microsoft][ODBC SQL Server Driver][SQL Server]Command has been aborted.

Unable To Set Version Info. MODE: NEW

Class[APPOBJ.SerVersion]

Is this some type of version conflict? I've tried finding more information about the Error 40002 but I don't find the one specific to mine. I'm not an SQL expert but I would like to try and find out what the error message is trying to tell me. Any help would be greatly appreicated.

I thought I would add we are using SQL 6.5 and Access 97. Server is running NT 4.00.1381 and clients are running WinXP.sql

DB Sizes

hi,

i'm planning to create a DB of about 1TB of text.

can SQL EXPRESS handle this sizes of data? what about SQL 2005?

does it matter if i put all the data in 1 table?

Thanks.

SQL Express can handle a db size of maximum 4 GB(max db size) ! ! pls refer the below link,
http://download.microsoft.com/download/4/2/c/42c6af76-a28c-4560-8269-e8fe906ea675/SQLExpressdatasheet.pdf

for sql 2005 pls refer the below link,
http://technet.microsoft.com/en-us/library/ms143432.aspx

you can go for table partioning in sql 2005 refer
http://sql-articles.com/articles/part/PARTITIONING.htm

Thanxx
Deepak

DB Script

hello I need to script my Db, tables and storedprocedures. so i can create another same DB

Dim con As New SqlConnection(DBClass.Config.DBString)
Dim cmdName As String = File.OpenText("pro.sql").ReadToEnd()
Dim cmd As New SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text
con.Open()
Try
cmd.ExecuteNonQuery()
MessageBox.Show("DB Created")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

con.Close()

Any suggestions plzzzzzzzzzzz

you can right click on your DB and generate scripts for all objects in your DB. If you want to do it programmatically run the profiler and see what mgmt studio is doing to generate the scripts. You can do the same from your application.|||If SMO is used (liek in SSMS) There is some more information needed than this which is queried fromt he SQl Server. I would suggest using the SMO namespace for creating your script. SMO is the successor of DMO and has a dependency walker to keep track of dependent objects.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

if ur using sqlserver 2000 make use of the sql DMO functions...simple functions to achive wat u want... examples at C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\Sqldmo

similarly for SS 2005 though DMO are supported(just for backward compatibility,wont be there in future editions), u can use SMO,which offers many additional functions...

|||Can you give me some more details about that plz|||have you looked in the folder in the post?

There should be a demo for you to look at which should give you more info as you requested.|||

I have no problem creating Data Base, I just want to have SQLScript(tables and procedures), this file called db.sql
Then I want to use this code, why Im having problem creating the procedures?

Dim con As New SqlConnection(DBClass.Config.DBString)
Dim cmdName As String = File.OpenText("db.sql").ReadToEnd()
Dim cmd As New SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text
con.Open()
Try
cmd.ExecuteNonQuery()
MessageBox.Show("DB Created")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

con.Close()

|||hmmm,

we seem to be duplicating ourselves as you have an open topic here; http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006948&SiteID=1

In this thread I provide you with a code example, the example lets you read a script in a StreamReader, then execute this script on a database.

Please be careful you dont upset people posting the same questions in multiple topics

DB Role Security

Hello,
My mananger has asked me to see if it possible to allow
developers the rights to modify table structures only, and
not create, delete tables, and not create, modify and
delete views store procedures.
I am going to tell him "No" as I will need to give the
developers dlladmin access rights which allows full object
modification rights.
What do people think ?
PeterYou are partially right - you can grant the "Create Table" permission only,
which includes Alter and Drop Table permissions, but does not include
permissions to create, alter or drop views, functions and procedures.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:0b9301c36253$57729300$a601280a@.phx.gbl...
> Hello,
> My mananger has asked me to see if it possible to allow
> developers the rights to modify table structures only, and
> not create, delete tables, and not create, modify and
> delete views store procedures.
> I am going to tell him "No" as I will need to give the
> developers dlladmin access rights which allows full object
> modification rights.
> What do people think ?
> Peter|||Thanks Dejan,
Where do I set that option ?
Thanks
Peter
>--Original Message--
>You are partially right - you can grant the "Create
Table" permission only,
>which includes Alter and Drop Table permissions, but does
not include
>permissions to create, alter or drop views, functions and
procedures.
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
>news:0b9301c36253$57729300$a601280a@.phx.gbl...
>> Hello,
>> My mananger has asked me to see if it possible to allow
>> developers the rights to modify table structures only,
and
>> not create, delete tables, and not create, modify and
>> delete views store procedures.
>> I am going to tell him "No" as I will need to give the
>> developers dlladmin access rights which allows full
object
>> modification rights.
>> What do people think ?
>> Peter
>
>.
>|||Not sure on this, but may be possible to make developers
data reader and data writer (if relevant) and give explicit
GRANT ALTER TABLE TO <username>
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:0b9301c36253$57729300$a601280a@.phx.gbl...
> Hello,
> My mananger has asked me to see if it possible to allow
> developers the rights to modify table structures only, and
> not create, delete tables, and not create, modify and
> delete views store procedures.
> I am going to tell him "No" as I will need to give the
> developers dlladmin access rights which allows full object
> modification rights.
> What do people think ?
> Petersql

DB Restore

What's the best way to restore or create a copy of production db (15 GB) on
development server and also another copy on same sql server with different
name.
The copies need to be refreshed on a monthly basis.
1. For doing above things what's best and can some one post sctips or TSQL
statements to do so...
2. what's the importance of move statement in a restore?
Thanks
BVRIf it only needs to be done monthly, backup / restore is probably the
easiest option.
eg:
--on prod server
backup database [dbname] to disk=c:\dbname.fbak
--on dev server
restore database [dbname] from disk=c:\dbname.fbak
You'll probably be able to access the .fbak from your existing production
backups. The above assumes that the database already exists. The database
can be created initially by issuing the restore command with replace with
replace, move etc. The point of the move statement is that, when you create
a fresh database from a backup, the o/s files that support the database need
to be given a directory location. If you're creating the new database on the
same server as the database that the backup came from, a different directory
will need to be given for the o/s files under the new db.
Regards,
Greg Linwood
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> What's the best way to restore or create a copy of production db (15 GB)
on
> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>|||Uhway
Take a look at WITH MOVE option of RESTORE command in BOL.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> What's the best way to restore or create a copy of production db (15 GB)
on
> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>|||Hi,
Fast method will be ,
1. From the actual server, Execute SP_DETACH DB database name
2. COpy the mdf and ldf files to a diff location
3.USE SP_ATTACH_DB in actual server to connect the database back
4. From the copied location copy the files to devolpment server
5. From Devolopment server Query ANalyzer execute SP_ATTACH_DB to attach the
database
6. In the actual server use SP_ATTACH_DB command . Use the filenames from
the copied folder while attaching.
Refer BOL for SP_ATTACH_DB and SP_DETACH_DB
Otherwise you can use RETORE DATABASE command with move option. But this
will take longer to restore the 15 GB database.
Thanks
Hari
MCDBA
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> What's the best way to restore or create a copy of production db (15 GB)
on
> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>|||This is ok as long as you don't mind taking the production database
offline..
Regards,
Greg Linwood
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u5MCby86DHA.1632@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Fast method will be ,
> 1. From the actual server, Execute SP_DETACH DB database name
> 2. COpy the mdf and ldf files to a diff location
> 3.USE SP_ATTACH_DB in actual server to connect the database back
> 4. From the copied location copy the files to devolpment server
> 5. From Devolopment server Query ANalyzer execute SP_ATTACH_DB to attach
the
> database
> 6. In the actual server use SP_ATTACH_DB command . Use the filenames from
> the copied folder while attaching.
> Refer BOL for SP_ATTACH_DB and SP_DETACH_DB
> Otherwise you can use RETORE DATABASE command with move option. But this
> will take longer to restore the 15 GB database.
>
> Thanks
> Hari
> MCDBA
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > What's the best way to restore or create a copy of production db (15 GB)
> on
> > development server and also another copy on same sql server with
different
> > name.
> >
> > The copies need to be refreshed on a monthly basis.
> >
> > 1. For doing above things what's best and can some one post sctips or
TSQL
> > statements to do so...
> >
> > 2. what's the importance of move statement in a restore?
> >
> > Thanks
> > BVR
> >
> >
> >
>|||On the same server, I need to restore on to a 6 month old copy (with
different name). The o/s files already exist. Still, Do I need to use move
statements in this case?
The main problem is, the ex dba created data and log files for the original
production db and for copy in the same directory. I don't want to restore
on to a original o/s files (loose data during restore) and it need to be
scheduled. Also, no chance to test before scheduling.
Thanks
BVR
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> If it only needs to be done monthly, backup / restore is probably the
> easiest option.
> eg:
> --on prod server
> backup database [dbname] to disk=c:\dbname.fbak
> --on dev server
> restore database [dbname] from disk=c:\dbname.fbak
> You'll probably be able to access the .fbak from your existing production
> backups. The above assumes that the database already exists. The database
> can be created initially by issuing the restore command with replace with
> replace, move etc. The point of the move statement is that, when you
create
> a fresh database from a backup, the o/s files that support the database
need
> to be given a directory location. If you're creating the new database on
the
> same server as the database that the backup came from, a different
directory
> will need to be given for the o/s files under the new db.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > What's the best way to restore or create a copy of production db (15 GB)
> on
> > development server and also another copy on same sql server with
different
> > name.
> >
> > The copies need to be refreshed on a monthly basis.
> >
> > 1. For doing above things what's best and can some one post sctips or
TSQL
> > statements to do so...
> >
> > 2. what's the importance of move statement in a restore?
> >
> > Thanks
> > BVR
> >
> >
> >
>|||I'm not 100% that I understand what you mean here, but if you want to
restore a 6 month old backup onto the same server, leaving the existing
database there then you do need to use the with move option in the restore
command (that's basically what it's for). Of course, you'd also need to give
the database a new name too..
Regards,
Greg Linwood
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uDcSoCG7DHA.4060@.tk2msftngp13.phx.gbl...
> On the same server, I need to restore on to a 6 month old copy (with
> different name). The o/s files already exist. Still, Do I need to use
move
> statements in this case?
> The main problem is, the ex dba created data and log files for the
original
> production db and for copy in the same directory. I don't want to restore
> on to a original o/s files (loose data during restore) and it need to be
> scheduled. Also, no chance to test before scheduling.
> Thanks
> BVR
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > If it only needs to be done monthly, backup / restore is probably the
> > easiest option.
> >
> > eg:
> > --on prod server
> > backup database [dbname] to disk=c:\dbname.fbak
> > --on dev server
> > restore database [dbname] from disk=c:\dbname.fbak
> >
> > You'll probably be able to access the .fbak from your existing
production
> > backups. The above assumes that the database already exists. The
database
> > can be created initially by issuing the restore command with replace
with
> > replace, move etc. The point of the move statement is that, when you
> create
> > a fresh database from a backup, the o/s files that support the database
> need
> > to be given a directory location. If you're creating the new database on
> the
> > same server as the database that the backup came from, a different
> directory
> > will need to be given for the o/s files under the new db.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> > news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > What's the best way to restore or create a copy of production db (15
GB)
> > on
> > > development server and also another copy on same sql server with
> different
> > > name.
> > >
> > > The copies need to be refreshed on a monthly basis.
> > >
> > > 1. For doing above things what's best and can some one post sctips or
> TSQL
> > > statements to do so...
> > >
> > > 2. what's the importance of move statement in a restore?
> > >
> > > Thanks
> > > BVR
> > >
> > >
> > >
> >
> >
>|||By default, a restore command will use the same file names as the original
database. You can use the WITH MOVE option to specify different paths. Use
the FILELISTONLY option to list the logical file names that you'll need to
specify on the MOVE clause. For example:
--list logical file Names
RESTORE FILELISTONLY
FROM DISK='c:\MyDatabase.bak'
--restore db with different physical file names
RESTORE DATABASE MyDatabase
FROM DISK='c:\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\Data\MyDatabase_Restored.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Data\MyDatabase_Log_Restored.ldf',
STATS=10
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uDcSoCG7DHA.4060@.tk2msftngp13.phx.gbl...
> On the same server, I need to restore on to a 6 month old copy (with
> different name). The o/s files already exist. Still, Do I need to use
move
> statements in this case?
> The main problem is, the ex dba created data and log files for the
original
> production db and for copy in the same directory. I don't want to restore
> on to a original o/s files (loose data during restore) and it need to be
> scheduled. Also, no chance to test before scheduling.
> Thanks
> BVR
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > If it only needs to be done monthly, backup / restore is probably the
> > easiest option.
> >
> > eg:
> > --on prod server
> > backup database [dbname] to disk=c:\dbname.fbak
> > --on dev server
> > restore database [dbname] from disk=c:\dbname.fbak
> >
> > You'll probably be able to access the .fbak from your existing
production
> > backups. The above assumes that the database already exists. The
database
> > can be created initially by issuing the restore command with replace
with
> > replace, move etc. The point of the move statement is that, when you
> create
> > a fresh database from a backup, the o/s files that support the database
> need
> > to be given a directory location. If you're creating the new database on
> the
> > same server as the database that the backup came from, a different
> directory
> > will need to be given for the o/s files under the new db.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> > news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > What's the best way to restore or create a copy of production db (15
GB)
> > on
> > > development server and also another copy on same sql server with
> different
> > > name.
> > >
> > > The copies need to be refreshed on a monthly basis.
> > >
> > > 1. For doing above things what's best and can some one post sctips or
> TSQL
> > > statements to do so...
> > >
> > > 2. what's the importance of move statement in a restore?
> > >
> > > Thanks
> > > BVR
> > >
> > >
> > >
> >
> >
>

DB Restore

What's the best way to restore or create a copy of production db (15 GB) on
development server and also another copy on same sql server with different
name.
The copies need to be refreshed on a monthly basis.
1. For doing above things what's best and can some one post sctips or TSQL
statements to do so...
2. what's the importance of move statement in a restore?
Thanks
BVRPlease do not post the same message in multiple groups like this. If your
subject is relevant to > 1 Newsgroup then you can include them all on the
"To" line.
See my answer in .dts
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uQNVLT56DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What's the best way to restore or create a copy of production db (15 GB)

---

on

quote:

---

> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>

---

DB Restore

What's the best way to restore or create a copy of production db (15 GB) on
development server and also another copy on same sql server with different
name.
The copies need to be refreshed on a monthly basis.
1. For doing above things what's best and can some one post sctips or TSQL
statements to do so...
2. what's the importance of move statement in a restore?
Thanks
BVRIf it only needs to be done monthly, backup / restore is probably the
easiest option.
eg:
--on prod server
backup database [dbname] to disk=c:\dbname.fbak
--on dev server
restore database [dbname] from disk=c:\dbname.fbak
You'll probably be able to access the .fbak from your existing production
backups. The above assumes that the database already exists. The database
can be created initially by issuing the restore command with replace with
replace, move etc. The point of the move statement is that, when you create
a fresh database from a backup, the o/s files that support the database need
to be given a directory location. If you're creating the new database on the
same server as the database that the backup came from, a different directory
will need to be given for the o/s files under the new db.
Regards,
Greg Linwood
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What's the best way to restore or create a copy of production db (15 GB)

---

on

quote:

---

> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>

---

|||Uhway
Take a look at WITH MOVE option of RESTORE command in BOL.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What's the best way to restore or create a copy of production db (15 GB)

---

on

quote:

---

> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>

---

|||Hi,
Fast method will be ,
1. From the actual server, Execute SP_DETACH DB database name
2. COpy the mdf and ldf files to a diff location
3.USE SP_ATTACH_DB in actual server to connect the database back
4. From the copied location copy the files to devolpment server
5. From Devolopment server Query ANalyzer execute SP_ATTACH_DB to attach the
database
6. In the actual server use SP_ATTACH_DB command . Use the filenames from
the copied folder while attaching.
Refer BOL for SP_ATTACH_DB and SP_DETACH_DB
Otherwise you can use RETORE DATABASE command with move option. But this
will take longer to restore the 15 GB database.
Thanks
Hari
MCDBA
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> What's the best way to restore or create a copy of production db (15 GB)

---

on

quote:

---

> development server and also another copy on same sql server with different
> name.
> The copies need to be refreshed on a monthly basis.
> 1. For doing above things what's best and can some one post sctips or TSQL
> statements to do so...
> 2. what's the importance of move statement in a restore?
> Thanks
> BVR
>
>

---

|||This is ok as long as you don't mind taking the production database
offline..
Regards,
Greg Linwood
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u5MCby86DHA.1632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi,
> Fast method will be ,
> 1. From the actual server, Execute SP_DETACH DB database name
> 2. COpy the mdf and ldf files to a diff location
> 3.USE SP_ATTACH_DB in actual server to connect the database back
> 4. From the copied location copy the files to devolpment server
> 5. From Devolopment server Query ANalyzer execute SP_ATTACH_DB to attach

---

the

quote:

---

> database
> 6. In the actual server use SP_ATTACH_DB command . Use the filenames from
> the copied folder while attaching.
> Refer BOL for SP_ATTACH_DB and SP_DETACH_DB
> Otherwise you can use RETORE DATABASE command with move option. But this
> will take longer to restore the 15 GB database.
>
> Thanks
> Hari
> MCDBA
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> on
different[QUOTE]
TSQL[QUOTE]
>

---

|||On the same server, I need to restore on to a 6 month old copy (with
different name). The o/s files already exist. Still, Do I need to use move
statements in this case?
The main problem is, the ex dba created data and log files for the original
production db and for copy in the same directory. I don't want to restore
on to a original o/s files (loose data during restore) and it need to be
scheduled. Also, no chance to test before scheduling.
Thanks
BVR
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
> If it only needs to be done monthly, backup / restore is probably the
> easiest option.
> eg:
> --on prod server
> backup database [dbname] to disk=c:\dbname.fbak
> --on dev server
> restore database [dbname] from disk=c:\dbname.fbak
> You'll probably be able to access the .fbak from your existing production
> backups. The above assumes that the database already exists. The database
> can be created initially by issuing the restore command with replace with
> replace, move etc. The point of the move statement is that, when you
create
> a fresh database from a backup, the o/s files that support the database
need
> to be given a directory location. If you're creating the new database on
the
> same server as the database that the backup came from, a different
directory
> will need to be given for the o/s files under the new db.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:uoyp0S56DHA.1636@.TK2MSFTNGP12.phx.gbl...
> on
different
TSQL
>|||I'm not 100% that I understand what you mean here, but if you want to
restore a 6 month old backup onto the same server, leaving the existing
database there then you do need to use the with move option in the restore
command (that's basically what it's for). Of course, you'd also need to give
the database a new name too..
Regards,
Greg Linwood
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uDcSoCG7DHA.4060@.tk2msftngp13.phx.gbl...
> On the same server, I need to restore on to a 6 month old copy (with
> different name). The o/s files already exist. Still, Do I need to use
move
> statements in this case?
> The main problem is, the ex dba created data and log files for the
original
> production db and for copy in the same directory. I don't want to restore
> on to a original o/s files (loose data during restore) and it need to be
> scheduled. Also, no chance to test before scheduling.
> Thanks
> BVR
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
production
database
with
> create
> need
> the
> directory
GB)
> different
> TSQL
>|||By default, a restore command will use the same file names as the original
database. You can use the WITH MOVE option to specify different paths. Use
the FILELISTONLY option to list the logical file names that you'll need to
specify on the MOVE clause. For example:
--list logical file Names
RESTORE FILELISTONLY
FROM DISK='c:\MyDatabase.bak'
--restore db with different physical file names
RESTORE DATABASE MyDatabase
FROM DISK='c:\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\Data\MyDatabase_Restored.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Data\MyDatabase_Log_Restored.ldf',
STATS=10
Hope this helps.
Dan Guzman
SQL Server MVP
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:uDcSoCG7DHA.4060@.tk2msftngp13.phx.gbl...
> On the same server, I need to restore on to a 6 month old copy (with
> different name). The o/s files already exist. Still, Do I need to use
move
> statements in this case?
> The main problem is, the ex dba created data and log files for the
original
> production db and for copy in the same directory. I don't want to restore
> on to a original o/s files (loose data during restore) and it need to be
> scheduled. Also, no chance to test before scheduling.
> Thanks
> BVR
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uAyN3Y56DHA.2412@.TK2MSFTNGP09.phx.gbl...
production
database
with
> create
> need
> the
> directory
GB)
> different
> TSQL
>

db owner

We are using SQL Express and I have a problem with dbo.
On my machine if I logon to SQL Express using windows authentication
and create a new database I automatically have db_owner role membership
on the new database.
On a colleagues machine, if he logs onto his SQL Express using windows
authentication and creates a new database he does NOT have db_owner role
membership on the new database.
How come?
I have checked pretty much everythng - windows built in admins, SQL express
sysadmin roles, service pack versions but I can not find any difference in
setup.
What should I do to his machine/SQL Express setup so he automatically has
db_owner role membership for every newly created database?
Thanks
CharlieCharlie
What if you create login as the following
CREATE LOGIN Charlie WITH password='pp'
CREATE USER Charlie FOR LOGIN Charlie
WITH DEFAULT_SCHEMA=dbo
EXEC sp_addrolemembe 'db_owner','Charlie'
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:0ECAEB63-1450-40C9-B51D-01F5F4C9B92E@.microsoft.com...
> We are using SQL Express and I have a problem with dbo.
> On my machine if I logon to SQL Express using windows authentication
> and create a new database I automatically have db_owner role membership
> on the new database.
> On a colleagues machine, if he logs onto his SQL Express using windows
> authentication and creates a new database he does NOT have db_owner role
> membership on the new database.
> How come?
> I have checked pretty much everythng - windows built in admins, SQL
> express
> sysadmin roles, service pack versions but I can not find any difference in
> setup.
> What should I do to his machine/SQL Express setup so he automatically has
> db_owner role membership for every newly created database?
>
> Thanks
> Charlie
>

DB Maintenance Plan

I have a question on DB Maintenance Plan:
If we create a plan with "Reorganize Data and Index Pages"
selected only, and running the job weekly, do we also need
to set up another job to run sp_updatestats?
Thanks,
Vickyyes
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Vicky" <vickyfan@.hotmail.com> wrote in message
news:7a7901c402e1$a724f210$a301280a@.phx.gbl...
> I have a question on DB Maintenance Plan:
> If we create a plan with "Reorganize Data and Index Pages"
> selected only, and running the job weekly, do we also need
> to set up another job to run sp_updatestats?
> Thanks,
> Vicky|||That step executes DBCC DBREINDEX which does a full rebuild of the indexes.
The statistics are updated with that rebuild, so there is not need to run
UPDATE STATISTICS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Vicky" <vickyfan@.hotmail.com> wrote in message
news:7a7901c402e1$a724f210$a301280a@.phx.gbl...
> I have a question on DB Maintenance Plan:
> If we create a plan with "Reorganize Data and Index Pages"
> selected only, and running the job weekly, do we also need
> to set up another job to run sp_updatestats?
> Thanks,
> Vicky|||Oops, thanks Tibor... I read too fast and though she was talking about
backing up the database, not rebuilding indexes.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$BhynuAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> That step executes DBCC DBREINDEX which does a full rebuild of the
indexes.
> The statistics are updated with that rebuild, so there is not need to run
> UPDATE STATISTICS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Vicky" <vickyfan@.hotmail.com> wrote in message
> news:7a7901c402e1$a724f210$a301280a@.phx.gbl...
> > I have a question on DB Maintenance Plan:
> >
> > If we create a plan with "Reorganize Data and Index Pages"
> > selected only, and running the job weekly, do we also need
> > to set up another job to run sp_updatestats?
> >
> > Thanks,
> >
> > Vicky
>

DB Maintenance Plan

I have a question on DB Maintenance Plan:
If we create a plan with "Reorganize Data and Index Pages"
selected only, and running the job weekly, do we also need
to set up another job to run sp_updatestats?
Thanks,
Vickyyes
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Vicky" <vickyfan@.hotmail.com> wrote in message
news:7a7901c402e1$a724f210$a301280a@.phx.gbl...
> I have a question on DB Maintenance Plan:
> If we create a plan with "Reorganize Data and Index Pages"
> selected only, and running the job weekly, do we also need
> to set up another job to run sp_updatestats?
> Thanks,
> Vicky|||That step executes DBCC DBREINDEX which does a full rebuild of the indexes.
The statistics are updated with that rebuild, so there is not need to run
UPDATE STATISTICS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Vicky" <vickyfan@.hotmail.com> wrote in message
news:7a7901c402e1$a724f210$a301280a@.phx.gbl...
> I have a question on DB Maintenance Plan:
> If we create a plan with "Reorganize Data and Index Pages"
> selected only, and running the job weekly, do we also need
> to set up another job to run sp_updatestats?
> Thanks,
> Vicky|||Oops, thanks Tibor... I read too fast and though she was talking about
backing up the database, not rebuilding indexes.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$BhynuAEHA.2480@.TK2MSFTNGP12.phx.gbl...
> That step executes DBCC DBREINDEX which does a full rebuild of the
indexes.
> The statistics are updated with that rebuild, so there is not need to run
> UPDATE STATISTICS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Vicky" <vickyfan@.hotmail.com> wrote in message
> news:7a7901c402e1$a724f210$a301280a@.phx.gbl...
>

DB maint. plan - UNC problem

Hi,
I'm attempting to use a UNC path within a Database Maintenance Plan, and
receiving a 'cannot create' O/S error. The maintenance plan accepts the path
to the server/share, but the subsequent SQL job fails with the above error.
I believe maintenance plans can't use UNC paths. Is this true? Is there a
way around this problem?
My alternative is to create a database device, and backup the databases in
that manner, but I want the retention/deletion features of the maintenance
plan.
Cheers,
James G.
It can use them but the account that SQL Server runs under must have the
proper permissions to use this share. If SQL Server is running under the
local admin account it can not see the share on a remote server.
Andrew J. Kelly SQL MVP
"Jimbo" <james.goodwill@.mail.com> wrote in message
news:L8hKf.13177$gB4.4335@.newsfe4-gui.ntli.net...
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the
> path
> to the server/share, but the subsequent SQL job fails with the above
> error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>
|||Sorry meant to add this: http://support.microsoft.com/?kbid=555128
Andrew J. Kelly SQL MVP
"Jimbo" <james.goodwill@.mail.com> wrote in message
news:L8hKf.13177$gB4.4335@.newsfe4-gui.ntli.net...
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the
> path
> to the server/share, but the subsequent SQL job fails with the above
> error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>
|||Hi
Maintenance Plans should be able to take a UNC path, make sure that the SQL
Agent Service Account has sufficient permissions to access this location.
John
"Jimbo" wrote:

> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the path
> to the server/share, but the subsequent SQL job fails with the above error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>
>

DB maint. plan - UNC problem

Hi,
I'm attempting to use a UNC path within a Database Maintenance Plan, and
receiving a 'cannot create' O/S error. The maintenance plan accepts the path
to the server/share, but the subsequent SQL job fails with the above error.
I believe maintenance plans can't use UNC paths. Is this true? Is there a
way around this problem?
My alternative is to create a database device, and backup the databases in
that manner, but I want the retention/deletion features of the maintenance
plan.
Cheers,
James G.It can use them but the account that SQL Server runs under must have the
proper permissions to use this share. If SQL Server is running under the
local admin account it can not see the share on a remote server.
Andrew J. Kelly SQL MVP
"Jimbo" <james.goodwill@.mail.com> wrote in message
news:L8hKf.13177$gB4.4335@.newsfe4-gui.ntli.net...
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the
> path
> to the server/share, but the subsequent SQL job fails with the above
> error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>|||Sorry meant to add this: http://support.microsoft.com/?kbid=555128
Andrew J. Kelly SQL MVP
"Jimbo" <james.goodwill@.mail.com> wrote in message
news:L8hKf.13177$gB4.4335@.newsfe4-gui.ntli.net...
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the
> path
> to the server/share, but the subsequent SQL job fails with the above
> error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>|||Hi
Maintenance Plans should be able to take a UNC path, make sure that the SQL
Agent Service Account has sufficient permissions to access this location.
John
"Jimbo" wrote:

> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the pa
th
> to the server/share, but the subsequent SQL job fails with the above error
.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>
>

DB maint. plan - UNC problem

Hi,
I'm attempting to use a UNC path within a Database Maintenance Plan, and
receiving a 'cannot create' O/S error. The maintenance plan accepts the path
to the server/share, but the subsequent SQL job fails with the above error.
I believe maintenance plans can't use UNC paths. Is this true? Is there a
way around this problem?
My alternative is to create a database device, and backup the databases in
that manner, but I want the retention/deletion features of the maintenance
plan.
Cheers,
James G.It can use them but the account that SQL Server runs under must have the
proper permissions to use this share. If SQL Server is running under the
local admin account it can not see the share on a remote server.
--
Andrew J. Kelly SQL MVP
"Jimbo" <james.goodwill@.mail.com> wrote in message
news:L8hKf.13177$gB4.4335@.newsfe4-gui.ntli.net...
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the
> path
> to the server/share, but the subsequent SQL job fails with the above
> error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>|||Sorry meant to add this: http://support.microsoft.com/?kbid=555128
--
Andrew J. Kelly SQL MVP
"Jimbo" <james.goodwill@.mail.com> wrote in message
news:L8hKf.13177$gB4.4335@.newsfe4-gui.ntli.net...
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the
> path
> to the server/share, but the subsequent SQL job fails with the above
> error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>|||Hi
Maintenance Plans should be able to take a UNC path, make sure that the SQL
Agent Service Account has sufficient permissions to access this location.
John
"Jimbo" wrote:
> Hi,
> I'm attempting to use a UNC path within a Database Maintenance Plan, and
> receiving a 'cannot create' O/S error. The maintenance plan accepts the path
> to the server/share, but the subsequent SQL job fails with the above error.
> I believe maintenance plans can't use UNC paths. Is this true? Is there a
> way around this problem?
> My alternative is to create a database device, and backup the databases in
> that manner, but I want the retention/deletion features of the maintenance
> plan.
> Cheers,
> James G.
>
>

DB Dublicate

I need to create a dublicate for a databaseprogramatically
meaning if i have db2004, I need to create db2005programatically same as db2004(tables and procedures, only structure)

Is that possible??

thank you

The backup and restore wizard will let you change the name of the new one and you can generate the create statement and run it through ADO.NET but you have to manually move the data. In the backup and restore wizard choose restore from device. Try the link below for ADO.NET create database code sample. Hope this helps.

http://www.functionx.com/csharp/adonet/Lesson04.htm

|||Thank you for this fast reply

Is there any way to backup restore programatically?|||

I have thought of that if it is there it is in SMO(sql server management object). I did not see any code so you will need a book because Microsoft wait for us to write the code most of the time. Try the link below for detail.

http://msdn2.microsoft.com/en-us/library/ms162175.aspx

|||

Thanks alot..

Is there any other way I can clone my current db to a new named db??|||

You have two options generate the create statement to create it in ADO.NET and use SSIS(sql server integration service) to move your data later or dettach and attache the database programmatically. Hope this helps.

http://www.sqlis.com

http://forums.asp.net/thread/977493.aspx

|||

sorry for these too much questions, please be patient

I forgot to tell you that im using SQL Server 2000

I have an Idea but i need some help

1- I can easily create a database and name it whtever I want
2- I can generate sql script for all tables and procedure of my current DB

Is there any way that I can run the .sql file from my code?

Thank you soo much in advanced

|||

That means you need DMO(data management object) code and I have found two try the links below for the code. Hope this helps.

http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO-Using-VBNET/

http://www.codersource.net/csharp_sqldmo_sqlserver.aspx

|||hello again

sorry but I meant in my last post to execute an sql file from my vb.net code

Dim cmdNameAsString = File.OpenText("tables.sql").ReadToEnd()
Dim cmdAsNew SqlCommand(cmdName, con)
cmd.CommandType = CommandType.Text

con.Open()
cmd.ExecuteNonQuery()
con.Close()

-----------------------
I have a problem here...
tables.sql is execured with no problems

but when im trying to execute "procedures.sql" (file containing SqlScript of all stored procedures)
errors are raised

Please help this is the last step

Thank you sooooooooooooooooo much

|||

Multiple stored proc can be called with a system stored procedure sp_executesql so you will need to wrap your code with it. Try the link below for details. The other option is to call each stored proc separately but that is just time consuming. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

|||You are amaaaazingggggggggggggggggggggggg

Thank you sooo much....

It worked finally|||

JRICE:

You are amaaaazingggggggggggggggggggggggg

Thank you sooo much....

It worked finally

I am glad I could help.