SQL Server database backup (URGENT)

40 pts.
Tags:
Database Backup
SQL Server 2005 backup
SQL Server backup
Hi I have a db backup requirement. As shown in the attached image current system is as follows:
    There are three source databases such as DB001, DB002 and DB003. Each DB having one table as dbo.EMP and same table schema. Emp table consists of EmpID (PK) and EmpName. One backup database i.e, DBbackup
I need as follows:
    Looping through all DB: "SELECT NAME FROM sys.sysdatabases WHERE NAME LIKE ''DB00%''" Check if table dbo.EMP exist in each DB. If exist then check table "001" exist in DB "DBbackup". >> "SELECT OBJECT_ID('DBbackup.dbo.001','U') AS Check " . This query will return NULL if table "001" not exist. If not exist then create a table by taking part of DB name like "001" with same schema in "DBbackup" database. Select into "001", from dbo.Emp before matching row count to avoid duplicate records.
Can you give idea how to achieve it. Data export of each table is very time consuming. There are more than 150 DBs with huge data. Is there any alternative wizard or inbuilt script is there in SQL server 2005. Can it be achieve by stored procedure. Can we put a time stamp in SP to check the processing time for each DB. Expecting for your solution. Its very urgent. Thanks Sharma

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    I'm really not sure what you are asking for here. Can you try to explain it again?
    66,010 pointsBadges:
    report
  • Plugsharma
    Hi Denny This is like a backup strategy. Migrating to a new system. Before each application having individual DB but actually having only one table. All databases having only one table with same schema. For example i have given EMP table. Now we want to move data to a individual table(for example table "001") in a single DB(DBbackup) from different DBs like DB001 and DB002, etc. New table name will be like '001'. This table refer to DB001 and will have data from EMP table from DB001. DTS allowing only one DB at a time and there are more than 150 tables with millions of records. So this process will take long time. The whole application is accessed 24x7. Can you please write me a script for this. Thanks
    40 pointsBadges:
    report
  • Plugsharma
    Im posting again. Hi I have a db backup requirement. As shown in the attached image current system is as follows: 1. There are three source databases such as DB001, DB002 and DB003. Each DB having one table as dbo.EMP and same table schema. 2. Emp table consists of EmpID (PK) and EmpName. 3. One backup database i.e, DBbackup I need as follows: 1. Looping through all DB: "SELECT NAME FROM sys.sysdatabases WHERE NAME LIKE ''DB00%''" 2. Check if table dbo.EMP exist in each DB. 3. If exist then check table "001" exist in DB "DBbackup". >> "SELECT OBJECT_ID('DBbackup.dbo.001','U') AS Check " . This query will return NULL if table "001" not exist. 4. If not exist then create a table by taking part of DB name like "001" with same schema in "DBbackup" database. 5. Select into "001", from dbo.Emp before matching row count to avoid duplicate records. Can you give idea how to achieve it. Data export of each table is very time consuming. There are more than 150 DBs with huge data. Is there any alternative wizard or inbuilt script is there in SQL server 2005. Hope now you understand. Thanks
    40 pointsBadges:
    report
  • Denny Cherry
    You can use SSIS in a FOR EACH loop using a .NET script in a Script Task to change the source database name, and change the destination table name. You can use BCP from a batch file, using a text file with a list of database names and a FOR loop to process through the list of database exporting each one to a separate text file. You can use a T/SQL loop like this.
    EXEC sp_MSforeachdb 'IF EXISTS (SELECT * FROM ?.sys.tables WHERE name = 'Emp')
    BEGIN
          IF NOT EXISTS (SELECT * FROM DBBackup.sys.tables WHERE name = '?')
                    SELECT *
                    INTO ?
                    FROM ?.dbo.Emp
                    WHERE 1=2
          TRUNCATE TABLE DBBackup.dbo.?
    
          INSERT INTO DBBackup.dbo.?
          SELECT *
          FROM ?.dbo.Emp
    END'
    You will probably be better off just backing up each database separately. You aren't going to save anything by moving the data into a single database.
    66,010 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following