[RESOLVED] SQL 2005 Backup Table

[RobDog888]

Is there a SP or scripted task that I can use to backup a single tables contents to a file? Then I also need to script the table with all foreign dependancies and all custom data types too.

I have a 6+ GB database that I need to download from a clients servers and restore on mine for testing and development of a new project for them.

I could backup the entire database but it may take too long and put too much of a resource drain on their servers as users are online almost 24/7.

Thanks

[RobDog888]

Oh this is a hot thread

Well I decided to shrink the db and download it. Got it down to ~5.5 Gbs and have another 1.5 hours left to download, total will be 10 hours

We shall see if I have no issues attaching it as I think the issues will be of user accounts and permissions.

[GaryMazzone]

I asked the same question a while ago Rob. The short answer I got was not available out side some commercial applications.

[RobDog888]

Finally got back to this as I have Server 2008 installed with SQL Server 2005 SP-2. I restored the database's but there is a common sql user account that I need to reattach to a new account on my sql server instance but I cant create the user account as the dbs have it from the backup.

How can I purge them or create a new account using the same name and relink the two?

I'm sure it has to do with the SID being diffferent between servers.

[szlamany]

Robert - sorry - I never saw this thread!

You can make a .BAK backup of a 6 gb database - that won't drain the user/server experience.

Then you zip that up.

You would be surprised - it will be 100 or 200 MB after that zip!

That user problem you are having is pain - I've seen others with it.

Are you simply attaching the DB - or are you using a restore??

[RobDog888]

I used a restore. Too bad I didnt zip the bak file lol. Didnt think it would compress even more.

I remember there was a sp that you could run to reseat a sql login but its been about 5+ years since I used it. Cant find it in searching, yet.

[RobDog888]

I think I found it.

http://support.microsoft.com/default.aspx/kb/246133

[RobDog888]

Hmm, thats only to move the login. Too much work plus from what I've read it deletes the login on the source server. I need to retain it.

How about removing it from the restored db so I can create it again?

[tommygrayson]

Hmm, thats only to move the login. Too much work plus from what I've read it deletes the login on the source server. I need to retain it.

How about removing it from the restored db so I can create it again?

Rob, this thread is too hot for me to understand.

You cannot login using the same account found on the database server?

I also had that problem what I do is I remove the user account found on the users section of the database and replace it with mine.

This occurs when SID's or passwords are different but the user name is the same.

[RobDog888]

Bah! I had that user account still associated with another database. Thats why I thought there was still something retained in the db. I have 3 SQL databases that I downloaded and restored all with the same user account. I deleted from two but forgot the third. Guess thats multitasking for you.

Ok, one more issue. I need to still be able to connect from one workstation (XP) to the sql server in a workgroup environment. My connection string is valid but I think it may be permission issues now.

I have the user set as dbo on the 3 databases now and still I cant connect.

[szlamany]

We've had a couple of these workstation authentication threads in the past month or so.

I believe they were resolved by enabling SQL Authentication and adding a SQL login.

I'm not 100% sure but I believe that Windows Authentication only works on a domain (I've only ever had domains - so I cannot know for sure - only based on what the posters have had issues with).

[RobDog888]

Yes, I have it setup as "SQL Server and Windows Authentication mode" as SQL only is no longer available under 2005. The concerned login account assigned as dbo of the three dbs is a sql user account.

Connectionstring:

Cnn.ConnectionString = "driver={SQL Server};server=MyServer;database=MyDatebase;uid=MyUser;pwd=mypassword;"

Ive tried all the different connection strings that are relevant but could it be server collations? Btw, this is a VB 6 app.

[tommygrayson]

We've had a couple of these workstation authentication threads in the past month or so.

I believe they were resolved by enabling SQL Authentication and adding a SQL login.

I'm not 100% sure but I believe that Windows Authentication only works on a domain (I've only ever had domains - so I cannot know for sure - only based on what the posters have had issues with).

Just like he said Windows Authentication works only on domains.

Also most of the connection errors in the SQL is caused by networking issues mostly.

Try pinging the computer name of the database server and see if it returns any requests.

[szlamany]

Won't be sql collation - don't go there...

I'm not sure about your connection string - but I don't want to lead you down a bad path - if it worked before it's probably an authentication/networking issue.

Our VB6 connections are made like this:

    If strDriver = "" Then
        strDriver = "SQLOLEDB"
    End If
    
    'Create a connection to the database
    Set gCn = New ADODB.Connection
    
    gCn.Provider = strDriver
    gCn.Properties("Data Source").Value = strServer
    gCn.Properties("Initial Catalog").Value = strDatabase
    If gstrUser <> "" Then
        gCn.Properties("User Id").Value = gstrUser
        gCn.Properties("Password").Value = gstrPassWord
    Else
        gCn.Properties("Integrated Security").Value = "SSPI"
    End If

SQLOLEDB is the driver for our MDAC/VB6 apps

[RobDog888]

Yes its a workgroup but there is no setting for sql only in 2005. In sql 2000 there was.

Yes I can ping it from XP and also open the workgrouped computers near me and login when prompted.

-2147467259
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

Edit: Its probably an old connectionstring but it works on the clients server. On my workgroup maybe not but I tried all standard and .net strings.

Is there anything on the sql server that I should "allow"? Also, since this is server 2008 perhaps its blocking the request with some "new" security

I've always done the ado connections this way and its always worked. The only thing that may be different is workgroup and 2008

[tommygrayson]

Yes its a workgroup but there is no setting for sql only in 2005. In sql 2000 there was.

Yes I can ping it from XP and also open the workgrouped computers near me and login when prompted.



Edit: Its probably an old connectionstring but it works on the clients server. On my workgroup maybe not but I tried all standard and .net strings.

Is there anything on the sql server that I should "allow"? Also, since this is server 2008 perhaps its blocking the request with some "new" security

I've always done the ado connections this way and its always worked. The only thing that may be different is workgroup and 2008

Is there any firewall installed? Windows firewall turned on? Forefront Security Server turned on? Antivirus blocking your ports?

How about creating a whole new user account not found on the users section of your database? Does it work?

[RobDog888]

After changing to oledb its still getting denied so it must be a security setting on server 08.

Cnn.ConnectionString = "Provider=sqloledb;Data Source=myserver;Initial Catalog=Production;User Id=who;Password=pwd"

Cnn.ConnectionString = "Provider=sqloledb;Data Source=myserver;Initial Catalog=mydatabase;User Id=me;Password=pwd"

Error message when selecting "Securables" from the user properties

[szlamany]

SQL 2008

http://forums.microsoft.com/MSDN/Sho...90907&SiteID=1

Google for:

sql 2008 cannot resolve the collation conflict

and you might very well be in front of a large swell...

Why 2008 (except for the obvious reasons that you have it!)...

[RobDog888]

Um, I have sql 2005 sp-2 but it is windows server 2008 RC1

[RobDog888]

What I will need to do is create a new instance of SQL 2005 with the correct collations, apply SP2, detach and attach databases, ensure that logins are correct, make changes on Team Foundation Application Tier, etc, etc.

That is alot of work, plus the added (and needless) overhead of another SQL 2005 instance. *Sigh*

Guess I will have to try reinstalling damn sql 2005, sp-2 and reselect a different collation. I had choosen the default one but noooo!

I had turned off the firewall on server 2008, created my user account with the same name as whats on my XP box, added it to the admin group and yet still cant connect.

[szlamany]

RD - it seems from this (SQL 2000 through)

http://msdn2.microsoft.com/en-us/lib...9(sql.80).aspx

That you can change collation settings after an install

For SQL 2005 Express

http://technet.microsoft.com/en-us/l.../ms143311.aspx

http://technet.microsoft.com/en-us/l.../ms143508.aspx

and especially this one...

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

[RobDog888]

Hmm Im thinking of just reformatting the server and downgrading it with installing 2003 R2 and then reinstall sql 2005 sp-2. I will make sure the collations match this time. For some reason I rebooted the server and the collation issue seems to have gone away.

If your connectionstring passes an appropriate user account it shouldnt matter if its in a workgroup or domain but the ability for one system to access the other is crucial. I thinkaccess is not an issue as I can see the server in net neighborhood and even logon.

Just a bit stumped here on this one.

[RobDog888]

I figure I should post the solution to the connection issue.

For whatever reason when installing SQL 2005 on Server 2008, it disabled TCP and Pipes. I had to go into the SQL server Configuration Utility and enable them and thn programmatically connecting to them is good.