Solved

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

Posted on 2014-02-02
6
313 Views
Last Modified: 2014-02-06
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 ?
0
Comment
Question by:indikad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Assisted Solution

by:r3nder
r3nder earned 100 total points
ID: 39828699
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
 

Author Comment

by:indikad
ID: 39828714
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
 
LVL 6

Expert Comment

by:r3nder
ID: 39828795
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 43

Accepted Solution

by:
Eugene Z earned 400 total points
ID: 39829159
<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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39829163
give a try to ExpressMaint: http://expressmaint.codeplex.com/
0
 

Author Closing Comment

by:indikad
ID: 39840619
Thanks EugeneZ and others for the help.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

635 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