Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!
Let us see how a DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.
Here is the basic script for Backup and Restore
Note: name of my database server is touching and I am connecting it with windows authentication.
Backup
BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'
Restore
RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'
Here is the basic script for Backup and Restore using SQLCMD
Backup
C:\Users\pinaldave>SQLCMD -E -S touch -Q "BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"
Restore
C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"
Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.
Watch a 60 second video on this subject
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
65 Comments. Leave new
Hi Pinal,
I need to be able to backup a Database but change it’s name for example if it is called Nuneaton and i want to change it to the software name ie Topconnect.
Then i need to restore it to the name Topconnect. I need to be able to do this without having to connect to SQL Management studio express and would like to do it all using scripts for any version of SQL.
Any ideas that you may be able to suggest please?
I am not able to restore the database. It gives error as .mdf and .ldf file cannot be overwritten.
I need to restore the database programatically.
Any idea, how this error can be resolved please?
Hi all,
I had a similar question. I want to backup my databases to another system/share.
How can I accomplish this.on SQL 2000?
P.S. Don’t Ask!
Dear Mr. pinal,
ur article is wrong and plz respond to Ajay Reddy | February 9, 2013 at 8:17 am post.
-E -S -Q what does these options indicates in back up command…
For those of you who are struggling with the Named Pipes Error issue, please try the following (I ran into the same issue but realized my mistake)
(1) First of all please ensure that the Names Pipes are enabled under “Client protocols” as well “Protocols for SQLEXPRESS” in “SQL Server Configuration Manager” [I believe some of you already enabled them – if yes, then move to step 2 below]
(2) Make sure that you are not using “localhost” or IP address for servername. Please use the fully qualified server name the way you use in SQL Server Management Studio to connect to the DB server for e.g. [ComputerName\SQLExpress.
My Named Pipes issues got fixed by doing the above 2 steps. Hope it works for you all too. Good Luck.
Thanks,
Jeet
I have issues where restoring the same .bak via the GUI vs. command line restores different versions of the databse, as crazy as that sounds. Anyone else have that issue?
Hi All,
Is there any way, we can get to know whether the queries are executed properly or not? (e.g through some return code)
I got a job by saying this answer in my last interview. thanks for awesome help.
I got more idea about oracle from Besant Technologies. If anyone wants to get oracleTraining in Chennai visit Besant Technologies.
Ajay Reddy, maybe you’re using “touch” as your instance name, if so, you’ll need to change to your correct instance name (in example, “localhost”)
Hi,
Last week i attended IBM interview. one interviewer asked one critical question but i cant and guess the answer. Please help me.
question : last night your colleague took the sql Database backup but unfortunately at the time your default back path drive is full . so he took the backup some different place. next day morning your friend is left from office . he gone to his native his mobile is also switch off. morning you came to office as usual you stars your activity suddenly your server database has been corrupted. so you HAVE to restore your full backup that time you checked in default path but backup is not there . two option is there
1 ) is you want to ask your friend where you took the backup, ask the path
2) another one is some command base we can find
first option is already fail because friend mobile is switched off
what is second option ? how you can find.
please help me how to find the bACKUP .
There are some system tables available to capture the backup activity. You can find those list tables in msdb database. Table names are starting like msdb.dbo.backup… Also you can check the below script to get the backup file details
SELECT S.database_name, S.backup_start_date, S.backup_finish_date
, F.logical_name, F.physical_drive, F.physical_name
FROM msdb.dbo.backupset S
INNER JOIN msdb.dbo.backupmediaset MS
ON S.media_set_id = MS.media_set_id
INNER JOIN msdb.dbo.backupfile F
ON F.backup_set_id = S.backup_set_id
i use this it gives an error —-
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘E:\adw.bak’. Operating system error 5(Access is denied.)
Hi,
I’ve been trying to restore a database but it seems the log is far too large and the server is running out of memory! Is there any way to just extract the data file from the .bak or to tell RESTORE to ignore the log file?
Thanks
RESSTORE OPTION NOT WORKING FOR ME
Hello Sir,
How can we restore it at specified location, my c: drive is about to full and i restore it in another drive, how can i do that using query?????????????????????
Restore command has WITH MOVE option. That can be used to restore by moving MDF/LDF to new location.
how to backup of database from other server (ex:A server) and restore to other server (ex: B server) using scripts Note:this entire operation should perform from other server(ex:C server)
In SQLCMD you can provide server name where you want to run restore command. Make sure that backup file is accessible from server B.
when i am using the above command it gives me the error can’t open backup device. How can i short out this problem . Any help will be appreciated. Please reply via email its urgent
Sorry for delay. What is the operating system error? 5 generally means access denied.
Here’s script/stored procedure i wrote for backing up all databases in the current instance. Actually I wrote it for use in the express edition, because no database maintanance is available.
It will create one backup statement for every database except tempdb and execute it.
The proc will check, if the backup path is available. It is designed to create a subfolder for every day of the week in which a backup is executed and overwrite it one week later.
What do you think of it?
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BACKUP_ALL_DB] Script Date: 05/29/2015 11:49:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— ===========================================================================================
— Author: NSc
— Create date: 28. – 29.05.2015
— Description: dynamic backcup script for current instance, backup all DB except TEMPDB
— Backuppath MUST be available, missing folders for days of week will be created
— ===========================================================================================
ALTER PROCEDURE [dbo].[BACKUP_ALL_DB]
— Add the parameters for the stored procedure here
@BackupPath nvarchar(255) = ”, –if paramater is not provided, proc will read it from the registry
@NoOfBackupDays int = 5, — max. 7 days
@FirstBackupDay int = 1 — first day + No of days 7
begin
set @NoOfBackupDays = 7
end
— sum first day + no of days 8
begin
set @FirstBackupDay = 8 – @NoOfBackupDays
end
— check if backup path is provided
if (@BackupPath = ” or @BackupPath is NULL)
begin
— build regkey for backup path of current Instance
declare @RegKey nvarchar (100)
set @RegKey = ( select ‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL’ + substring(cast(serverproperty(‘ProductVersion’) as nvarchar (255)),1,2) + ‘.’ + @@SERVICENAME +’\MSSQLServer’)
— read registry value BackupDirectory
DECLARE @returnValue NVARCHAR(255)
EXEC master.dbo.xp_regread
@rootkey = N’HKEY_LOCAL_MACHINE’,
@key = @RegKey,
@value_name = N’BackupDirectory’,
@value = @returnValue output
SET @BackupPath = ( @returnValue + ”)
end
— Parameters are correct now
—————————————————————————————————————————————————–
— check directorys and create if neccessary
— create table for directory information
Create Table #Dir (
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ENTRY] nvarchar (255) null
,[File Exists] int NULL
,[File is a Directory] int null
,[Parent Directory Exists] int null
)
— is backup path available in OS?
declare @DirFlag int = 0
insert into #Dir ([File Exists], [File is a Directory], [Parent Directory Exists]) EXEC master..xp_fileexist @BackupPath
set @DirFlag = (select TOP 1 [FILE is a Directory] from #Dir)
— if BackupPfad is not avialable in OS => End
if @DirFlag = 0 goto finish
set @DirFlag = 0
— check for subfolders for days of week and create if missing
— make temp table #Dir empty and reset identity column
delete #Dir
dbcc checkident (#Dir, reseed,1)
— fill temp table with subdirectory info of backup path
insert into #Dir (ENTRY) EXEC master..xp_subdirs @BackupPath
— loop for subfolder check: all all necessary subfolders for days of week available ?
declare @i int = @FirstBackupDay
declare @DirectoryCommand nvarchar (255)
while @i < (@NoOfBackupDays + @FirstBackupDay)
Begin
— is subfolder available?
set @DirFlag = (select COUNT(*) from #Dir where [ENTRY]= CAST (@i as nvarchar (2)))
— if not, create
if @DirFlag = 0
begin
set @DirectoryCommand = (select 'md ' + char(34)+ @BackupPath + CAST (@i as nvarchar (2)) + CHAR (34))
— select @DirectoryCommand
exec master..xp_cmdshell @DirectoryCommand
end
set @i = @i + 1
end
— subfolders are available now
———————————————————————————————————————
— execute backup
— empty destination folder
declare @DelCommand nvarchar (255)
set @DelCommand= (select 'del "' + @BackupPath + CAST(datepart(dw, sysdatetime()) as nvarchar(1)) + '*.*" /Q /F')
— select @DelCommand
EXEC master..xp_cmdshell @DelCommand
–Temp table for backup statements
create table #SQLSTATEMENTS(
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SQLTEXT] [nvarchar](1000) NULL
)
— fill temp table with statemants
insert into #SQLSTATEMENTS (SQLTEXT) select 'Backup Database ' + char(91) + name + char(93) + ' to Disk=' + char(39) + @BackupPath + cast(DATEPART(DW, sysdatetime()) as varchar (1)) + '' + replace(name,'','') + '.bak' + char(39) from sys.databases where name ‘tempdb’
— execute staemants
set @i = 1
declare @sql nvarchar (1000)
while @i <= (select MAX(id) from #SQLSTATEMENTS)
begin
set @sql= (select sqltext from #SQLSTATEMENTS where #SQLSTATEMENTS.ID=@i)
exec sp_executesql @sql
set @i=@i+1
end
return @i — number of backup files
— Backup executed
—————————————————————————————————————————————-
— error handling: backup path provided ist not available: return value -1
finish:
return -1
END
Actually there seems to be a problem with some characters missing in above script after putting it into the reply window. How do need to handle this?
Nils
That’s WordPress issue.
Hello Pinal,I have a big SQL file (almost 1GB) from a database, SSMS does not open it due to storage issues. I want to use SQLCMD to execute it on another database,how can I go about it?I used the -d option but it still says databse does not exist.
What is the content of the file? Have you tried on 64 bit machine?