0

The answer from Spike here was very nearly exactly what we needed. The only hurdle is how to have the backup run on one server and the restore run on another through linked server all in one script? It must be a script that can be turned into a stored procedure that's fed only source and destination DB names.

Does it present a problem that the source server is SQL Server Express 2012 whereas the destination is the full version?

It has to be a script that can run against any DB by just changing the DB name and logical names. It's for a process that's need to run very regularly and fully automatically against a different DB each time.

Also, why wouldn't be better to just detach, copy the mdf and ldf, then re-attach?

Community
  • 1
  • 1
Adamantish
  • 1,888
  • 2
  • 20
  • 23
  • 1
    detach would require to bring the DB offline.In case you cant take it offline – Miller Jan 31 '14 at 14:23
  • 1
    @adamantish Use the GUI option for attach/detach. Fill in the options but dont click on OK yet, instead script out the T-SQL for it. Change the script accorindgly to your requirement and use in the main script. – Punter015 Jan 31 '14 at 15:30

2 Answers2

0

I'd suggest right clicking on the database in Management Studio, clicking Tasks then Generate Scripts.

You can script your entire database (make sure in advanced options you select to script both schema and data) and restore it on the other database.

You can also backup from one instance and onto another but bear in mind this may have issues if you're moving between versions.

Transfering LDF/MDF files is possible but in my opinion is difficult and high risk.

Liath
  • 9,913
  • 9
  • 51
  • 81
  • Hiya, just clarified my question to explain that this is not for a one off script. I need to make a stored procedure that I can feed a DB source name, a destination name and off we go. Look at the answer I linked to for a better idea of the kind of problem this is. – Adamantish Jan 31 '14 at 14:33
0

You can copy a database from one server to another, just follow those steps (SQL Server 2008): 1.Select the database in the source server in SSMS. 2.Right click ->Tasks->Copy Database Wizard to launch the copy database wizard. Enter the source and destination credentials and select either attach/detach or SMO type click next and you can schedule or run immediately, Click finish.

For your reference:

http://msdn.microsoft.com/en-us/library/ms188664.aspx

Amo A.
  • 70
  • 2