Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL Dump

Posted on 2014-02-27
6
Medium Priority
?
697 Views
Last Modified: 2014-03-04
Hi Experts

Does MS SQL have a dump all databases, similar to the following in MySQL:

mysqldump --opt --all-databases -r c:\mysql_dump.txt -u root -p123

Open in new window


Thank you
0
Comment
Question by:APD_Toronto
  • 4
  • 2
6 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39893496
0
 

Author Comment

by:APD_Toronto
ID: 39893510
But, here you specify the db name.  My goal is for ALL databases (present & future)
0
 

Accepted Solution

by:
APD_Toronto earned 0 total points
ID: 39893532
I'm trying the following, it ran without errors, but the .bak files do not exist

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
 
-- specify database backup directory
SET @path = 'D:\DBs_Backups'  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:APD_Toronto
ID: 39893539
got it, was missing my \ in my path.

Final step... I saved the above as backup.sql, now how can I execute it from a batch file? I think I need to use sqlcmd but how do I specify the script file, user, password?
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39893561
I don't know.  Click on "Request Attention" above to get more people to look at your question.
0
 

Author Closing Comment

by:APD_Toronto
ID: 39902820
figured out o my own.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

927 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