Showing posts with label entire. Show all posts
Showing posts with label entire. Show all posts

Monday, March 19, 2012

Copying entire databases to remote server w/o Enterprise Manager

Is there any possible way to copy an entire MSDE database from my local system to a remote server using a program like 'osql'?

Or, is there any other GUI available for MSDE?

Thanks in advance.

GrierSure. There are a few ways. A couple that come to mind are to use the sp_detach_db system stored procedure in osql, copy the database files to the server, and use sp_attach_db to reattach them. Another is to run dtsrun.exe to run a DTS package that copies it over. You could also write ADO.NET code to do it, but it would be a lot of work to get all the objects copied.

There are several tools you can use to administer MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access. I'm not sure if this will do what you want, though.

Any of these work for you?
Don

Copying Entire Database

I am trying to copy the MSSQL7 Database from one Server to Another.
On Server "A", the MSSQL7/Data Directory is on the "D:" Partition.
On Server "B", the MSSQL7/Data Direcotry is on the "E:" Partition.
I have copied the entire Data directory from Server "A" to Server "B".
Now when I try to start the MSSQL Service on Server "B". the ERRORLOG shows
that the Model.mdf could not be found at: "D:\MSSQL7\Data\Model.mdf". And,
the MSSQL Service fails to start.
Is there a way to tell MSSQL Server that all of the databases that were on
the "D:" partition are now located on the "E:" Partition?
Will,
things are a fair bit more complicated than just moving the directories.
Vyas has compiled a series of information and links that can help you:
http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thank you Paul.
The documentation there is very thorough, except for one thing. I have read
through similar documentation, and am performing a move very similar to the
situation in the link you provided. However, in the examples they are
documented in cases where the Logical Drive is the same (i.e. moving from a
"D:" drive on ServerA to a "D:" drive on ServerB).
In my situation, i need to move from a "D:" Drive on ServerA to a "E:" Drive
on ServerB. I cannot rename the drives because of other (backup) software
that is already installed on the "D:" Logical Drive on this server. In my
case, when I try to start SQL Services, the LOGERROR shows an error of:
"Unable to find D:\MSSQL7\DATA". That is because it is now on the "E:"
drive...
Thank you for your information. The steps in the link you provided were
much more in depth than what I was doing, and I did learn a couple of things
in there.
-- Will G.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uKn0QvisEHA.2556@.tk2msftngp13.phx.gbl...
> Will,
> things are a fair bit more complicated than just moving the directories.
> Vyas has compiled a series of information and links that can help you:
> http://vyaskn.tripod.com/moving_sql_server.htm
> HTH,
> Paul Ibison (SQL Server MVP)
>
>

Sunday, March 11, 2012

Copying Database without data

Hi,
Does anyone know, with either the Apr or Jun CTP, how to copy an entire database without the data? I know to use the SSIS wizard to copy tables with data, but there seem to be no option to not copy the data.

Thanks.In the June CTP you can use the Transfer Objects Task. It has an option for transferring data that you may set to false.|||BTW, There are some issues with this task in the June CTP that have recently been fixed and will not be available until the next CTP. This solution will probably not work for you until you can get the version with the fixes.Sad|||Hi Kirk,
Thanks for the update. I was actually hoping for a checkbox option inside the SSIS Import and Export wizard. :) I'll wait till it is fixed then.|||

you can

1. left click source database ->Script database ->create to

1.1. change database and filenames on the script created

1.2 execute script to create new database

2. left click source database -> tasks -> generate scripts

2.1 add at the to the following lines

use newdatabasename

go

2.2. run the script

you can save the scripts and use them at SSIS for automation

Copying Database without data

Hi,
Does anyone know, with either the Apr or Jun CTP, how to copy an entire database without the data? I know to use the SSIS wizard to copy tables with data, but there seem to be no option to not copy the data.

Thanks.In the June CTP you can use the Transfer Objects Task. It has an option for transferring data that you may set to false.|||BTW, There are some issues with this task in the June CTP that have recently been fixed and will not be available until the next CTP. This solution will probably not work for you until you can get the version with the fixes.Sad|||Hi Kirk,
Thanks for the update. I was actually hoping for a checkbox option inside the SSIS Import and Export wizard. :) I'll wait till it is fixed then.|||

you can

1. left click source database ->Script database ->create to

1.1. change database and filenames on the script created

1.2 execute script to create new database

2. left click source database -> tasks -> generate scripts

2.1 add at the to the following lines

use newdatabasename

go

2.2. run the script

you can save the scripts and use them at SSIS for automation

Wednesday, March 7, 2012

Copying BAK file

When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
this:
copy DBF_db_20040602.BAK .
it copies the entire file to the current directory where the cmd-file is
started.
So far so good...
But, when I change the cmd-file to do this:
copy DBF_db_20040602.BAK DBF_newfile.BAK
is only creates a file of only 6k in size. The original file is about 2 Gb
in size.
Anyone knows why this happens?
..Nico
hi
Have u tried using xcopy?
J
"Nico" <duiken@.nospam.nl> wrote in message
news:40bdeb35$0$136$18b6e80@.news.wanadoo.nl...
> When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
> this:
> copy DBF_db_20040602.BAK .
> it copies the entire file to the current directory where the cmd-file is
> started.
> So far so good...
> But, when I change the cmd-file to do this:
> copy DBF_db_20040602.BAK DBF_newfile.BAK
> is only creates a file of only 6k in size. The original file is about 2 Gb
> in size.
> Anyone knows why this happens?
> .Nico
>
|||Nico,
Sounds strange. I suggest you post this to a windows forum, as likelyhood to find Windows experts should be
higher there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nico" <duiken@.nospam.nl> wrote in message news:40bdeb35$0$136$18b6e80@.news.wanadoo.nl...
> When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
> this:
> copy DBF_db_20040602.BAK .
> it copies the entire file to the current directory where the cmd-file is
> started.
> So far so good...
> But, when I change the cmd-file to do this:
> copy DBF_db_20040602.BAK DBF_newfile.BAK
> is only creates a file of only 6k in size. The original file is about 2 Gb
> in size.
> Anyone knows why this happens?
> .Nico
>

Copying BAK file

When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
this:
copy DBF_db_20040602.BAK .
it copies the entire file to the current directory where the cmd-file is
started.
So far so good...
But, when I change the cmd-file to do this:
copy DBF_db_20040602.BAK DBF_newfile.BAK
is only creates a file of only 6k in size. The original file is about 2 Gb
in size.
Anyone knows why this happens?
.Nicohi
Have u tried using xcopy?
J
"Nico" <duiken@.nospam.nl> wrote in message
news:40bdeb35$0$136$18b6e80@.news.wanadoo.nl...
> When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
> this:
> copy DBF_db_20040602.BAK .
> it copies the entire file to the current directory where the cmd-file is
> started.
> So far so good...
> But, when I change the cmd-file to do this:
> copy DBF_db_20040602.BAK DBF_newfile.BAK
> is only creates a file of only 6k in size. The original file is about 2 Gb
> in size.
> Anyone knows why this happens?
> .Nico
>|||Nico,
Sounds strange. I suggest you post this to a windows forum, as likelyhood to
find Windows experts should be
higher there... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nico" <duiken@.nospam.nl> wrote in message news:40bdeb35$0$136$18b6e80@.news.wanadoo.nl...[vb
col=seagreen]
> When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
> this:
> copy DBF_db_20040602.BAK .
> it copies the entire file to the current directory where the cmd-file is
> started.
> So far so good...
> But, when I change the cmd-file to do this:
> copy DBF_db_20040602.BAK DBF_newfile.BAK
> is only creates a file of only 6k in size. The original file is about 2 Gb
> in size.
> Anyone knows why this happens?
> .Nico
>[/vbcol]

Copying BAK file

When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
this:
copy DBF_db_20040602.BAK .
it copies the entire file to the current directory where the cmd-file is
started.
So far so good...
But, when I change the cmd-file to do this:
copy DBF_db_20040602.BAK DBF_newfile.BAK
is only creates a file of only 6k in size. The original file is about 2 Gb
in size.
Anyone knows why this happens?
.Nicohi
Have u tried using xcopy?
J
"Nico" <duiken@.nospam.nl> wrote in message
news:40bdeb35$0$136$18b6e80@.news.wanadoo.nl...
> When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
> this:
> copy DBF_db_20040602.BAK .
> it copies the entire file to the current directory where the cmd-file is
> started.
> So far so good...
> But, when I change the cmd-file to do this:
> copy DBF_db_20040602.BAK DBF_newfile.BAK
> is only creates a file of only 6k in size. The original file is about 2 Gb
> in size.
> Anyone knows why this happens?
> .Nico
>|||Nico,
Sounds strange. I suggest you post this to a windows forum, as likelyhood to find Windows experts should be
higher there... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nico" <duiken@.nospam.nl> wrote in message news:40bdeb35$0$136$18b6e80@.news.wanadoo.nl...
> When I copy a BACKUP using a .cmd file from a command-box in Win2K, like
> this:
> copy DBF_db_20040602.BAK .
> it copies the entire file to the current directory where the cmd-file is
> started.
> So far so good...
> But, when I change the cmd-file to do this:
> copy DBF_db_20040602.BAK DBF_newfile.BAK
> is only creates a file of only 6k in size. The original file is about 2 Gb
> in size.
> Anyone knows why this happens?
> .Nico
>

Saturday, February 25, 2012

Copying a database to another server

Im running SQL Server 2005 Express with SQL Server Management studio installed on my laptop. After updating my database, how do I copy the entire contents to the main server in the office, which is running windows server 2003 and SQL Server 2000 Developer Edition.
And is it possible to do it all from my laptop?

right click the database an you will see a context menu. on one of the sub menus here you will see generate script. In the wizard, one of the options is to specify sql 200 compatible

After running the script on the office server, right click on the database, select export, follow the steps in the wizard to export the data.

Friday, February 24, 2012

Copy'n data from one table to the other

I want to copy an entire tables data into a new table but this code does not work need some help

SELECT condcode,brief_desc,condtext
INTO CD_ConditionCode, CD_BriefDesc, CD_Description
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The INTO clause is where you define the target table.

You would do something like:

Code Snippet

SELECT condcode,brief_desc,condtext
INTO dbo.MyNewTable
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The table will be populated with the columns listed in the SELECT.

Copy'n an data from one table to the other

I want to copy an entire tables data into a new table but this code does not work need some help

SELECT condcode,brief_desc,condtext
INTO CD_ConditionCode, CD_BriefDesc, CD_Description
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The INTO clause is where you define the target table.

You would do something like:

Code Snippet

SELECT condcode,brief_desc,condtext
INTO dbo.MyNewTable
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The table will be populated with the columns listed in the SELECT.

Monday, February 13, 2012

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'
Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>
|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>> We have been using the 'Copy SQL Server Objects' with success for some
>> time to copy an entire database to another server. Recent changes to our
>> database infrastructure cause DTS to fail:
>> 1) We have a check constraint which uses a user function (which refers to
>> the table on the check constraint
>> 2) We have a view which refers to another view
>> DTS fails on both. DTS fails on the check constraint and stops. if I
>> remove the check constraint it will fail on the view. See errors below.
>> Any hints how to solve this would be appreciated.
>> Jonathan Orgel
>> Errors:
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'dbo.SRS_NumberRWSubjects'
>> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
>> 'VMSGRECIPIENTS2'
>>
>

Copy SQL Server Objects Fails for certain views

We have been using the 'Copy SQL Server Objects' with success for some time
to copy an entire database to another server. Recent changes to our database
infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I remove
the check constraint it will fail on the view. See errors below.
Any hints how to solve this would be appreciated.
Jonathan Orgel
Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object n
ame
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object n
ame
'VMSGRECIPIENTS2'Redefine the package. Copy objects just uses an alphabetical order. If you
define this to explicitly define which objects are moving, you can specify
the order which will move the dependent objects first.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
> We have been using the 'Copy SQL Server Objects' with success for some
> time to copy an entire database to another server. Recent changes to our
> database infrastructure cause DTS to fail:
> 1) We have a check constraint which uses a user function (which refers to
> the table on the check constraint
> 2) We have a view which refers to another view
> DTS fails on both. DTS fails on the check constraint and stops. if I
> remove the check constraint it will fail on the view. See errors below.
> Any hints how to solve this would be appreciated.
> Jonathan Orgel
> Errors:
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object
name
> 'dbo.SRS_NumberRWSubjects'
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object
name
> 'VMSGRECIPIENTS2'
>
>|||> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving,
Easier said than done. Our database is a constantly evolving conglomeration
of hundreds of objects. We don't relish having to constantly evolve the DTS
package as well. That's why we are using the more generic transfer
methodology to transfer the entire database, rather than identifying each
object specifically.
Also, it does not seem that objects are copied in alphabetic order, it seems
to have more to do with creation / last modification date. By making an
innocuous change to the object we are now able to complete the transfer. We
did not rename the object, yet we managed to change its transfer sequence.
Nice going Jonathan! :-)
- Joe Geretz -
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OJOVDuNDGHA.3876@.tk2msftngp13.phx.gbl...
> Redefine the package. Copy objects just uses an alphabetical order. If
> you define this to explicitly define which objects are moving, you can
> specify the order which will move the dependent objects first.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
> news:ee1MIoJDGHA.3920@.tk2msftngp13.phx.gbl...
>

copy sql server db on same server

what would be the best way to copy the entire db to the same server instance?
The only thing is the db name needs to be changed. But the logins, tables,
etc are all the same.
Can't use the wizard since the same instance...
thanx?
Simply restore a full backup and restore it to a different db name and
either place the files on different drives or rename them with the MOVE
option.
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F44B8F48-4635-45AD-844F-473899A5B94E@.microsoft.com...
> what would be the best way to copy the entire db to the same server
> instance?
> The only thing is the db name needs to be changed. But the logins, tables,
> etc are all the same.
> Can't use the wizard since the same instance...
> thanx?

copy sql server db on same server

what would be the best way to copy the entire db to the same server instance
?
The only thing is the db name needs to be changed. But the logins, tables,
etc are all the same.
Can't use the wizard since the same instance...
thanx?Simply restore a full backup and restore it to a different db name and
either place the files on different drives or rename them with the MOVE
option.
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F44B8F48-4635-45AD-844F-473899A5B94E@.microsoft.com...
> what would be the best way to copy the entire db to the same server
> instance?
> The only thing is the db name needs to be changed. But the logins, tables,
> etc are all the same.
> Can't use the wizard since the same instance...
> thanx?

copy sql server db on same server

what would be the best way to copy the entire db to the same server instance?
The only thing is the db name needs to be changed. But the logins, tables,
etc are all the same.
Can't use the wizard since the same instance...
thanx?Simply restore a full backup and restore it to a different db name and
either place the files on different drives or rename them with the MOVE
option.
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F44B8F48-4635-45AD-844F-473899A5B94E@.microsoft.com...
> what would be the best way to copy the entire db to the same server
> instance?
> The only thing is the db name needs to be changed. But the logins, tables,
> etc are all the same.
> Can't use the wizard since the same instance...
> thanx?