Database Backup for Log Shipping

Hi,
Does the Log Shipping Setup wizard provide any options to do the database
backup and restore after the initialization? what if the table structures
are changed and stored procedures are added? If not, does that mean I have
to start over the setup process again?
THanks
EdI am a little as to what you are asking as Log shipping is simply
Backup and Restore that is somewhat automated. If you add a sp then when the
backup gets applied to the other server it will create that sp there as
well. You might want to read the sections in BooksOnLIne under Log Shipping.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
> Hi,
> Does the Log Shipping Setup wizard provide any options to do the database
> backup and restore after the initialization? what if the table structures
> are changed and stored procedures are added? If not, does that mean I
> have
> to start over the setup process again?
> THanks
> Ed|||Sorry for the question.
I have the log shipping setup and everything works fine. However, the
current database structure needs to be changed, do I need to start over to
setup the log shipping again or I can just easily copy and restored the
altered database to the standby server?
"Andrew J. Kelly" wrote:

> I am a little as to what you are asking as Log shipping is simply
> Backup and Restore that is somewhat automated. If you add a sp then when t
he
> backup gets applied to the other server it will create that sp there as
> well. You might want to read the sections in BooksOnLIne under Log Shippin
g.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:96DCEC95-6391-4BFA-A236-D04F26D5BD1E@.microsoft.com...
>
>|||Hi
The log contains both DML and DDL events, so if you make a change to a SP,
or a table, or a row of data, on the source server, the change will be in th
e
log backup, and since you are using log shipping, the change will be applied
to the destination database automatically.
Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
how SQL Server works internally.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ed" wrote:
> Sorry for the question.
> I have the log shipping setup and everything works fine. However, the
> current database structure needs to be changed, do I need to start over to
> setup the log shipping again or I can just easily copy and restored the
> altered database to the standby server?
> "Andrew J. Kelly" wrote:
>|||Yes, this is the case. It is something that I don't know...
How about Trasactional Replication? I don't think the transfered Reader Log
contains all the DML and DDL, am I right?
Ed
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The log contains both DML and DDL events, so if you make a change to a SP,
> or a table, or a row of data, on the source server, the change will be in
the
> log backup, and since you are using log shipping, the change will be appli
ed
> to the destination database automatically.
> Get "Inside SQL Server 2000" by Kalen Delaney to get a full unserstanding
> how SQL Server works internally.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Ed" wrote:
>|||Replication and Log shipping are two different things. Replication may or
may not pass certain commands off to the subscriber but Log shipping will
make sure the other db is an exact duplicate of the first.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BC2E60AA-5D35-4A7A-B992-50E7B782A267@.microsoft.com...
> Yes, this is the case. It is something that I don't know...
> How about Trasactional Replication? I don't think the transfered Reader
> Log
> contains all the DML and DDL, am I right?
> Ed
> "Mike Epprecht (SQL MVP)" wrote:
>

DataAdapter Wizard and rows not found

I am using MSDE and am trying to configure a dataAdapter. I generates a
couple of statements automatically, but I get an error message about a couple
of other statements. It says that I cannot generate those last statements
because it needs to associate a row with a table. If I am correct there are
now rows in MSDE only tables and columns. What can I do about configuring
MSDE so that that ado wizard can generate all the statements?
Spencer
hi Spencer,
Spencer H. Prue wrote:
> I am using MSDE and am trying to configure a dataAdapter. I generates
> a couple of statements automatically, but I get an error message
> about a couple of other statements. It says that I cannot generate
> those last statements because it needs to associate a row with a
> table. If I am correct there are now rows in MSDE only tables and
> columns. What can I do about configuring MSDE so that that ado wizard
> can generate all the statements?
I do think you missed something in the table mapping schema... and MSDE has
nothing to do with all that.. so I do think you'll have better luck asking
in the dotnet newsgroup hierarchy...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Data Transformation Services Migration wizard

i am trying to use the DTS migration wizard in sql server 2005 to migrate some of the DTS packages that i have on sql server 2000.

After entering the source and destination server i get the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Does anyone know the reson behind this?

Thanks for any help.

I've just installed Server 2005 and am getting the same message. The earlier threads refer to special characters and leading or trailing spaces. I've tried the wizard on a few packages that have nothing but letters in the name, and I get the above message. I tried repairing .NET 2.0 as well (didn't work).

What else should I/we try?

Thanks,

K

|||

Found a forum where a user clarified that NONE of the DTS packages can have a leading/trailing space. Well, one out of a hundred or so packages had a space; after I fixed that one, the wizard worked.

Find the spaces (thanks to Joseph Sack's SQL Server blog):

SELECT DISTINCT name
FROM msdb.dbo.sysdtspackages
WHERE name LIKE '% '

and I'd suggest: or name LIKE ' %'

|||

Thanks a lot.

I had one package that had a space. After i deleted the space i was able to get a little further. but when i hit finish, All the packages display "Stopped" and none gets transferred.

Thanks

Data Transfer Wizard - Not Copying properly

Here is the scenerio

I am trying to copy a database from a live sql server to a local server.

I wanted to copy the tables, triggers, procedures, functions, users

So I have "Copy tranfer of objects " in the DTS wizard.

But the process fails informing that the users are not there in the local server and fails the process.

Now what is my expectation is that why the user logins every thing is not copied to the local sever.

Help would be more appreciated. Pretty urgent.

The quick, easy and reliable way is to backup the live one and restore on the local server it moves everything and gives you the option to change the name of the database. In the backup and restore wizard choose restore from device option, known issues orphaned permissions just delete that restore and create a new one. The other valid option is to dettach and attach but the former is the best I know. Hope this helps.|||

I tried your solution.

This are the steps i follow to do the backup.

1. I select the database from the live server.

2. I wanted to give a file name and need to specify a location.

3. I am not able to specify a location of my local drive instead i am able to specify a location of the live server harddisk which is of no

use to me as i am not able to restore because i dont have access to the live server system.

Solution is pretty urgent.

|||

I think DTS not is properly executed when a field is having a datatype of image in a table.

As the "image" field is holding a binary value it is not properly performed.

How to Replicate the use.

1. Create a database and a table with a field "projectimage" and set it datatype as image.

2. Now try doing DTS and create a new database using DTS

3. I hope the error would be replicated.

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[<tablename]')
Data truncation occurred in table [dbo].[tablename], column 1.

any solutions would be more appericated

|||Now to the correct way to use the backup and restore wizard, is because it is a backup in a different server you put the .bak in the backup subfolder in programs , Microsoft SQL Server, Backup you put the file there and then let Windows create the file path for you when you choose the restore from device option in the wizard that is the most important part because if you don't SQL Server gets confused. So try these instructions and post again. I am sorry I did not add this part in my first post, Hope this helps.|||

Hi caddre, I am looking for more help from you.

In the backup option of live server it shows the files and folder of the live server and not of local disk nor any shared directory. -This is the problem

Similarly in

the backup option of local server it shows the files and folder of the local server and not of the live server.

So I dont find any common place to restore the database.

Please consider that i dont have any access to live server.

|||

I have figured out the solution in msdn

http://support.microsoft.com/kb/257425

|||I am glad you got it resolved but you did not undersatnd my instruction because if you email me your .bak I can restore it in less than five minutes. You can put your .bak in a CD post it to me and I can still restore it in five minutes it is the most efficient way to move SQL Server databases.|||

I Found that was the actually problem but unable to resolve it.

The problem seems to be occur in sql server 7.0 and not in sql server 2000.

I am using sql server 2000 and win xp but still not able solve it.

I need a step by step details or screen sheets would be more helpful.

|||

I have restored SQL Server 7.0 in SQL Server 2000 and 2000 is very good it will ask you if you want to keep TDS(tabular data stream) 70 that is 7.0 compatibility and the .bak was emailed to me on the company network.

Here are the steps right click on the SQL Server 7.0 database go to all tasks and then backup, then use the backup and restore wizard to do a full backup, when you have completed the backup use any zip software like winzip and zip it. Then take the .bak file when you go into SQL Server 2000 in xp go to programs Microsoft SQL Server then there are two subfolders one is the Data folder where the MDF,LDF and IDF are stored the other one is the backup put your .bak there, then go to the backup and restore wizard back in Enterprise manager and click to create a restore it will ask for the location of the .bak file. This is the very important part when you choose restore from device it will want to know the file part click on the ellipse Windows will create the file part for you don't try to create it because if you do SQL Server will get confused, after Windows create the file part you are almost there, it will ask if you want to change the name of the database. There are many threads here where I have helped others and the problem is always the file path let Windows create it but also verify it is correct. Post again if you still have question.

|||

1Here are the steps right click on the SQL Server 7.0 database go to all tasks and then backup, then use the backup and restore wizard todo a full backup,

when you have completed the backup use any zip software like winzip and zip it. - This is the problem.

In the Select Backup Database (4th step in the wizard)

I need to give a file location and click the ellipse button to see the files and folder of live server .

I am not able to see the files and folder of local disk. - this is where i dont really understand

I hope now u have got my problem clearly.

|||

You need to take the .bak file physically to your local SQL Server Backup subfolder you cannot try to do the restore on your local drive from the live server, I know you cannot do that with the SQL Server backup and restore wizard.

Take the .bak put it in your local SQL Server backup folder in programs. I will look for the other thread so you may understand, because I think you are trying to restore on the local one from the live server and that is not correct.

|||

You need to take the .bak file physically to your local SQL Server Backup subfolder you cannot try to do the restore on your local drive from the live server, I know you cannot do that with the SQL Server backup and restore wizard - This is the problem.

I dont have the access to copy the .bak file from live server to local disk.

Is there is any work around.

|||

See if you can register the live server on your local SQL Server right click at the top of Enterprise Manager you will see register server if you can register it then it becomes local to you and you can just copy the .bak into this physical location. If you cannot then you have to create a blank database in your local XP and use DTS to move the data into your local machine. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\

|||

you can just copy the .bak into this physical location - this is the problem.

1. I am able to register the live server in my local server and able to create the .bak file but unable to copy the .bak into this physical location of local

disk. 2.I have tried using DTS, Then only I faced the problem of the image field

The first option is ruled out because iam not able to access it

ie i am using DTS and finding problem with image field which gives me the following error.

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[tablename')
Data truncation occurred in table [dbo].[tablename], column 1

This is a problem in sql server 7.0 and getting solved using sp4 but still iam facing the same problem with sql 2000 and win xp

which i dont know why?

I hope now things clear and leaving for End of Day and hope to see u in the morning.