May 25, 2010 at 1:13 am
Hello,
I have 2 servers, One in SQL 2008 and One in SQL 2000. I have to send 2008 database copy to client in form of 2000 database. I have create a Package in 2008 to transfer all objects from sql 2008 to sql 2000 database. Now my question is I have to perform a Full Backup only from sql 2008 server for sql 2000 db. How I can do that? Once I will do backup, I need to do a zip and place it on FTP. so at the end of the day client wants a database copy of sql 2000 server but I have to create a job only in sql 2008.
please let me know.
Thanks,
nik.....
May 25, 2010 at 1:24 am
You can't restore a backup from one version of SQL Server. The first time the 2000/2005 database is opened in 2008 the structure is changed and won't be recognised by earlier versions.
http://www.sqlservercentral.com/Forums/Topic828380-5-1.aspx as an earlier example of this question.
BrainDonor.
May 25, 2010 at 1:26 am
I do want to do a restore, I just want to take a backup of sql 2000 db from sql 2008 server....then will do a zip and put on FTP for client pickup...
May 25, 2010 at 1:34 am
If I understand you correctly, what you want to do is backup a database that is being used on a SQL Server 2008 system and restore it on a SQL Server 2000 system.
If that is what you want then it can't be done.
Scripting the database and it's contents is probably the best way around this, but not pretty.
BrainDonor.
May 25, 2010 at 1:38 am
You cannot take a backup of a database on a SQL 2008 instance and restore it on SQL 2000. Once a database is attached to a SQL 2008 instance, it is a SQL 20o08 database, regardless of compatibility level. SQL 2000 does not understand the database structure of a SQL 2008 database, and the restore will always fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2010 at 1:40 am
No you get me wrong... let me tell you more clearly.
I have one db in sql 2008 but we need to send same to db to client as sql 2000, so i create one empty db on sql 2000 server then i create ssis package to transfer all objects from sql 2008 to sql 2000. now finally i need to take this sql 2000 db backup , zip and do a ftp for client pick up. now my question i need to create one job to do this automate...
this job needs to be set up only on sql 2008 server.....even though i need to send client a db copy in sql 2000 form...
in job i will call this package then i need to perform a full backup for that sql 2000 db from sql 2008 and zip and ftp process...
May 25, 2010 at 3:51 am
As stated a backup of 2008 cant be restored on 2000. If it were me , i would investigate using SSIS (taking an incremental difference or the tables) or replication as options.
May 25, 2010 at 4:51 am
nik27184 (5/25/2010)
No you get me wrong... let me tell you more clearly.I have one db in sql 2008 but we need to send same to db to client as sql 2000, so i create one empty db on sql 2000 server then i create ssis package to transfer all objects from sql 2008 to sql 2000. now finally i need to take this sql 2000 db backup , zip and do a ftp for client pick up. now my question i need to create one job to do this automate...
this job needs to be set up only on sql 2008 server.....even though i need to send client a db copy in sql 2000 form...
in job i will call this package then i need to perform a full backup for that sql 2000 db from sql 2008 and zip and ftp process...
So on the sql 2008 instance you want to start a process that backups the database on the sql 200 server and then zip / ftp to your client?
Is that what you want?
May 25, 2010 at 5:06 am
You can create a storedproc on the sql2000, that backups the database on the sql2000, returns the filename, then go on in sql2008 and do a ssis ftp upload using this filename.
Marten
May 25, 2010 at 8:52 am
Are you permitted to use linked servers?
have you considered using a Central management server (execute a backup script against the instance from the central management server)?
You could also use SSIS to execute a backup database command against the 2000 box, zip the backup file and ftp it from the ssis package. Using SSIS, you could reuse the package for several different servers.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 25, 2010 at 9:30 am
which utility will help me to perfom sql 2000 backup zip and ftp from ssis .. please advice...
May 25, 2010 at 10:02 am
Not a single utility.
In SSIS, you would issue a backup command via a sql script. This will backup the database to the desired location.
Once you have backed it up, you could use a script object to call a compression application to zip the files in that location (pkzip, winzip, winrar - pkzip would probably be a better bet here).
After you have backed up the database and zipped it, then use another script task or the ftp task to ftp the file to your desired location.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 25, 2010 at 10:04 am
I have all script ready to do a backup, zip and FTP but only concern is can i run a backup script from ssis for sql 2000 db?????
May 25, 2010 at 10:07 am
Yes - you can run that against a 2000 db so long as you are using supported commands for SQL 2000.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 25, 2010 at 10:13 am
i have written a store proc to perform to do a backup zip and ftp process..
my question is i have to run this store proc from ssis for sql 2000 db...
which utility you advised to use in ssis which will do a run under sql 2000 db from ssis...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply