CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-server-2012
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10570

How to backup all databases on SQL Server 2008

I have been working on SQL Server 2008 R2 for 4 years and it's time to format my laptop.

I just use the default instance, which I can access using the . as server name, and then my username and password for user authentication.

Now I want to format my laptop, and it is almost impossible to backup manually all the database.

I found in the following path

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL

all the databases that I have, for each database I found:

  1. databasename_log.ldf
  2. databasename.mdf

I copied these files to an external hard drive.

My question:

Are those files enough to import the database after formatting? Will they work if I installed (after formatting) SQL Server 2012 not 2008 R2?

Upvotes: 2

Views: 11704

Answers (3)

Filip Holub
Filip Holub

Reputation: 71

You can use SELECT Statement or CURSOR like this:

DECLARE @PathForBackUp VARCHAR(255)
SET @PathForBackUp = 'F:\Backup\User DB\'

SELECT 'BACKUP DATABASE [' + name + '] TO  DISK = N''' + @PathForBackUp + '' + name + '.bak''
WITH NOFORMAT, NOINIT,  NAME = N''' + name + '_FullBackUp'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 5'
FROM sys.databases
WHERE database_id > 4

OR

DECLARE @DBName VARCHAR(255)  
DECLARE @PathForBackUp VARCHAR(255) 
DECLARE @FileName VARCHAR(255)  
DECLARE @DateFile VARCHAR(255)
DECLARE @SQL NVARCHAR(2048) 
SET @PathForBackUp = 'F:\Backup\User DB\'  
SET @DateFile = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120) ,' ','T'), ':','') 

DECLARE BACKUPING CURSOR FOR   
SELECT name  
FROM master.dbo.sysdatabases WHERE dbid > 4 

OPEN BACKUPING    
FETCH NEXT FROM BACKUPING INTO @DBName    
WHILE @@FETCH_STATUS = 0    

BEGIN    
        SET @FileName = @PathForBackUp + @DBName + '_' + @DateFile + '.BAK'  
    SET @SQL = 'BACKUP DATABASE '+@DBName+ ' TO DISK = '''+@FileName+''' WITH COMPRESSION ' 
    PRINT @SQL 
    EXECUTE sp_executesql @sql   
    FETCH NEXT FROM BACKUPING INTO @DBName  

END    

CLOSE BACKUPING    
DEALLOCATE BACKUPING

If you want to more about these solution I wrote post about that here: http://www.pigeonsql.com/single-post/2016/12/20/Backup-All-Users-databases-via-Select-and-Cursor

Upvotes: 1

Shanky
Shanky

Reputation: 626

Are those files enough to import the database after formatting? Will they work if I installed (after formatting) SQL Server 2012 not 2008 R2?

You are taking file level backup of database mdf and ldf file this is bit different from T-SQL backup. As I can read you have already formatted your machine and you have not taken backup so in that case please copy the mdf and ldf file on the local drive and then attach those files to create the database.

Please note before attaching make sure you have installed Service pack(SP), if it was installed on SQL Server 2008 r2 before formatting. If not please install SP after attaching database.

To attach database you can use both TSQL and GUI. Below link would help you in attaching the data files. Before opening SSMS please right click on SSMS and select run as administrator to avoid any access denied message.

Attach a database using SSMS

Sample script for attach

CREATE DATABASE DatabaseName 
    ON (FILENAME = 'FilePath\FileName.mdf'), -- Main Data File .mdf
    (FILENAME = 'FilePath\LogFileName.ldf'), -- Log file .ldf
     (FILENAME = 'FilePath\SecondaryDataFile.ndf)  -- Optional - any secondary data files
    FOR ATTACH 
GO

Upvotes: 0

Sathish
Sathish

Reputation: 2056

I found the SQL from this article useful in taking backups of all databases on a server.

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 = 'C:\Backup\'  


-- 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

Upvotes: 5

Related Questions