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
311 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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