Can I run a sql back up job using older version SQL server on a newer version of SQL database

I have SQL Server 2012 Express installed on this server. However I cannot automatically run a backup job since the SQL server agent cannot be automated on Express version.  Which is fine as I knew about it before implementing 2012 express.

I also have the SQL Server 2008 R2 Standard  - fully licensed software.  What I want to do is:

1.  Install SQL Server 2008R2  as well on the same server ( as a second instance ) - I believe this is possible ?

2. Then once this is installed - create a backup job to back up one of the SQL Server 2012 express databases. Is this possible ?

if so how would i do it ?  Would I be able to install another instance of the SQL server Management studio for 2008 R2 so I can create a job ?  Most importantly as asked above, will the 2008 R2 SQL agent backup a 2012 database ?
indikadAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Eugene ZConnect With a Mentor Commented:
<1.  Install SQL Server 2008R2  as well on the same server ( as a second instance ) - I believe this is possible ?
-yes

<2. Then once this is installed - create a backup job to back up one of the SQL Server 2012 express databases. Is this possible ?
-yes

<if so how would i do it ?
-- use  script
< Would I be able to install another instance of the SQL server Management studio for 2008 R2 so I can create a job ?  

-if you need - yes
Sql agent is just "scheduler"   where from you can run code..
more about sql agent :
http://technet.microsoft.com/en-us/library/ms189237.aspx

<Most importantly as asked above, will the 2008 R2 SQL agent backup a 2012 database ?

- yes



however, you may consider "free" solution" windows feature: "Windows Scheduled Tasks"


you can set sql script for backup ( not just sql express)

in notepad or from SSMS create this file
mySqlExprBackup.sql
with like this code (just adjust )
-----
BACKUP DATABASE MyDB TO  DISK = N'H:\backup\MyDDB.bak'
WITH NOFORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
----

and call it via batch file from Windows Scheduled Tasks as scheduled job there
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S
yourserver\SQLExpress -i H:\backup\script\mySqlExprBackup.sql


--

and or use 3rd party tools\ Sql dev edition (~$50)\ etc

more:

Configure a Scheduled Task Item (At least Windows 7)
http://technet.microsoft.com/en-us/library/dd851678.aspx





BACKUP (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms186865.aspx
0
 
r3nderConnect With a Mentor Commented:
SQL Managment Studio Express should allow you to administer local sql express instances.

Here is the info
 
BACKUP DATABASE [AdventureWorks] TO  
    DISK = N'\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10

RESTORE DATABASE [AdventureWorksNew] 
    FROM  DISK = N'\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak' 
    WITH  FILE = 1,  
    MOVE N'AdventureWorks_Data' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Data.mdf',  
    MOVE N'AdventureWorks_Log' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Log.ldf',  
    NOUNLOAD,  STATS = 10

Open in new window

0
 
indikadAuthor Commented:
r3nder,

SQL Management Studio Express doe allow to administer local sql express instances,

but what I need is to setup a backup job. The backup jobs are run by the SQL Agent and the agent that comes with SQL Express has a restriction - it will not automatically run.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
r3nderCommented:
0
 
Éric MoreauSenior .Net ConsultantCommented:
give a try to ExpressMaint: http://expressmaint.codeplex.com/
0
 
indikadAuthor Commented:
Thanks EugeneZ and others for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.