Solved

MSSQL Dump

Posted on 2014-02-27
6
586 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 83

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 83

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now