Converting problem: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.Bart
--
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.
Bart
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

copy on condition

i have two tables A and B with the same fields,

If the id field of table B equals id field in Table A i need to update th edata for that id row.
If the id field doesn;t match then i need to insert a new record in tale A for that id

that is i need to perform insertion or updation into table A depending on table B data
Can anyone give me some idea how to start?

You need two statements.
UPDATE A SET col1 = b.col1, col2 = b.col2, ... FROM A INNER JOIN B on A.pkcol = B.pkcol
INERT INTO A (pkcol, col1, col2, col3)
SELECT pkcol, col1, col2, col3
FROM B
WHERE pkcol NOT IN (SELECT pkcol FROM A)|||

Thank you very much Alex!
One more question, I have millions of data in the table, will the 2 queries work well for such huge data
.I mean performance wise. I have created indexes .

|||You'll just have to try it to find out. No way of guessing that without knowing what the tables are, how the indexes are, hardware, load, etc ...

Tuesday, March 27, 2012

Copy items from one SQL Database to another...

...such as stored procedures, tables etc.

Initially this started as a case of "doh, I should just be using one database here, not two", and I was simply wanting to copy database tables. In VS2K5 I tried as there is a right click menu option of copy when a table is selected but this doesn't work for me with any database object of any kind.

So fast forward to the present...I now am attempting to deploy an app to a hosting service, 1&1.com. I am allowed only one database in my current package, which should be fine for now. So I had to combine the ASPNETDB along with 2 other databases. It took a bit of time, but I got everything done, I thought, and posted to the servers. While debugging I get an error saying a stored procedure can not be found. And it indeed is not.

This really confuses me as I made the changes in VS2k5, shut down and restarted to make sure I didn't miss anything, then used SQL Server Man. Studio to make a .bak file to upload to my hosting service. It never occurred to me to verify the changes I made in VS2k5 were actually on the database when viewed there. Well, they aren't, and I have no idea why. That would be issue #1 I suppose.

So after giving the background info here, what I am looking for help with is how to get the changes I am making in VS2k5 to also be present when viewed from SQL Server Man. Studio as the only means of posting a db to my hosting provider is by using a .bak file.

Also, why is it a project template I download has a .mdf file I am not able to even see in SQL Server Man. Studio? I guess if I had this answer the issue would be resolved.

TIA

Regards,

Joe

When you say you want to copy tables, do you mean the data in the tables or just the structure of the tables? If it is just the structure you could script out the tables and stored procedures and execute them against your other database.

|||

OK...I'll start looking for pointers on what exactly you mean. I honestly have no idea as I am not an SQL guy, and am new to ASP.NET/VS/VB etc.

If you can point me in the direction of what you mean that would be great.

I appreciate the response.

Regards,

Joe

|||

I think I was too long winded and did not communicate my question well in an earlier post.

I want to transfer database tables and stored procedures from datbase1 and database2 to database3, and end up with just database3. (I don't care one way or the other about the data in the tables) I am finding posts regarding how to do this, but most expect the database to be viewable in SQL Server management studio, and I can not see all the databases there. In one case I have a website completed from a MSDN template, and in another case the database was created as a part of a web app I created from scratch. Both were created using VS2K5 pro.

The odd bit is that I created all the databases from within VS2K5 while working on web apps. So I am very confused as to why some databases show up, some don't, and some work done doesn't show up in the database that is showing up in server management studio. (to clarify, all the databases are visible from VS2K5, but only one is visible in the server management studio)

Any help would be great, thanks

TIA

Regards,

Joe

|||Right click on the database and select Tasks > Generate Scripts. Follow the steps and select stored procedures and tables to be scripted. It will generate a sql script that you can run on another database that will create those tables and stored procedures.|||

OK. I did see that option and was hoping to get a response confirming that.

My big problem though, is that I can't even see one of the databases in the management studio. I am not sure what I'm missing, but I have tried to make it visible but have failed. I do not understand this because I created all the databases from within VS2K5, so why I can see some but not others.

Any pointers in this regard would be much appreciated.

Regards,

Joe

|||

Hi Joe,

Are you sure that the database has been attached to the SQL Server instance? Because some databases are attached dynamically at runtime.

You can try to attach the database in SQL Server management studio.

|||

I am starting to feel very ignorant, lol. Not that it's a terrible thing, we all have to learn right...

I think in order to grasp what is going on here I need to understand what's happening when I add the database in VS2K5. If in VS2K5 I "add an existing item" (the database that SQL Server management studio sees) then why am I getting two instances of the database? I don't understand why I end up with two copies. I am not asking for another db to be created as far as I know.

I appreciate the responses posted so far, they are helpful, but I don't understand why I need to take either action suggested. I am certainly not unwilling to do either. But if I do I am still going to have no idea why I have tow copies of the db, and unless something changes will the same result not continue? When I make changes in either software (VS2K5/SQL Server management studio) will I not be changing just one db?

Still very uncertain here...

Any comments are appreciated.

Regards,

Joe

|||

OK...

I may have discovered the issue.

I went poking around again paying veryclose attention to every step as I am adding a database to my site. As I stepped through I get to the point where I test the connection and it fails, saying it's in use by another process, almost without thinking I begin to do what I have done numerous times in the past. I was going to stop the instance of SQL server. And it was then I realized..why? As I considered this I realized that did not make sense. So i looked at the dialog for add database and realized the dropdown had SQl Database selected, not SQL server. I feel relatively confident this is the cause of my troubles.

For anyone who is having siilar issues, I'll post as soon as i determine whether this was the problem.

Regards,

Joe

|||

OK.

That was it. I now have a connection only, not a new copy. I hope this helps anyone else as new as me Tongue Tied ...

Regards,

Joe

PS: do not know how to mark a question as answered...

sql sql

Copy ID from one table to another as foreign key

Hi,
I have two tables, one, called tblDoctor, with
docNo, docName . . .
the other, called tblPatient with
pntNo, pntName . . .
I want to insert a specific docNo value from one row in tblDoctor into
a specific row in tblPatient using a stored procedure within ADO.
Can i have an SQL statement along the lines of:
INSERT INTO tblPatient (docNo)
WHERE pntName = "a value"
SELECT docNo
FROM tblDoctor WHERE docName = "another value"
If not, has anyone got any suggestions on alternative methods of
accomplishing the same thing?
ThanksTry,
update tblPatient
set docNo = (SELECT docNo FROM tblDoctor WHERE docName = "another value")
WHERE pntName = "a value"
Be sure that you get just one docNo for a specific docName, if not sql
server will give an error. A trick can be to use "TOP 1" in the select
statement, but this is not the correct way to do this.
AMB
"Assimalyst" wrote:

> Hi,
> I have two tables, one, called tblDoctor, with
> docNo, docName . . .
> the other, called tblPatient with
> pntNo, pntName . . .
> I want to insert a specific docNo value from one row in tblDoctor into
> a specific row in tblPatient using a stored procedure within ADO.
> Can i have an SQL statement along the lines of:
> INSERT INTO tblPatient (docNo)
> WHERE pntName = "a value"
> SELECT docNo
> FROM tblDoctor WHERE docName = "another value"
> If not, has anyone got any suggestions on alternative methods of
> accomplishing the same thing?
> Thanks
>|||I've guessed what your tables should look like from your question and I used
some artistic licence to add a docNo Foreign Key to the Patient table. That
is of course that I understand what you are trying to achieve.
CREATE TABLE tblPatient (patientNo int, pntName varchar(10), docNo int)
CREATE TABLE tblDoctor (docNo int, docName varchar(10))
UPDATE tblPatient SET DocNo = D.docNo
FROM (SELECT docNo FROM tblDoctor WHERE docName = 'another value') D
WHERE pntName = 'a value'
Hope this offers some thought to your problem
Andy
"Assimalyst" wrote:

> Hi,
> I have two tables, one, called tblDoctor, with
> docNo, docName . . .
> the other, called tblPatient with
> pntNo, pntName . . .
> I want to insert a specific docNo value from one row in tblDoctor into
> a specific row in tblPatient using a stored procedure within ADO.
> Can i have an SQL statement along the lines of:
> INSERT INTO tblPatient (docNo)
> WHERE pntName = "a value"
> SELECT docNo
> FROM tblDoctor WHERE docName = "another value"
> If not, has anyone got any suggestions on alternative methods of
> accomplishing the same thing?
> Thanks
>|||Thank you for the replies, i've tried both suggestions, but having no
luck. The tblPatient.doctorNo remains blank.
Below is the relevant code and stored procedure, i'm getting no errors,
and the code runs through the debugger without a hitch.
string strSgyNo = System.Convert.ToString(intSgyNo);
string strDocFName = fNameTxtBx.Text;
string strDocLName = lNameTxtBx.Text;
string strPatientCode = patientCodeLbl.Text;
string strDOB = dobLbl.Text;
// Use stored procedure to input doctorNo as foreign key in tblPatient
SqlCommand cmd = new SqlCommand("proc_AddDocToPnt", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Add parameters to command, which will be passed to the stored
procedure
cmd.Parameters.Add(new SqlParameter("@.docFName", strDocFName));
cmd.Parameters.Add(new SqlParameter("@.docLName", strDocLName));
cmd.Parameters.Add(new SqlParameter("@.sgyNo", strSgyNo));
cmd.Parameters.Add(new SqlParameter("@.pntCode", strPatientCode));
cmd.Parameters.Add(new SqlParameter("@.pntDOB", strDOB));
Stored Procedure:
CREATE PROCEDURE proc_AddDocToPnt
(@.docFName varchar(20),
@.docLName varchar(30),
@.sgyNo int,
@.pntCode varchar(15),
@.pntDOB datetime
)
AS
UPDATE tblPatient
SET doctorNo = D.doctorNo
FROM(SELECT doctorNo
FROM tblReferringDoctorContacts
WHERE (docFName = @.docFName)
AND (docLName=@.docLName)
AND (sgyNo=@.sgyNo)) D
WHERE ((pntUnitID = @.pntCode) AND (pntDOB = @.pntDOB))
GO
Can anyone spot a problem?
Thanks again.|||Hi
Run these 2 queries. Both should return results using Query Analyser
DECLARE @.docFName varchar(20)
DECLARE @.docLName varchar(30)
DECLARE @.sgyNo int
DECLARE @.pntCode varchar(15)
DECLARE @.pntDOB datetime
--Set the values that would be passed using your code
SET @.docFName = ''
SET @.docLName ''
SET @.sgyNo = 0
SET @.pntCode ''
SET @.pntDOB '1980-01-01'
SELECT doctorNo
FROM tblReferringDoctorContacts
WHERE (docFName = @.docFName)
AND (docLName=@.docLName)
AND (sgyNo=@.sgyNo)
SELECT * FROM tblPatient WHERE ((pntUnitID = @.pntCode) AND (pntDOB =
pntDOB))
If you don't get any results from either query, then the query to update the
tblPatient will results in no changes being made because 1 or both of the
criterion isn't matched.
Using Query Analyser (included with MS SQL2K) or Visual Studio to test your
procedure / queries may offer more information from the output of your
queries better than running from your code.
Good Luck :)
Andy Furnival
"Assimalyst" wrote:

> Thank you for the replies, i've tried both suggestions, but having no
> luck. The tblPatient.doctorNo remains blank.
> Below is the relevant code and stored procedure, i'm getting no errors,
> and the code runs through the debugger without a hitch.
> string strSgyNo = System.Convert.ToString(intSgyNo);
> string strDocFName = fNameTxtBx.Text;
> string strDocLName = lNameTxtBx.Text;
> string strPatientCode = patientCodeLbl.Text;
> string strDOB = dobLbl.Text;
> // Use stored procedure to input doctorNo as foreign key in tblPatient
> SqlCommand cmd = new SqlCommand("proc_AddDocToPnt", conn);
> cmd.CommandType = CommandType.StoredProcedure;
> // Add parameters to command, which will be passed to the stored
> procedure
> cmd.Parameters.Add(new SqlParameter("@.docFName", strDocFName));
> cmd.Parameters.Add(new SqlParameter("@.docLName", strDocLName));
> cmd.Parameters.Add(new SqlParameter("@.sgyNo", strSgyNo));
> cmd.Parameters.Add(new SqlParameter("@.pntCode", strPatientCode));
> cmd.Parameters.Add(new SqlParameter("@.pntDOB", strDOB));
>
> Stored Procedure:
> CREATE PROCEDURE proc_AddDocToPnt
> (@.docFName varchar(20),
> @.docLName varchar(30),
> @.sgyNo int,
> @.pntCode varchar(15),
> @.pntDOB datetime
> )
> AS
> UPDATE tblPatient
> SET doctorNo = D.doctorNo
> FROM(SELECT doctorNo
> FROM tblReferringDoctorContacts
> WHERE (docFName = @.docFName)
> AND (docLName=@.docLName)
> AND (sgyNo=@.sgyNo)) D
> WHERE ((pntUnitID = @.pntCode) AND (pntDOB = @.pntDOB))
> GO
> Can anyone spot a problem?
> Thanks again.
>|||Thanks again,
I've run that, it seems it is finding the doctorNo from
blReferringDoctorContacts, but is not ablke to find the row from
tblPatient because the insetrts are not working properly.
If the number entered into the form is say 19/04/1945, the database
stores the date as 19/04/2001? Always changes the year to 2001!'
Very strange, but hence it cannot find the record. Think i'll need to
sort this before proceding with the above.
Thanks again|||OK solved that, i'd declared the pntDOB ad a varchar instead of a
datetime in the stored procedure oops!
Since tried again though, and i'm still not getting a row selected from
tblPatient.
i've set @.pntCode = 'CO123' and @.pntDOB = '10/03/1950' a row exists in
the tblPatient where pntUnitID = CO123 and pntDOB = 10/03/1950, but it
is not being found any ideas why?
Thanks again|||The problem seems to be with the date, although it is displayed in the
table as 10/03/1950 it is actually stored as 1950-03-10. if i change
@.pntDOB to 1950-03-10 it works. :)|||Dates in SQL can be ambigious. Therefore 10/03/1950 could be interpreted as
either 10-March-1950 or 3-October-1950.
When using datatimes in sql use the data format yyyy-mm-dd so set your
@.pndDOB = '1950-03-10'
To see how your dates are being interpreted run the code with your @.pndDOB
set.
CONVERT(SMALLDATETIME, @.pndDOB, 112) -- 112 is the yyyy-mm-dd format
Thanks
Andy
"Assimalyst" wrote:

> OK solved that, i'd declared the pntDOB ad a varchar instead of a
> datetime in the stored procedure oops!
> Since tried again though, and i'm still not getting a row selected from
> tblPatient.
> i've set @.pntCode = 'CO123' and @.pntDOB = '10/03/1950' a row exists in
> the tblPatient where pntUnitID = CO123 and pntDOB = 10/03/1950, but it
> is not being found any ideas why?
> Thanks again
>|||Thank you again.
Forgive my stupidity but i'm not sure how to implement what you're
suggesting.
Do you mean to change the query SET to
SET @.pntDOB = CONVERT(SMALLDATETIME, @.pntDOB, 112)
I've tried this, and get now matched rows. I've also tried
SET @.pntDOB = CONVERT(SMALLDATETIME, 10/03/1950, 112)
and again get no matches. the only way i've found so far is to manually
type
SET @.pntDOB = '1950-03-10' but i really need to implement this convert.
Can you elaborate?
Thankssql sql

Sunday, March 25, 2012

Copy DB to a different Machine

Hi,

I have developed an application using SQl Server 2000.

I want to transfer the database to my new server. I want all the tables and datas to be transferred.

I am new to this application. So i am not able to get mysel started. Can someone help me in this problem.

My database is large. It has stored procedures, database diagrams.

Any help in this regard would be highly appreciated.

Thanks,
-sriramFin MS Sql Server on your Programs menu. Choose 'Import/Export' data > next. The following Dialogue is the From dialogue. Choose the Databse you want to copy and press next. In the 'To' dialogue, enter the IP/URL to the remote SQL Server. In the Dropdown list, choose '<New>' and name your Database - press OK. I nteh next dialogue, choose 'Copy objects and data between SQL Server databases'. Click Next 3 times and then End. Now your Database and data will be copied to your new Server.|||I prefer doing a full backup and then restoring the backup on the new machine. I find it quicker that way. This way your DB Diagrams will survive too.|||i agree with the full backup technique.

the only time i "object copy" between db's is to keep my local DB in sync with a shared Development DB and 2 other developers.|||Hi,

Thanks for replying.

How can i do a full backup. I am not able to backup and transfer to the new machine.

So can you help me??
thanks,
-sriram|||if you go to C:\Program Files\Microsoft SQL Server\MSSQL\Data
find your db files ...( 2- the db file and the log file)

copy them over to the new machine in the same data folder...and "attach" the db...

** remember to "Stop" the sql server before you do this...

hth|||I'm working on a project with 3 other people whom are using MSDE for the DB. Is there a function similar to 'attach db' that can be used with MSDE. Our computers are not networked, so we will only be able to pass the database file around.

Thanks in advance!

Copy DB tables and stored procedures to a new DB with different name

Hello all!

What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB?

So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?

The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!

Any help would be greatly appreciated!!!

Hello,

1) Create empty dbs

db2,3,4

2) Right click on db1 and select Generate SQL script

3) Generate the script for tables,sp,views....

4) Open your query analyzer

Now, you have to do the following for each db

Use db2
-paste the sql script you did generate in step 3

Run it

Repeat step 4 for all your db.

Hope that helps
Regards

|||

That is easy use the Backup and Restore wizard use the restore from device option it will ask you for a new name just add the name and you will be done in a few minutes. Known issues orphaned permissions just delete the restore and create a new one. If you have more questions post again. Hope this helps.

|||

Hello

Is there another way so I can copy tables and procedures to a new DB , without any manual thing "by coding??"

Thank you

sql sql

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye
1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>
|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:
> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I would like to copy the structure of a DB (tables,relations, indexes,
> > default values, ecc...) into a new DB on the same server.
> >
> > I don't want to copy the data in the tables. Is this possible? How?
> >
> > Thank you very much
> >
> > Bye
> >
> >
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options
).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

Copy DB from 2005 to 2000

Is there a ligitimate way to copy database (ALL objects, not just tables and
views) from SQL 2005 to SQL 2000?
Thank you for your help!
You could try the instructions in this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=842225&SiteID=1
Look for a posting titled: "How to Downgrade a Database from SQL Server 2005
to SQL Server 2000"
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:BE2F5E3E-BAA3-49EA-B6A7-1BB69AE581D2@.microsoft.com...
> Is there a ligitimate way to copy database (ALL objects, not just tables
> and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!
|||DTS and, possibly, BCP.
On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:

>Is there a ligitimate way to copy database (ALL objects, not just tables and
>views) from SQL 2005 to SQL 2000?
>Thank you for your help!
|||bradsbulkmail@.comcast.net wrote:
[vbcol=seagreen]
> DTS and, possibly, BCP.
> On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
> <LeonShargorodsky@.discussions.microsoft.com> wrote:
Try SSIS, BCP or Replication
Regards
Amish Shah
|||You can export your database to SQL 2000 with data . SQL 2005 support this.
All tasks--> export data
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:BE2F5E3E-BAA3-49EA-B6A7-1BB69AE581D2@.microsoft.com...
> Is there a ligitimate way to copy database (ALL objects, not just tables
> and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!
|||Many people seem to mention using SSIS, but when I tried it using the
'Transfer Database task', it wouldn't let me downgrade: 'The source
connection ... must specify a SQL server with a version less than or
equal to the destination connection ... '.
Using SQL Server 2005 Replication works, though it's a bit overkill for
a one-off task.
amish wrote:
> bradsbulkmail@.comcast.net wrote:
>
> Try SSIS, BCP or Replication
> Regards
> Amish Shah
|||No, you can't 'Transfer database'. But you can script out the tables, views,
stored procedures, and the data
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"decates" <decates@.gmail.com> wrote in message
news:1164387119.010205.146340@.m7g2000cwm.googlegro ups.com...
> Many people seem to mention using SSIS, but when I tried it using the
> 'Transfer Database task', it wouldn't let me downgrade: 'The source
> connection ... must specify a SQL server with a version less than or
> equal to the destination connection ... '.
> Using SQL Server 2005 Replication works, though it's a bit overkill for
> a one-off task.
> amish wrote:
>

Copy DB from 2005 to 2000

Is there a ligitimate way to copy database (ALL objects, not just tables and
views) from SQL 2005 to SQL 2000?
Thank you for your help!You could try the instructions in this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=842225&SiteID=1
Look for a posting titled: "How to Downgrade a Database from SQL Server 2005
to SQL Server 2000"
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:BE2F5E3E-BAA3-49EA-B6A7-1BB69AE581D2@.microsoft.com...
> Is there a ligitimate way to copy database (ALL objects, not just tables
> and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!|||Leon Shargorodsky wrote:
> Is there a ligitimate way to copy database (ALL objects, not just tables and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!
You can't directly "downgrade" a database from SQL2005 to SQL2000. I
think your only option is to script it all out and the run these scripts
on your SQL2000 database.
Regards
Steen SchlÃ¼ter Persson
Database Administrator / System Administrator|||DTS and, possibly, BCP.
On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:
>Is there a ligitimate way to copy database (ALL objects, not just tables and
>views) from SQL 2005 to SQL 2000?
>Thank you for your help!|||bradsbulkmail@.comcast.net wrote:
> DTS and, possibly, BCP.
> On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
> <LeonShargorodsky@.discussions.microsoft.com> wrote:
> >Is there a ligitimate way to copy database (ALL objects, not just tables and
> >views) from SQL 2005 to SQL 2000?
> >
> >Thank you for your help!
Try SSIS, BCP or Replication
Regards
Amish Shah|||You can export your database to SQL 2000 with data . SQL 2005 support this.
All tasks--> export data
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:BE2F5E3E-BAA3-49EA-B6A7-1BB69AE581D2@.microsoft.com...
> Is there a ligitimate way to copy database (ALL objects, not just tables
> and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!|||Many people seem to mention using SSIS, but when I tried it using the
'Transfer Database task', it wouldn't let me downgrade: 'The source
connection ... must specify a SQL server with a version less than or
equal to the destination connection ... '.
Using SQL Server 2005 Replication works, though it's a bit overkill for
a one-off task.
amish wrote:
> bradsbulkmail@.comcast.net wrote:
> > DTS and, possibly, BCP.
> >
> > On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
> > <LeonShargorodsky@.discussions.microsoft.com> wrote:
> >
> > >Is there a ligitimate way to copy database (ALL objects, not just tables and
> > >views) from SQL 2005 to SQL 2000?
> > >
> > >Thank you for your help!
> Try SSIS, BCP or Replication
> Regards
> Amish Shah|||No, you can't 'Transfer database'. But you can script out the tables, views,
stored procedures, and the data
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"decates" <decates@.gmail.com> wrote in message
news:1164387119.010205.146340@.m7g2000cwm.googlegroups.com...
> Many people seem to mention using SSIS, but when I tried it using the
> 'Transfer Database task', it wouldn't let me downgrade: 'The source
> connection ... must specify a SQL server with a version less than or
> equal to the destination connection ... '.
> Using SQL Server 2005 Replication works, though it's a bit overkill for
> a one-off task.
> amish wrote:
>> bradsbulkmail@.comcast.net wrote:
>> > DTS and, possibly, BCP.
>> >
>> > On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
>> > <LeonShargorodsky@.discussions.microsoft.com> wrote:
>> >
>> > >Is there a ligitimate way to copy database (ALL objects, not just
>> > >tables and
>> > >views) from SQL 2005 to SQL 2000?
>> > >
>> > >Thank you for your help!
>> Try SSIS, BCP or Replication
>> Regards
>> Amish Shah
>

Copy DB from 2005 to 2000

Is there a ligitimate way to copy database (ALL objects, not just tables and
views) from SQL 2005 to SQL 2000?
Thank you for your help!You could try the instructions in this thread:
http://forums.microsoft.com/MSDN/Sh...842225&SiteID=1
Look for a posting titled: "How to Downgrade a Database from SQL Server 2005
to SQL Server 2000"
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:BE2F5E3E-BAA3-49EA-B6A7-1BB69AE581D2@.microsoft.com...
> Is there a ligitimate way to copy database (ALL objects, not just tables
> and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!|||Leon Shargorodsky wrote:
> Is there a ligitimate way to copy database (ALL objects, not just tables a
nd
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!
You can't directly "downgrade" a database from SQL2005 to SQL2000. I
think your only option is to script it all out and the run these scripts
on your SQL2000 database.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||DTS and, possibly, BCP.
On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
<LeonShargorodsky@.discussions.microsoft.com> wrote:

>Is there a ligitimate way to copy database (ALL objects, not just tables an
d
>views) from SQL 2005 to SQL 2000?
>Thank you for your help!|||bradsbulkmail@.comcast.net wrote:
[vbcol=seagreen]
> DTS and, possibly, BCP.
> On Wed, 1 Nov 2006 14:28:02 -0800, Leon Shargorodsky
> <LeonShargorodsky@.discussions.microsoft.com> wrote:
>
Try SSIS, BCP or Replication
Regards
Amish Shah|||You can export your database to SQL 2000 with data . SQL 2005 support this.
All tasks--> export data
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:BE2F5E3E-BAA3-49EA-B6A7-1BB69AE581D2@.microsoft.com...
> Is there a ligitimate way to copy database (ALL objects, not just tables
> and
> views) from SQL 2005 to SQL 2000?
> Thank you for your help!|||Many people seem to mention using SSIS, but when I tried it using the
'Transfer Database task', it wouldn't let me downgrade: 'The source
connection ... must specify a SQL server with a version less than or
equal to the destination connection ... '.
Using SQL Server 2005 Replication works, though it's a bit overkill for
a one-off task.
amish wrote:
> bradsbulkmail@.comcast.net wrote:
>
> Try SSIS, BCP or Replication
> Regards
> Amish Shah|||No, you can't 'Transfer database'. But you can script out the tables, views,
stored procedures, and the data
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"decates" <decates@.gmail.com> wrote in message
news:1164387119.010205.146340@.m7g2000cwm.googlegroups.com...
> Many people seem to mention using SSIS, but when I tried it using the
> 'Transfer Database task', it wouldn't let me downgrade: 'The source
> connection ... must specify a SQL server with a version less than or
> equal to the destination connection ... '.
> Using SQL Server 2005 Replication works, though it's a bit overkill for
> a one-off task.
> amish wrote:
>

Tuesday, March 20, 2012

Copy Database Wizard

Hello, I am trying to copy my database tables and stored procedures to my local machine. I keep gettin gthis error
CDW Error Message

X "Your SQL Server Service is running under the local system account. You need to change you SQL Server Sevice account to have the rights to copy files over the network."

What do I do to correct this?

Thanks,
EricAre you working in a controlled environment ??
Active Directory and so ??
If that is so, you need to be system admin in the network to be able to copy those files ...|||The local System account only has permissions to run proggys on the local machine, to do any networkable actions the SQL Server needs to be run under an account that is able to have permissions added to it.

What You should do when createing or installing a SQL Server is to create another account for it to run under. If you are working in a domain You would create a domain account that is a member of the domain users group, and has the add permissions to log on as a service. Then increase the permissions as needed. Now if you want to use SQL Mail you need to do the above so that you can add a mail profile to the server so that the user can send emails (SQLServer mail uses the MAPI system same as Outlook).

If you are in a workgroup, create a local user and add the log on as service permissions. then on the other machines that it needs to talk to you would add the user.|||Is it at all possible to get instructions on how this can happen. Even if I have to uninstall Sql server and re-install it. I need to copy the Database to my local machine.|||Are you just trying to copy it once, or do you want this to be a regular job.

Monday, March 19, 2012

copy database tables

Hi!

Can someone tell me how I can copy my database which is made with SQLExpress or the tables to another SQL server. The servers could be SQL server 2000 and SQLExpress.

Thanks!

SQL Server Express --> SQL Server Express (or any other 2005er edition), just detach the mdf file (if possible) and copy it to the destination location, then attach the orginal one to the server again, and attach the copy to the remote server -done. If you have no maintainance window to do so, make a backup and a restore on the other server (could take a bit longer but keeps your database online during the whole time)

SQL Server Express --> downgade versions: Create scripts of your objects and data. Make sure that the scripts are compatible with the versions you want to deploy it to.

HTH, jens Suessmeyer.

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

At first I made backup file of my SQLExpress database and and copy it to another PC witch have SQLExpress also, but I didn't work.

Then I tryed the script and it did work. Now I have to test this also to SQL Server 2000.

The question, is what did I do wrong with the backup?

|||Doing a copy from SQL Server Express to SQL Server Express should work fine, which error message are you getting ?

If you have a SQL Server 2000 Dev/Std/Ent you can use the DTS Wizard to copy the database from SQl Server Express to SQL 2000 (Import).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Copy database from SQL 2k to 2005?

I'm able to use SQL 2005's VB management page to import the tables and views from a database in SQL 2000 on another server, but how do I get all of the data like stored procedures, etc...?

you were better off backing up the database from sql 2000 (right click db, all tasks > backup database), then restoring in from device on 2005, that would have kept all your data
|||

if you want the entire dbs data you can perform backup and restore in sql 2005........for certain objects you can script them and execute the script in sql 2k5.......

Sunday, March 11, 2012

copy database easiest way!

Hi I have a database with tables, views, stored procedures and want to put a
copy of it on another server. Just wondering an easy way to do this? I have
Enterprise manager, SQL query analyzer as well.
--
Paul G
Software engineer.I prefer backup and restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> Hi I have a database with tables, views, stored procedures and want to put a
> copy of it on another server. Just wondering an easy way to do this? I have
> Enterprise manager, SQL query analyzer as well.
> --
> Paul G
> Software engineer.|||ok can I do this from Enterprise manager on the source machine?
thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> I prefer backup and restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > Hi I have a database with tables, views, stored procedures and want to put a
> > copy of it on another server. Just wondering an easy way to do this? I have
> > Enterprise manager, SQL query analyzer as well.
> > --
> > Paul G
> > Software engineer.
>|||Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
on that (which you can do remotely in EM , connected to the target machine..).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> ok can I do this from Enterprise manager on the source machine?
> thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> I prefer backup and restore.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> > Hi I have a database with tables, views, stored procedures and want to put a
>> > copy of it on another server. Just wondering an easy way to do this? I have
>> > Enterprise manager, SQL query analyzer as well.
>> > --
>> > Paul G
>> > Software engineer.
>>|||Hi thanks for the additional information. Just had a question on the copy, I
found the back and restore as part of the tools of EM. I could not find the
copy, can you copy the dbase just like a standard file from explorer?
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> Do the backup on the source machine. Copy the backup file to the target machine. Then do the restore
> on that (which you can do remotely in EM , connected to the target machine..).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > ok can I do this from Enterprise manager on the source machine?
> > thanks.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> I prefer backup and restore.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> >> > Hi I have a database with tables, views, stored procedures and want to put a
> >> > copy of it on another server. Just wondering an easy way to do this? I have
> >> > Enterprise manager, SQL query analyzer as well.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
>|||Only if you detach it from the SQL Server or stop the SQL Server service
first...detach/attach is preferred
look up sp_detach_db in Books Online
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Yes, a regular file copy operation (explorer)...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the copy, I
> found the back and restore as part of the tools of EM. I could not find the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target machine. Then do the
>> restore
>> on that (which you can do remotely in EM , connected to the target machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want to put a
>> >> > copy of it on another server. Just wondering an easy way to do this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Hi Paul,
After the backup, Just copy the .BAK file to destination server. After that
restore the database in destination server using
Enterprise Manager. Otherwise use the RESTORE DATABASE from Query analyzer.
Thanks
Hari
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
> copy, I
> found the back and restore as part of the tools of EM. I could not find
> the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>|||Here's a link that might help you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> Hi thanks for the additional information. Just had a question on the
copy, I
> found the back and restore as part of the tools of EM. I could not find
the
> copy, can you copy the dbase just like a standard file from explorer?
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
> > Do the backup on the source machine. Copy the backup file to the target
machine. Then do the restore
> > on that (which you can do remotely in EM , connected to the target
machine..).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > ok can I do this from Enterprise manager on the source machine?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> I prefer backup and restore.
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > >> > Hi I have a database with tables, views, stored procedures and want
to put a
> > >> > copy of it on another server. Just wondering an easy way to do
this? I have
> > >> > Enterprise manager, SQL query analyzer as well.
> > >> > --
> > >> > Paul G
> > >> > Software engineer.
> > >>
> > >>
> >
> >|||Hi,
In this approach the source database will be unavailable until you attach
the database back. This approach is not recommended in
a production server.
Thanks
Hari
SQL Server MVP
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:%23jtPRLOfFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Only if you detach it from the SQL Server or stop the SQL Server service
> first...detach/attach is preferred
> look up sp_detach_db in Books Online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>> Hi thanks for the additional information. Just had a question on the
>> copy, I
>> found the back and restore as part of the tools of EM. I could not find
>> the
>> copy, can you copy the dbase just like a standard file from explorer?
>> --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the target
>> machine. Then do the restore
>> on that (which you can do remotely in EM , connected to the target
>> machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> > ok can I do this from Enterprise manager on the source machine?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I prefer backup and restore.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>> >> > Hi I have a database with tables, views, stored procedures and want
>> >> > to put a
>> >> > copy of it on another server. Just wondering an easy way to do
>> >> > this? I have
>> >> > Enterprise manager, SQL query analyzer as well.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>>
>|||Hi thanks for all the replies. I created a sample database and then backed
it up. I then detatched it but could not find it to re-attatch it. I tried
to re-attatch the backup but it said this is not a correct file format. Also
when I actually copy it over do I use the database wizard?
--
Paul G
Software engineer.
"Ron Hinds" wrote:
> Here's a link that might help you:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> > Hi thanks for the additional information. Just had a question on the
> copy, I
> > found the back and restore as part of the tools of EM. I could not find
> the
> > copy, can you copy the dbase just like a standard file from explorer?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Do the backup on the source machine. Copy the backup file to the target
> machine. Then do the restore
> > > on that (which you can do remotely in EM , connected to the target
> machine..).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > > ok can I do this from Enterprise manager on the source machine?
> > > > thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> I prefer backup and restore.
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://www.solidqualitylearning.com/
> > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >>
> > > >>
> > > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > > >> > Hi I have a database with tables, views, stored procedures and want
> to put a
> > > >> > copy of it on another server. Just wondering an easy way to do
> this? I have
> > > >> > Enterprise manager, SQL query analyzer as well.
> > > >> > --
> > > >> > Paul G
> > > >> > Software engineer.
> > > >>
> > > >>
> > >
> > >
>
>|||I ended up creating an empty database at the desintation machine and then
using export data wizard from the source dbase (EM). Seemed to work ok.
--
Paul G
Software engineer.
"Paul" wrote:
> Hi thanks for all the replies. I created a sample database and then backed
> it up. I then detatched it but could not find it to re-attatch it. I tried
> to re-attatch the backup but it said this is not a correct file format. Also
> when I actually copy it over do I use the database wizard?
> --
> Paul G
> Software engineer.
>
> "Ron Hinds" wrote:
> > Here's a link that might help you:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> > > Hi thanks for the additional information. Just had a question on the
> > copy, I
> > > found the back and restore as part of the tools of EM. I could not find
> > the
> > > copy, can you copy the dbase just like a standard file from explorer?
> > > --
> > > Paul G
> > > Software engineer.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Do the backup on the source machine. Copy the backup file to the target
> > machine. Then do the restore
> > > > on that (which you can do remotely in EM , connected to the target
> > machine..).
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > Blog: http://solidqualitylearning.com/blogs/tibor/
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> > > > > ok can I do this from Enterprise manager on the source machine?
> > > > > thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > >> I prefer backup and restore.
> > > > >>
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> http://www.karaszi.com/sqlserver/default.asp
> > > > >> http://www.solidqualitylearning.com/
> > > > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > > > >>
> > > > >>
> > > > >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > >> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> > > > >> > Hi I have a database with tables, views, stored procedures and want
> > to put a
> > > > >> > copy of it on another server. Just wondering an easy way to do
> > this? I have
> > > > >> > Enterprise manager, SQL query analyzer as well.
> > > > >> > --
> > > > >> > Paul G
> > > > >> > Software engineer.
> > > > >>
> > > > >>
> > > >
> > > >
> >
> >
> >|||It seems like you've mixed things up....
When you backup the database, you get a file with the extention .BAK
(..actually it can have what ever extension you give it, but .BAK it the
"standard"). This .BAK file is just a normal file that you can copy as any
other file. If you copy this file to a location on your destination server,
you can restore this file on the destination server, and then you'll have an
exact copy of your database.
When you use sp_detach_db/sp_attach_db it's the actual files belonging to
the database you are working with (Database and logfiles). If you run
sp_detach_db, you'll have to copy the .mdf and .ldf file from the source
server to the destination server. When you've copied the files, you'll have
to run sp_attach_db BOTH on the source server (to get the database attach
again) and on the destination server (to get the database created).
Both the BACKUP/RESTORE and sp_detach_db/sp_attach_db syntaxes can be found
in Books On Line.
Regards
Steen
Paul wrote:
> I ended up creating an empty database at the desintation machine and
> then using export data wizard from the source dbase (EM). Seemed to
> work ok.
>> Hi thanks for all the replies. I created a sample database and then
>> backed it up. I then detatched it but could not find it to
>> re-attatch it. I tried to re-attatch the backup but it said this is
>> not a correct file format. Also when I actually copy it over do I
>> use the database wizard?
>> --
>> Paul G
>> Software engineer.
>>
>> "Ron Hinds" wrote:
>> Here's a link that might help you:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
>> Hi thanks for the additional information. Just had a question on
>> the copy, I found the back and restore as part of the tools of EM.
>> I could not find the copy, can you copy the dbase just like a
>> standard file from explorer? --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>> Do the backup on the source machine. Copy the backup file to the
>> target machine. Then do the restore on that (which you can do
>> remotely in EM , connected to the target machine..).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
>> ok can I do this from Enterprise manager on the source machine?
>> thanks.
>> --
>> Paul G
>> Software engineer.
>>
>> "Tibor Karaszi" wrote:
>>> I prefer backup and restore.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
>>> Hi I have a database with tables, views, stored procedures and
>>> want to put a copy of it on another server. Just wondering an
>>> easy way to do this? I have Enterprise manager, SQL query
>>> analyzer as well. --
>>> Paul G
>>> Software engineer.|||thanks for the additional information.--
Paul G
Software engineer.
"Steen Persson (DK)" wrote:
> It seems like you've mixed things up....
> When you backup the database, you get a file with the extention .BAK
> (..actually it can have what ever extension you give it, but .BAK it the
> "standard"). This .BAK file is just a normal file that you can copy as any
> other file. If you copy this file to a location on your destination server,
> you can restore this file on the destination server, and then you'll have an
> exact copy of your database.
> When you use sp_detach_db/sp_attach_db it's the actual files belonging to
> the database you are working with (Database and logfiles). If you run
> sp_detach_db, you'll have to copy the .mdf and .ldf file from the source
> server to the destination server. When you've copied the files, you'll have
> to run sp_attach_db BOTH on the source server (to get the database attach
> again) and on the destination server (to get the database created).
> Both the BACKUP/RESTORE and sp_detach_db/sp_attach_db syntaxes can be found
> in Books On Line.
> Regards
> Steen
> Paul wrote:
> > I ended up creating an empty database at the desintation machine and
> > then using export data wizard from the source dbase (EM). Seemed to
> > work ok.
> >
> >> Hi thanks for all the replies. I created a sample database and then
> >> backed it up. I then detatched it but could not find it to
> >> re-attatch it. I tried to re-attatch the backup but it said this is
> >> not a correct file format. Also when I actually copy it over do I
> >> use the database wizard?
> >>
> >> --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Ron Hinds" wrote:
> >>
> >> Here's a link that might help you:
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:72B3B21C-D957-4226-AA06-110D04620F41@.microsoft.com...
> >> Hi thanks for the additional information. Just had a question on
> >> the copy, I found the back and restore as part of the tools of EM.
> >> I could not find the copy, can you copy the dbase just like a
> >> standard file from explorer? --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Do the backup on the source machine. Copy the backup file to the
> >> target machine. Then do the restore on that (which you can do
> >> remotely in EM , connected to the target machine..).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:518F6560-9DF4-4BC9-8C06-670309756C98@.microsoft.com...
> >> ok can I do this from Enterprise manager on the source machine?
> >> thanks.
> >> --
> >> Paul G
> >> Software engineer.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >>> I prefer backup and restore.
> >>>
> >>> --
> >>> Tibor Karaszi, SQL Server MVP
> >>> http://www.karaszi.com/sqlserver/default.asp
> >>> http://www.solidqualitylearning.com/
> >>> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>>
> >>>
> >>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >>> news:88EC11CC-6E28-4CE0-91DF-2B38E96D19B4@.microsoft.com...
> >>> Hi I have a database with tables, views, stored procedures and
> >>> want to put a copy of it on another server. Just wondering an
> >>> easy way to do this? I have Enterprise manager, SQL query
> >>> analyzer as well. --
> >>> Paul G
> >>> Software engineer.
>
>

Copy database compability

Hi,

In sql 2000 was possible to copy entire database objects from one db to another. This includes tables, pk, indexes, views, sp, functions, logins and so on.

This is very usefull because at the same time it creates a full log with entire database scripting, it can be used to track changes in databases.

In sql 2005, I can't find a solution like this.

Someone have an idea to this?

Even in SQL2005 you can copy whole database with complete schema intact using SSIS (Transfer database task).

Thanx

Thursday, March 29, 2012

Copy Permissions

Copy Permissions

copy on condition

Tuesday, March 27, 2012

Copy items from one SQL Database to another...

Copy ID from one table to another as foreign key

Sunday, March 25, 2012

Copy DB to a different Machine

Copy DB tables and stored procedures to a new DB with different name

Copy DB structure into a new DB

Copy DB structure into a new DB

Copy DB structure into a new DB

Copy DB from 2005 to 2000

Copy DB from 2005 to 2000

Copy DB from 2005 to 2000

Tuesday, March 20, 2012

Copy Database Wizard

Monday, March 19, 2012

copy database tables

Copy database from SQL 2k to 2005?

Sunday, March 11, 2012

copy database easiest way!

copy database easiest way!

copy database easiest way!

Copy database compability

Converting problem

Blog Archive

About Me