Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

NAMING A BACKUP

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
luserafino
Asked:
luserafino
  • 6
  • 4
1 Solution
 
ShogunWadeCommented:
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
 
luserafinoAuthor Commented:
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
 
ShogunWadeCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
ShogunWadeCommented:
i think that should do the trick
0
 
luserafinoAuthor Commented:
Forgive me for being so ignorant, but how do i pass the name?  i am pretty new to this.  
0
 
ShogunWadeCommented:
you would call this stored procedure by doing :


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

for example.
0
 
luserafinoAuthor Commented:
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
 
ShogunWadeCommented:
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
 
luserafinoAuthor Commented:
i gotcha.  thanks for the help and being so patient. Your the best!
0
 
ShogunWadeCommented:
your welcome.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now