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
309 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 42

Accepted Solution

by:
EugeneZ 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now