Solved

NAMING A BACKUP

Posted on 2004-07-30
10
257 Views
Last Modified: 2012-05-05
I am going to run a job nightly to restore backups from my prod box to my dev box.  Is it possible to drop and replace a backup with the same name?  

Let me clarify a little better.

When I wrote the script to restore the backups from the prod box to the dev box, I included the name of the backup file; exp.(backup_db_2004.bak).  Each night my backup runs, a new file is placed in the backup directory with a unique name.  Is there a way to change the name of that file before running my script?
0
Comment
Question by:luserafino
[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
  • 4
10 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678089
Im not quite following,   are you wanting to change the filename before a restore, or specify a filename in a restore statement or specify a filename for a backup?
0
 

Author Comment

by:luserafino
ID: 11678202
I need to either change the filename before the restore or specify a filename for a backup.  Either way, the file name has to stay the same or be changed in order for me not to have to go into the script and change it daily. If you can tell me how to change the filename before the restore, that would be perfect.  Here is my script:

USE master

RESTORE FILELISTONLY
FROM DISK = '\\production_serv\F$\SQLBCKUP\bkupdir\database.bak'
RESTORE DATABASE database
FROM DISK = '\\production_serv\F$\SQLBCKUP\bkupdir\database.bak'
WITH RECOVERY,
MOVE 'database_dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.mdf',
MOVE 'database_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.ldf'

GO
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678268
Ah ok.


personally I would create a little SP

CREATE PROCEDURE usp_Restore(@Filename varchar(200)) AS
BEGIN

RESTORE FILELISTONLY
FROM DISK =@Filename
RESTORE DATABASE database
FROM DISK = @Filename
WITH RECOVERY,
MOVE 'database_dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.mdf',
MOVE 'database_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.ldf'


END
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678272
i think that should do the trick
0
 

Author Comment

by:luserafino
ID: 11678598
Forgive me for being so ignorant, but how do i pass the name?  i am pretty new to this.  
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678619
you would call this stored procedure by doing :


usp_Restore '\\production_serv\F$\SQLBCKUP\bkupdir\database.bak'

for example.
0
 

Author Comment

by:luserafino
ID: 11678774
Okay...maybe I'm not getting this.  One more time. :)  

My backup runs each night.  It names the file each night a different name that includes the date.

In order to run this automated (scheduled job), the name of that file has to be changed before I do a restore, how do i do that?  I must be having a blonde moment here.

Your suggestion for running an SP is good, however, when I call it, the name of the backup from the night before will be different each day. Am I making sense?
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 50 total points
ID: 11678949
lets say for example:

you name your backup files

c:\20040730backup.bak



if you called


 usp_Restore 'c:\' +  convert(varchar(8),getdatE(),112) + 'backup.bak'

or you could modify the sp to :


CREATE PROCEDURE usp_Restore AS
BEGIN

DECLARE @Filename varchar(100)
SET @Filename='c:\' +  convert(varchar(8),getdatE(),112) + 'backup.bak'


RESTORE FILELISTONLY
FROM DISK =@Filename
RESTORE DATABASE database
FROM DISK = @Filename
WITH RECOVERY,
MOVE 'database_dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.mdf',
MOVE 'database_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.ldf'


END


0
 

Author Comment

by:luserafino
ID: 11679028
i gotcha.  thanks for the help and being so patient. Your the best!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11679078
your welcome.  
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 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