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
308 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 69

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

932 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

15 Experts available now in Live!

Get 1:1 Help Now