Good Script man, it was very helpfull to me, and i hope it will be very much helpfull to others tooo....

Thanks a lot. I needed the file name also in the same naming convention, it was really helpful to me

.

Thnak you so much.  :)

 After many hundreds of years of searching the net and speaking to SQL X Spurts, a last a backup that I can understand

Thanks so much 

Here is an update to the script to also include the time in the filename.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR

SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('test5')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Hi, Thanks for this script.

 Is there anyway to get this to overwrite the files?

I want to dump every day without date in the filename to put them on tape and not get a bunch of files stacked on the server.

But when the job runs the next day it appends on the previous files and they get double in size.

 If you want to overwrite the backup file, add a WITH INIT to the end of the BACKUP command.  This will overwrite as opposed to appending.

Great script but not sure how to get around the date variable as this creates a new database each time. With INIT as the command to overwrite the database, doesn't help if the database has a different name each time.

Not a sql DBA obviously and if you were to change the following to a static name what syntax would you use? thanks

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

 I think all you need to do is eliminate the filedate piece

i.e.

from

SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

to

SET @fileName = @path + @name + '.BAK' 

Greg's script modified for a db that is backed up to a single file name with its contents overwritten would look something like this

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR

SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('armando')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0

BEGIN

    SET @fileName = @path + @name + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName WITH INIT
    FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor

 As I learned some where that cursors are not to be used so just updated the main article without cursors.

Yours scipts are superb, very helpful - Thanks.

I was wondering can the script be modified to backup half of the database instead of the whole DB?

I have a large database and i want to backup half of it because of space isues on our hard disks.

Regards, Riaz

Riaz,

How can we justify the half, as you said you want "to backup half of the database instead of the whole DB".

 It sounds like to what you're referring to is archiving data.  Backing up a database just makes a copy of the data.  If you're experiencing data bloat, then you should look into archiving old data.

If the database is setup using mulitiple Files you can do file level backups instead of a full backup.

If you have enough space on other drives you can split the backup into multiple files.

See this tip:

http://www.mssqltips.com/tip.asp?tip=935

You can also do a backup to a network drive.  See this tip:

http://www.mssqltips.com/tip.asp?tip=1126

Our database is 22553.56 MB

I would like to backup a small amount of this, i am not interested in having all the data backed up as space is an issue on our disk.

Could this be chopped in say half?

maybe i should read into archiving and do it this way.

No matter what you do you will need at least one full backup, so there are not a lot of options.

You could look at using this free tool from Idera that does compressed backups.

This could compress the backup up to 90%.

http://www.idera.com/Content/Show68.aspx?CartID=5290

 Another option is to use BCP to export the raw data table by table.

Ah BCP i will have a go at this one and thanks for the link for a free tool i could also use.

Much appreciated.  Thanks ;)

Thank you for this script.

Does anyone knows how I can make this more admin friendly?  I want to have my daily night backup script call this. Is there a way I can integrate zip into this?

Can I have thie proc call part of another script?

thanks

Manish, thanks a lot for the Idea to avoid cursors - I could not understand before, why not the all databases were backed up.

Small improvement: ms does not recommend to use "SET ROWCOUNT ..." effect with "UPDATE" statement as the this behavior will be possibly changed in next versions of mssql. So, I offer to replace

Update #tempbackup set flag=1 WHERE flag=0

with

Update #tempbackup set flag=1 WHERE flag=0 AND name=@name

Regards,
Yarick.

 hi i have tried this script that backs up all databases and i would say that that is really helpful. i have no problem backing up the databases.

 what i would like to know is how do i reinstate the .bak files into the sql server? what tool should i use if i have no access to the sql server enterprise manager?

thanks for any help.

Hi mon69

I don't know about the tools or any thing to restore the database, how ever I used to restore the same using the batch file.

Copy the following script in notepad and save as C:\Restore.bat. and simply on click your databse will be restored.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 osql -e -S DEV1\SQLEXPRESS -U SA -P manish -Q "RESTORE DATABASE MYDB_May29 FROM DISK = 'C:\Temp\MYDB_May29.bak' WITH REPLACE, MOVE 'MYDB' TO 'C:\Database\May29MYDB.mdf', MOVE 'MYDB_log' TO 'C:\Database\May29MYDB.ldf'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please Note : the following information need to modify

* DEV1\SQLEXPRESS is the name of the server name

* SA is the user name 

* manish is the password

-----------------------------------------

Let me know if u face any issues 

Just in case people are still following this thread, here is Manish's code which has been corrected per Yarick's catch and SQL deprecations:

DECLARE @name VARCHAR(50), -- database name
@path VARCHAR(256), -- path for backup files
@fileName VARCHAR(256), -- filename for backup
@fileDate VARCHAR(20) -- used for file name

CREATE TABLE [dbo].#tempBackup (name VARCHAR(200), flag BIT)

SET @path = 'C:\Backup\'

-- Includes the date in the filename
SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

INSERT INTO [dbo].#tempBackup (name, flag)
SELECT name, 0
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

-- does not overwrite the existing file
BACKUP DATABASE @name TO DISK = @fileName

-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT

DROP TABLE [dbo].#tempBackup

DECLARE @name VARCHAR(50), -- database name
@path VARCHAR(256), -- path for backup files
@fileName VARCHAR(256), -- filename for backup
@fileDate VARCHAR(20), -- used for file name
@backupCount INT

CREATE TABLE [dbo].#tempBackup (intID INT IDENTITY (1, 1), name VARCHAR(200))

SET @path = 'C:\Backup\'

-- Includes the date in the filename
SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

INSERT INTO [dbo].#tempBackup (name)
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

IF ((@backupCount IS NOT NULL) AND (@backupCount > 0))
BEGIN
DECLARE @currentBackup INT

WHILE (@currentBackup <= @backupCount)
BEGIN
SELECT
@name = name,
@fileName = @path + name + '_' + @fileDate + '.BAK' -- Unique FileName
--@fileName = @path + @name + '.BAK' -- Non-Unique Filename
FROM [dbo].#tempBackup
WHERE intID = @currentBackup

-- overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT

END
END

DROP TABLE [dbo].#tempBackup

Thanks Sullivrp for correcting the script.

 Very nice and helpful post.  Thank you so much.

 I have a quick question.  Do I also need to backup the SQL Data and Log files in order to be able to restore the database?   Is backing up the database like shown in this article sufficient to restore a database?

Creating a backup using this script which uses the native backup commands is all you need.

This will backup the data from the data file and the active part of the log file.

So when you need to restore your database you would do something like the following:

RESTORE DATABASE [insertDBnameHere] FROM DISK = 'insertPathAndFilenameHere'

AWESOME !  Thank you so much!

Dear Greg and sullivrp

I am geting and error as access denaid as below:

(1 row(s) affected)

Msg 3201, Level 16, State 1, Line 29

Cannot open backup device 'D:\EMS.BAK'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 29

BACKUP DATABASE is terminating abnormally.

Please help me in this

Thanks Guys, I found the solution which was about adding permissions of SQL Agent user (local) to the backup folder.

Thanks again

Obrigado.

Diretamente do Brasil. O seu script foi muito útil para mim. Obrigado.

Att.

Guilherme

Você é muito bem-vindos.

Just found this thread - very interesting & nice to know that we are doing things properly :)

A quick question - using the cursor script we have found that it appears to skip over databases for no readily apparent reason - does anyone know why this behaviour occurs?

Nick F, right by this reason I use the "no cursor" version of the script.

But I wunder also, why this behaviour occurs.

Thanks for the swift reply :)

We've noticed this behaviour over the last few weeks (we monitor the behaviour of 200+ servers...), and came to the conclusion that it had something to do with the cursor - but can't figure out what! It wouldn't have been so much of an issue if the job would fail when a database is 'skipped'!! <grrr>

Now we're in the process of re-writting the backup jobs (that use this cursor method) and changing over to the temp. table method.

i was wondering, where do you insert a try catch in the backup script above ?

nm figured it out

Thankyou sir..

Gone are the days of needing to manually maintain my scheduled "backup all db" script when databases are added or removed. THANK you all !!!! Great thread.

How to modify this script to also do verification after each database ?

Hello, thank you for the script. It works perfect within single server but when we try to move DB to the other brand new server there are issues with security because the new server doesn't have the same users and logins.

Is there a way to backup logins and relationship between users and logins in order to restore all at the new server?

Thank you so much.

Bob, add this line of code to the script after the BACKUP DATABASE command.  This will do a verify for each backup file that is created.

RESTORE VERIFYONLY FROM DISK = @fileName

Sasha, take a look at this KB article on moving logins between servers:

http://support.microsoft.com/kb/918992

If you follow the above steps in this link the users will be mapped correctly once you restore the databases on the new server.

Hi Greg, thank you very much!

Thanks for this thread.

Is there a way to add a "timeout" (a pause) into the code, so that after each file is backed up, the process pauses for perhaps 5 minutes?

Why? Because I am trying to backup to a SQL "backup device" (server object) that points to a .bak file located in "Amazon Cloud S3 storage".

It seems that the SQL backup process moves too fast, and the remote backup location can't "keep up" (I get "backup file improperly formatted" and "corrupt header" errors).

I know my code is correct, because if I change my SQL "backup device" to point to a local .bak file, everything works great.

Thanks to all of you that contributed!!
Byron

Hi Byron, yes you can use the WAITFOR comand to delay between steps.  See this tip:

http://www.mssqltips.com/sqlservertip/1423/create-delays-in-sql-server-processes-to-mimic-user-input/

Greg, I truly appreciate how you are helping us all!

Byron

Hi

Having used this code for a number of years now, I still have a small problem.

Is is possible to extend the script to delete backups after a certain period of time.

Noel, take a look at these tips to see if you can create a new process or integrate the delete process into this script.

http://www.mssqltips.com/sqlservertip/1324/maintenance-task-to-delete-old-sql-server-backup-files/

http://www.mssqltips.com/sqlservertip/1618/using-the-forfiles-command-to-delete-sql-server-backups/

Hi,

We need to exclude the Snapshot database name from the script as weel as it will fail for Snapshot DB . We can see similar error while executing the script.

Msg 3002, Level 16, State 1, Line 21

Cannot BACKUP or RESTORE a database snapshot.

Msg 3013, Level 16, State 1, Line 21

BACKUP DATABASE is terminating abnormally.

Thanks,

Kumar

Kumar - you can replace this query in the scrip:

SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

with this query which will only backup online databases and also databases that are not snapshots

SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
and state_desc = 'ONLINE'
and source_database_id IS NULL

Hai,

Thanks a lot. It was really helpful to me

Thnak you so much. 

Here is why some databases are skipped if you use a cursor.

Assume you have two jobs a full backup and a log backup. The full backup starts at 3:15AM. The log backup starts at 4:15 and runs hourly until 2:15AM. The databases have grown and now the full backups take over an hour. Since @@FETCH_STATUS is a global variable, both jobs are going to use it.

The log backup isn't going to back up a database if the full back up is still running. Since the log backups are going to finish faster than the full backup, this is going to cause @@FETCH_STATUS to return -1. The full back up job uses the global @@FETCH_STATUS which is now -1 and ends the loop.

This is why some days there will be full backups and other days there won't. On the servers where the full databases finish before the log back up job starts, all of the databases are probably backed up every day.

It is a matter of timing. In fact, it doesn't even have to be the log backup job. Anything that changes @@FETCH_STATUS could be culprit.

Another example of why cursors are evil.

Super!

Hi,

It was a great script in deed.How do i use this script to automate the backup so that it runs on its own.

Thanks !

Greetings,

In my job i have to create backup everyday by using isql commands in command prompt....these are the commands i used

1)d:

2)cd medical_backup* (*folder name)

3)isql -Usa -P*(username  and password)

4)dump database medical to"d:\medical_backup\medical_date*.dmp" (*current date)

5)go

Could u pls tell me the script so that i can schedule that script and it will run automatically everyday.....

thank you

you may find this article helpful:

Create Backup of Database in Sql Server using script files.

Hi

i want compare sql databse with backup via T-SQL?

It is awesome script, it so usefull for me.

This script is great thanks.

how could I schedule this so it does this every Sunday?

thanks

Great script man.... good work... 

Hi Greg,

This script also work in Microsoft SQL Server  2000 - 8.00.2194 (Intel X86)   Apr 20 2006 15:48:56   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 2) or not boz I am upgrading from sql server 2000 Desktop Engine to SQL server 2008R and I have around 120 DB's on that server. I need to take backup all databases using this script. or I have take backup one by one database? I not  able to test this script boz I have only SQl Desktop Engine in Pro. so pelase give me advise about this...

Thanks,

Pinakin

@Pinakin - yes this should work for SQL 2000 as well.

Thanks for your reply Greg :)

Thanks

which one is correct for making script of database backup...guide me because im Newbie..thanks for the help!

God Bless Us.....

@jay - you can just use the script in the tip above.   The scripts in the comments are just variations of the original script.

Also, take a look at this tutorial: http://www.mssqltips.com/sqlservertutorial/1/sql-server-backup-options-and-commands/

this script is working fine if I execute it manually.when I scheduled it,it skips databases.i am using this script to take backup of master and msdb databases.backup of msdb database is missing when script is scheduled.

how can i put all this code into a job to be run automatically everyday ???

thanks you

swolter

@Sebastian - you can create a SQL Agent job and just paste this code into a T-SQL job step.

If you are using SQL Express you can created a Windows Scheduled Task.  Take a look at these tips for more information on creating a backup plan for SQL Server Express:

http://www.mssqltips.com/sqlservertip/1174/scheduling-backups-for-sql-server-2005-express/

http://www.mssqltips.com/sqlservertip/1486/automate-sql-server-express-backups-and-deletion-of-older-backup-files/

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 

How i can backup the Database from server to on a Network PC.

@Imtiaz Hussain - you can make the following change in the script to include a Network Share instead.  Just make sure that SQL Server has permissions to write to this Network Share.

so change this

-- specify database backup directory
SET @path = 'C:\Backup\'

to something like this and put in your ServerName and ShareName

 -- specify database backup directory
SET @path = '\\ServerName\ShareName\'

This is a free tool that can help you too. I developed and is realible.

http://www.sqlserverbooster.com

i did all the steps and were able to open it sql server but that is not executing the above script 

@Tahir - do you get any error messages?

Thanks guys - really helpful..

@ noel

you can retain  database  using  the  command  below

WITH  RETAINDAYS = 14, NOFORMAT, NOINIT,SKIP, REWIND, NOUNLOAD 
RESTORE VERIFYONLY FROM DISK = @fileName

Hi,

Please get me a script for...

How to Find Last Backup Time for All Databases in sql server 2005 ?

@Sahul - take a look at this tip: http://www.mssqltips.com/sqlservertip/1596/sql-server-backup-history-analysis/

Hi,

Very useful script here.

I am in a situation where I'd like to backup all production databases(SharePoint2010 & inhouse app dbs) from on server say, SQL1 to another server SQL2 nightly and then restore them to this other server. 

I was gonna use this to do the backups and then this as a maintenace job to restore(haven't totally figured it out the entire process though)

Running the t-sql provided here gives me errors on just the SharePoint configuration dbs(Search_Service_Application_PropertyStoreDB, SharePoint_AdminContent_... and a couple more). 

The backup location is shared on the SQL2 and all other dbs backups got there no problem. 

So, question is...how to fix this error and if there are other ways of achieving my goal?

Thanks

I forgot to include the exact errors...  Database 'SharePoint_AdminContent_c4d97e78...' does not exist. Make sure that the name is entered correctly.

Msg 3013, Level 16, State 1, Line 31

Thanks.

@Shile - what SQL Server version are you using?  Also, what is the entire name of the database that is failing?  I tried to create some dummy databases based on what you show and it worked fine in SQL 2012.

Hi Greg,

So, I found out why it was failing and you were right on the money about the entire name of the database.

The database name (DECLARE @name VARCHAR(50) -- database name) was set to 50 and most of these SharePoint Configuration databases have names longer than 50 characters(the longest have is 80 characters WebAnalyticsServiceApplication_ReportingDB_356b2d44-71e9-4e3e-979d-29242bc36110)  so, I set it to 150 and all works now. 

And on the second part of my question from yesterday, whats the best way to automate this backup and the restore nightly?

Thanks.

Also, can full or differential backup options be added this script?

Thanks

@shile - if you are using SQL Server Standard or Enterprise you can use SQL Server Agent jobs to do the backup and restore.

If you are using SQL Server Express you can setup a Windows Scheduled Task.  Take a look at these tips:

http://www.mssqltips.com/sqlservertip/1486/automate-sql-server-express-backups-and-deletion-of-older-backup-files/

http://www.mssqltips.com/sqlservertip/2842/how-to-automate-sql-server-restores-for-a-test-server/

http://www.mssqltips.com/sqlservertip/1174/scheduling-backups-for-sql-server-2005-express/

@shile - yes you can do DIFFERENTIAL backups as well.

Just change this line

BACKUP DATABASE @name TO DISK = @fileName

to

BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL

See this for more info: http://www.mssqltips.com/sqlservertutorial/9/sql-server-differential-backups/

Hi Greg,

Thanks again and btw my machine is a sql2008r2 ent.

I look at those links and im not sure thats how i'd like to acheive this setup. I also found 2 extra scripts that seemed to have been written for earlier versions of sql server and was wondering if you could do any modification to those. Im also not sure whether to ask this here or on your restore tip page.

Restore all Databases in a Directory

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CSS_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_CSS_RestoreDir]

GO

/***************************************************************************************/

-- Procedure Name:    sp_CSS_RestoreDir

-- Purpose:           Restore one or many database backups from a single directory.  This script reads all 

--database backups that are found in the @restoreFromDir parameter.

--Any database backup that matches the form %_db_% will be restored to

--the file locations specified in the RestoreTo... parameter(s).  The database

--will be restored to a database name that is based on the database backup

--file name.  For  example Insurance_db_200305212302.BAK will be restored to

--a database named Insurance.  The characters preceeding the '_db_' text determines

--the name.

--

-- Input Parameters: @restoreFromDir - The directory where the database backups are located

--@restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to

--@restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to.  If

--this parameter is not provided then the log files are restored to @restoreToDataDir.

--                   @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,

--                           also allows for secondary data files 'ndf' to to be in a different dir than mdf files

--                   @DBName - restore just this one database - selects the latest bak file

--  

-- Output Parameters: None

--

-- Return Values:     

--

-- Written By:        Chris Gallelli -- 8/22/03

-- Modified By:       

-- Modifications:     Bruce Canaday -- 10/20/2003

--                       Added optional parameters @MatchFileList and @DBName

--                    Bruce Canaday -- 10/24/2003

--                       Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame

--                       This is to handle databases such as ALIS_DB

--                    Bruce Canaday -- 10/28/2003

--                       When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist

--                    Bruce Canaday -- 11/04/2003

--                       Allow spaces in the @restoreFromDir directory name

--

-- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log'

--

-- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y'

--

-- Reviewed By:       

--

/***************************************************************************************/

CREATE proc sp_CSS_RestoreDir 

       @restoreFromDir varchar(255),

@restoreToDataDir varchar(255)= null,

@restoreToLogDir varchar(255) = null,

       @MatchFileList char(1) = 'N',

       @OneDBName varchar(255) = null

as

--If a directory for the Log file is not supplied then use the data directory

If @restoreToLogDir is null

set @restoreToLogDir = @restoreToDataDir

set nocount on

declare @filename         varchar(40),

@cmd              varchar(500), 

@cmd2             varchar(500), 

        @DataName         varchar (255),

@LogName          varchar (255),

        @LogicalName      varchar(255), 

@PhysicalName     varchar(255), 

@Type             varchar(20), 

@FileGroupName    varchar(255), 

@Size             varchar(20), 

@MaxSize          varchar(20),

@restoreToDir     varchar(255),

        @searchName       varchar(255),

@DBName           varchar(255),

        @PhysicalFileName varchar(255) 

create table #dirList (filename varchar(100))

create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )

--Get the list of database backups that are in the restoreFromDir directory

if @OneDBName is null 

   select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'

else

   select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

insert #dirList exec master..xp_cmdshell @cmd  

select * from #dirList where filename like '%_db_%' --order by filename

if @OneDBName is null 

   declare BakFile_csr cursor for 

 select * from #dirList where filename like '%_db_%bak' order by filename

else

   begin  -- single db, don't order by filename, take default latest date /o-d parm in dir command above

     select @searchName = @OneDBName + '_db_%bak'

     declare BakFile_csr cursor for 

 select top 1 * from #dirList where filename like @searchName

   end

open BakFile_csr

fetch BakFile_csr into @filename

while @@fetch_status = 0

   begin

       select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'" 

       insert #filelist exec ( @cmd )

       if @OneDBName is null 

          select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)

       else

  select @dbName = @OneDBName

       select @cmd = "RESTORE DATABASE " + @dbName + 

" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH " 

       PRINT '' 

       PRINT 'RESTORING DATABASE ' + @dbName

       declare DataFileCursor cursor for  

select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize

from #filelist

open DataFileCursor

       fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

       while @@fetch_status = 0

          begin

              if @MatchFileList != 'Y'

                 begin  -- RESTORE with MOVE option 

      select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 )) 

       if @Type = 'L'

       select @restoreToDir = @restoreToLogDir

       else

       select @restoreToDir = @restoreToDataDir

       select @cmd = @cmd + 

           " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', " 

                 end

              else

                 begin  -- Match the file list, attempt to create any missing directory

                     select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )

                     select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir

                     exec master..xp_cmdshell  @cmd2

                 end

              fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

          end  -- DataFileCursor loop

close DataFileCursor

   deallocate DataFileCursor

       select @cmd = @cmd + ' REPLACE'

       --select @cmd 'command'

       EXEC (@CMD)

       truncate table #filelist

       fetch BakFile_csr into @filename

   end -- BakFile_csr loop

close BakFile_csr

deallocate BakFile_csr

drop table #dirList

return

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

Automatically Restores All database .BAK Files

/*************************************************************

Script made by : Lester A. Policarpio

Email Address : lpolicarpio2001@yahoo.com

Date Created : September 03, 2007

--=UPDATES=--

January 17, 2008

- Solved outputed value of the @restoredb variable

- Solved the "invalid length parameter" issue

May 6, 2008

- Removed unused variables

- Add the headeronly command to accept non sql backup format 

 (dbname_db_YYYYMMDDHHMM.BAK)

- Add more comments

May 12, 2008

- Accept Backup Files With Multiple NDFs

May 23, 2008

- Solved the problem when RESTORE HEADERONLY produces more than 1 value

--=LIMITATIONS=--

- This script is tested for backup files ".BAK" only 

-- SCRIPT NEEDS TO BE ALTERED IF BACKUP EXTENSION IS NOT ".BAK"

*************************************************************/

SET NOCOUNT ON

--Drop Tables if it exists in the database

if exists (select name from sysobjects where name = 'migration_lester')

DROP TABLE migration_lester

if exists (select name from sysobjects where name = 'header_lester')

DROP TABLE header_lester

if exists (select name from sysobjects where name = 'cmdshell_lester')

DROP TABLE cmdshell_lester

--Create Tables 

--(cmdshell_lester table for the cmdshell command)

--(migration_lester table for the restore filelistonly command)

--(header_lester table for the restore headeronly command)

CREATE TABLE cmdshell_lester( fentry varchar(1000))

CREATE TABLE migration_lester(LogicalName varchar(1024),

PhysicalName varchar(4000),type char(1),FileGroupName varchar(50),

size real,MaxSize real)

CREATE TABLE header_lester (BackupName varchar(50),

BackupDescription varchar(100),BackupType int,

ExpirationDate nvarchar(50),Compressed int,Position int,

DeviceType int,UserName varchar(30),ServerName varchar(30),

DatabaseName varchar(50),DatabaseVersion int,

DatabaseCreationDate datetime,BackupSize bigint,FirstLsn binary,

LastLsn binary,CheckpointLsn binary,DifferentialBasLsn binary,

BackupStartDate datetime,BackupFinishDate datetime,SortOrder int,

CodePage int,UnicodeLocaleid int,UnicodeComparisonStyle int,

CompatibilityLevel int,SoftwareVendorId int,SoftwareVersionMajor int,

SoftwareVersionMinor int,SoftwareVersionBuild int,

MachineName varchar(50),Flags int,BindingId nvarchar(50),

RecoveryForkId nvarchar(50),Collation nvarchar(50))

--Declare Variables

DECLARE @path varchar(1024),@restore varchar(1024)

DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)

DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)

--Set Values to the variables

SET @newpath_mdf = 'C:\' --new path wherein you will put the mdf

SET @newpath_ldf = 'D:\' --new path wherein you will put the ldf

SET @path = 'D:\' --Path of the Backup File

SET @extension = 'BAK'

SET @pathension = 'dir /OD '+@Path+'*.'+@Extension

--Insert the value of the command shell to the table

INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension

--Delete non backup files data, delete null values

DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%' 

DELETE FROM cmdshell_lester WHERE FEntry is NULL

--Create a cursor to scan all backup files needed to generate the restore script

DECLARE @migrate varchar(1024)

DECLARE migrate CURSOR FOR

select substring(FEntry,40,50) as 'FEntry'from cmdshell_lester 

OPEN migrate

FETCH NEXT FROM migrate INTO @migrate

WHILE (@@FETCH_STATUS = 0)BEGIN

--Added feature to get the dbname of the backup file

SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@Migrate+''''

INSERT INTO header_lester exec (@header)

--Get the names of the mdf and ldf

set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''

INSERT INTO migration_lester EXEC (@restore)

--Update value of the table to add the new path+mdf/ldf names

UPDATE migration_lester SET physicalname = reverse(physicalname)

UPDATE migration_lester SET physicalname = 

substring(physicalname,1,charindex('\',physicalname)-1)

UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'

UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'

--@@@@@@@@@@@@@@@@@@@@

--Set a value to the @restoredb variable to hold the restore database script

IF (select count(*) from migration_lester) = 2

BEGIN

SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)

+' FROM DISK = '+ ''''+@path+@migrate+''''+' WITH MOVE '+''''

+(select logicalname from migration_lester where type = 'D')+''''

+' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')

+''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')

+''''+' TO '+''''+( select physicalname from migration_lester 

WHERE physicalname like '%ldf%')+''''

print (@restoredb) 

END

IF (select count(*) from migration_lester) > 2

BEGIN

SET @restoredb = 

'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+

' FROM DISK = '+''''+@path+@migrate+''''+'WITH MOVE '

DECLARE @multiple varchar(1000),@physical varchar(1000)

DECLARE multiple CURSOR FOR

Select logicalname,physicalname from migration_lester

OPEN multiple

FETCH NEXT FROM multiple INTO @multiple,@physical

WHILE(@@FETCH_STATUS = 0)

BEGIN

SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''

FETCH NEXT FROM multiple INTO @multiple,@physical

END

CLOSE multiple

DEALLOCATE multiple

SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)

print (@restoredb)

END

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

-- Run print @restoredb first to view the databases to be restored

-- When ready, run exec (@restoredb)

-- EXEC (@restoredb)

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

--Clear data inside the tables to give way for the next 

--set of informations to be put in the @restoredb variable 

TRUNCATE TABLE migration_lester

TRUNCATE TABLE header_lester

FETCH NEXT FROM migrate INTO @migrate

END

CLOSE migrate

DEALLOCATE migrate

--@@@@@@@@@@@@@@@@@@@

--Drop Tables 

DROP TABLE migration_lester

DROP TABLE cmdshell_lester

DROP TABLE header_lester