?
Solved

Copy MSSQL DB to another DB on same server

Posted on 2009-12-23
9
Medium Priority
?
300 Views
Last Modified: 2012-05-08
I would like to copy database A to database B at a specific time.  I want to overwrite whatever data is in database B at the time.  Both DB's exist on the same server.  I'm running MS SQL 2005
0
Comment
Question by:tgtcat69
  • 5
  • 3
9 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26115850
do the contents of the dbA and dbB identical? I mean the tables, procedures, views, etc. If so you may detach dbA, copy the tables with new name to a storage. Attach the dbA. Detach the dbB.. Attach the copied files from the dbA with move option as dbB.. Now yoou have the identical databases with different names...
 
0
 
LVL 25

Expert Comment

by:jogos
ID: 26115862
backup A (should normaly exists , but maybe not of the moment you want)  restore backup of A but replace name of target database with B + check in options-page that also the filenames are changed or change them manualy.
0
 
LVL 25

Expert Comment

by:jogos
ID: 26116604
Actions in my previous comment are through the interface, so a one time operation.
backup / restore are perfectly scriptable and if you wish to do that every weekend/month .... by putting that script in a sql*agent-job.

Easiest way to make script. Start profiler trace, do your backup /restore by using the interface (sql management studio) so these actions are captured. look for the sql-statements.  If database B does not exists yet you must at least try it twice, the override existing database could be a problem otherwise.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:tgtcat69
ID: 26118744
Currently, the contents are not identical.  There may not be a time where they will be identical because changes will always be written to database A and not database B.

Jogos, I tried creating a script, but everything I saw included the name of the backup, which contained the date, and thus would be unique.
0
 
LVL 25

Accepted Solution

by:
jogos earned 200 total points
ID: 26118944
I forgot,  there is even a script-button in management studio.  You can enter your options for your backup in dthe GUI and then script it without having to execute it.

Important is the COPY_ONLY this will prevent that with a recovery model FULL your next transactionlog refers to this backup but still to the backup of the maintenance plan

Made a small example
-- Backup database 'dbA'
BACKUP DATABASE [dbA] 
TO  DISK = N'C:\MSSQL\Backup\dbB.Comp090525220021.BAK'  
WITH NOFORMAT, NOINIT, 
 NAME = N'dbA-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
,  COPY_ONLY -- don't interfere with backup-schema
GO

-- Restore database 'DbA' from previously taken backup
-- command invalidated to prevent accidents
--RESTORE DATABASE [dbA] 
RE   STORE DATABASE [dbA] 
FROM  DISK = N'C:\MSSQL\Backup\dbB.Comp090525220021.BAK'  
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

-- Restore database 'dbB' from previously taken backup of database 'dbA'
-- command invalidated to prevent accidents
--RESTORE DATABASE [dbB] 
RE   STORE DATABASE [dbB] 
FROM  DISK = N'C:\MSSQL\Backup\dbB.Comp090525220021.BAK' 
WITH  FILE = 1,  
MOVE N'db_Data' TO N'C:\mssql\DATA\dbB.mdf',  
MOVE N'db_Log' TO N'C:\mssql\DATA\dbB_1.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

Open in new window

0
 
LVL 6

Author Comment

by:tgtcat69
ID: 26119084
I think you lost me.

I did try the script option in the management studio, but that's how I got the unique filename thing.  Can you elaborate a little?
RESTORE DATABASE [DoorTrainingData] FROM  DISK = N'E:\SQLbackup\UpdatedNightly\UpdatedNightly_backup_200912232300.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [DoorTrainingData] FROM  DISK = N'\\server\systems\polaris\transaction_logs\UpdatedNightly\UpdatedNightly_backup_200912240000.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [DoorTrainingData] FROM  DISK = N'\\server\systems\polaris\transaction_logs\UpdatedNightly\UpdatedNightly_backup_200912240200.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [DoorTrainingData] FROM  DISK = N'\\server\systems\polaris\transaction_logs\UpdatedNightly\UpdatedNightly_backup_200912240400.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [DoorTrainingData] FROM  DISK = N'\\server\systems\polaris\transaction_logs\UpdatedNightly\UpdatedNightly_backup_200912240600.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [DoorTrainingData] FROM  DISK = N'\\server\systems\polaris\transaction_logs\UpdatedNightly\UpdatedNightly_backup_200912240800.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Open in new window

0
 
LVL 25

Expert Comment

by:jogos
ID: 26119210
First you have to have a backup
you:
used a backup made by a automatic backup system (Complete backup E::\sqlbackup...taken at 2009/12/23 23:00) and additional tranasctionlog backups (every 2 hours)
Me:
I wrote a script to make a complet backup and gave it an name which I choose, ok there is jet also a date in but it could aswel be 'e:\tataaaaaaa.bak'). I added COPY_ONLY to not interfere with the chain off automatic backups

Then you need to restore
You : restored database DoorTrainingData from a backup representing situation at 20091224 08:00
off database UpdatedNightly  (just interpreting the filenames).
I hope it was on a test environment because off the names I would thinkg you want  database UpdatedNightly to be a daily copy off the original  DoorTraingData.

Me
Having taken a copy-only backup from the moment I wanted an given a name I wanted I just have to restore 1 backup-file, every day with the same name.
A restore will automaticaly want to keep the original filenames off the different database-files. This would be a problem because those files already exists for the original database.
So in the restore command I added MOVE 'logical filename'  TO 'newf fysical location and filename' to solve that problem.
Logical filenames can be found in the database properties.
0
 
LVL 25

Expert Comment

by:jogos
ID: 26119242
0
 
LVL 6

Author Comment

by:tgtcat69
ID: 26119747
OK, I think I get the basic idea.  We've created a job on the server that will run nightly and fire the script.

Thanks for all your help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question