[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

SQL SErver 2008 automated copy/restore

Hi Experts,

I would like to copy a database and restore it on another database (on the same instance)everynight. How can I automate this?

Thx
0
dlan75
Asked:
dlan75
  • 2
  • 2
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
The easiest way would just be to setup a SQL Agent job to restore the backup over the existing database. I assume you already have a job to backup the main database, so you just need to grab that file and restore over the existing "copy" database.

You might have to code to figure out the name of the backup if you are including any timestamps, or have some naming convention in place.
0
 
dlan75Author Commented:
Thx for the fast repy.
Could you give me some kind of procedure?
0
 
Carl TawnSystems and Integration DeveloperCommented:
It's difficult to be precise without knowing the make up of your database (number of data files, log files, locations, etc), but logically you want something like the following in the step of a SQL Agent job:
RESTORE DATABASE [YourCopyDatabaseName]
    FROM FILE = N'<path_and_name_of_your_backup_file'
    WITH
         MOVE '<logical_data_file_name>' TO '<physical_path_of_file_for_your_copy_database>'
         MOVE '<logical_log_file_name>' TO '<physical_path_of_file_for_your_copy_database>'
         REPLACE

Open in new window

More detailed info on the syntax and options are here: http://technet.microsoft.com/en-us/library/ms186858.aspx

Specifically you should be looking at examples D and E towards the bottom of the page.
0
 
dlan75Author Commented:
thx will make my way around that :-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now