Solved

NAMING A BACKUP

Posted on 2004-07-30
10
258 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 …
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

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