MSSQL Backup all Non System Databases Scripted

Posted by Rose Bush on November 24th, 2015

Microsoft SQL Server backups, MSSQL Backup, process

Note: You may need to go to Tools > Options > Query Results > SQL Server > Results to Text in the Studio Manager and change the “Maximum number of characters displayed in each column to 1024” (default is 256). Also make sure you are outputting the results to text. If you do not do this prior, you run into the chance of either set of commands not outputting the full commands needed for the next step.
Also, you may need to open a New Query as the change ‘seems’ to only affect newly opened queries. The second pertinent setting, Open a new query window and ensure it has the focus, then go to Query > 249521. Here you can see the setting is located in 249521 > 249521. Change the Maximum number of characters displayed in each column the fields maximum of 8192.

The following command will generate a SQL script to back up all non system SQL databases. Be sure to replace the restore path. Make sure you set the results to text output.

use master
GO
select '
BACKUP DATABASE [' + [name] + ']
TO  DISK = N''C:\RESTORE\PATH\' + [name] + '.bak'' 
WITH NOFORMAT, NOINIT,  NAME = N''' + [name] + '-Full Database Backup''
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO' from master..sysdatabases where dbid > 4

1

2

3

4

5

6

7

8

use master

GO

select '

BACKUP DATABASE [' + [name] + ']

TO DISK = N''C:\RESTORE\PATH\' + [name] + '.bak''

WITH NOFORMAT, NOINIT, NAME = N''' + [name] + '-Full Database Backup''

, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO' from master..sysdatabases where dbid > 4

Once the above query has been run, copy the output and run it as a new query on the source server (I migrate a lot, hence the terminology used).

Filed under: Code or Hosting | Comments Off

Find the Default File Paths for a Given SQL Instance

Posted by Rose Bush on May 4th, 2015

Method 1 of 1?

-- Check if temp database exists
-- Tempdatabase is used for determining the default database path
--if the zztempDefaultPath db exists drop

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')

BEGIN
DROP DATABASE zzTempDBForDefaultPath
END;

-- Create temp database. Because no options are given, the default data and --- log path locations are used

CREATE DATABASE zzTempDBForDefaultPath;

--Declare variables for creating temp database

DECLARE @Default_Data_Path VARCHAR(512),
@Default_Log_Path VARCHAR(512);

--Get the default data path

SELECT @Default_Data_Path =
( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.[databases] d
ON mf.[database_id] = d.[database_id]
WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0);

--Get the default Log path

SELECT @Default_Log_Path =
( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.[databases] d
ON mf.[database_id] = d.[database_id]
WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1);

--Clean up. Drop de temp database

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')
BEGIN
DROP DATABASE zzTempDBForDefaultPath
END;

PRINT @Default_Data_Path;
PRINT @Default_Log_Path;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

-- Check if temp database exists

-- Tempdatabase is used for determining the default database path

--if the zztempDefaultPath db exists drop

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')

BEGIN

DROP DATABASE zzTempDBForDefaultPath

END;

-- Create temp database. Because no options are given, the default data and --- log path locations are used

CREATE DATABASE zzTempDBForDefaultPath;

--Declare variables for creating temp database

DECLARE @Default_Data_Path VARCHAR(512),

@Default_Log_Path VARCHAR(512);

--Get the default data path

SELECT @Default_Data_Path =

( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)

FROM sys.master_files mf

INNER JOIN sys.[databases] d

ON mf.[database_id] = d.[database_id]

WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0);

--Get the default Log path

SELECT @Default_Log_Path =

( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)

FROM sys.master_files mf

INNER JOIN sys.[databases] d

ON mf.[database_id] = d.[database_id]

WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1);

--Clean up. Drop de temp database

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')

BEGIN

DROP DATABASE zzTempDBForDefaultPath

END;

PRINT @Default_Data_Path;

PRINT @Default_Log_Path;

Filed under: Code or Hosting | Comment (0)

Create the alias with cliconfg.exe

Posted by Rose Bush on May 4th, 2015

Option 2: Create the alias with cliconfg.exe
Log into the Application and/or front-end Web servers
Got to Start > cliconfg.exe (Note: it’s c-l-i-c-o-n-f-g-dot-e-x-e)

Then click on “Alias”:

If you already have an alias set up, it will show up there. Otherwise…
Click “Add”

Specify the port number and give it a name. That’s it!
Test your SQL Alias:
To test your SQL alias, create a Microsoft Data Link (udl) file on your desktop:

From your desktop, right click on the desktop and choose New > Text file
A new file named New Text Document.txt appears in the directory.
Rename this file to “TestDBConnection.udl”, removing all spaces. Be sure to change its file extension to .udl.
Open the file and, under Connection, type either the SQL server name or, if you are testing an SQL alias, the alias name.
Under 2, chose “Windows Authenticated security”
Click “refresh”
If the connection was successful, list of databases should appear in the dropdown menu under #3.
I have a separate blog post that covers the same steps for creating a udl file here. http://smallcitydesign.com/how-to-test-a-database-connection-from-an-application-server/

Filed under: Code or Hosting | Comment (0)

Exporting/Importing (Migrating) All Non System MSSQL Databases

Posted by Rose Bush on May 4th, 2015

I want all DB’s moved, from SQL Server/Instance A to SQL Server/Instance B. The easiest way to do this of course, is scripted :D. Oh how I love the idea of automation.

Generate export script:

use master
GO
select '
BACKUP DATABASE [' + [name] + ']
TO  DISK = N''C:\RESTORE\PATH\' + [name] + '.bak'' 
WITH NOFORMAT, NOINIT,  NAME = N''' + [name] + '-Full Database Backup''
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO' from master..sysdatabases where dbid > 4

1

2

3

4

5

6

7

8

use master

GO

select '

BACKUP DATABASE [' + [name] + ']

TO DISK = N''C:\RESTORE\PATH\' + [name] + '.bak''

WITH NOFORMAT, NOINIT, NAME = N''' + [name] + '-Full Database Backup''

, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO' from master..sysdatabases where dbid > 4

Use the output to backup the databases.

Then run the following command to get the import commands you would run on the destination instance:

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
declare @pre varchar(500)

set @cmd1 = 'insert into #filesinfo (db, dn, dfn )
	select ''?'', [name], [filename] from [?]..sysfiles where groupid=1'

set @cmd2 = 'update #filesinfo set ln = [name], lfn =[filename]
	from [?]..sysfiles sf
	where sf.groupid=0 and #filesinfo.db=''?'''

set @cmd3 = 'update #filesinfo set nf = (select count(*) from [?]..sysfiles)
	where #filesinfo.db=''?'''

CREATE TABLE #filesinfo (db sysname, dn sysname, ln sysname null, dfn nvarchar(500), lfn nvarchar(500) null, nf int, sa sysname null)

EXEC sp_MSforeachdb @command1=@cmd1,@command2=@cmd2, @command3=@cmd3

set @cmd1 = 'update #filesinfo set sa = (select sl.[name] from master..syslogins sl inner join [?]..sysusers su on sl.sid=su.sid where su.name=''dbo'')
	where #filesinfo.db=''?'''

EXEC sp_MSforeachdb @command1=@cmd1

--EXEC sp_MSforeachdb 'insert into #filesinfo (db) select ''?''' 

DELETE FROM #filesinfo 
WHERE db in ('model','master','tempdb','pubs','Northwind','msdb') 

--SELECT 'backup database ' + db + ' to disk=''' + db + '.bak''' FROM #filesinfo

declare @restorepath nvarchar(1024)
declare @datapath nvarchar(1024)
declare @logpath nvarchar(1024)
set @datapath  = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
set @logpath  = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
set @restorepath  = N'C:\EdgeWeb\migrate\'

select '
RESTORE DATABASE [' + db + '] 
FROM  DISK = ''' + @restorepath + db + '.bak''
WITH  FILE = 1
,  MOVE N''' + dn + ''' TO N''' + @datapath + db + '.mdf''
,  MOVE N''' + ln + ''' TO N''' + @logpath + db + '_log.ldf''
,  NOUNLOAD,  REPLACE,  STATS = 10
GO

'
from #filesinfo

drop table #filesinfo

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

declare @cmd1 varchar(500)

declare @cmd2 varchar(500)

declare @cmd3 varchar(500)

declare @pre varchar(500)

set @cmd1 = 'insert into #filesinfo (db, dn, dfn )

select ''?'', [name], [filename] from [?]..sysfiles where groupid=1'

set @cmd2 = 'update #filesinfo set ln = [name], lfn =[filename]

from [?]..sysfiles sf

where sf.groupid=0 and #filesinfo.db=''?'''

set @cmd3 = 'update #filesinfo set nf = (select count(*) from [?]..sysfiles)

where #filesinfo.db=''?'''

CREATE TABLE #filesinfo (db sysname, dn sysname, ln sysname null, dfn nvarchar(500), lfn nvarchar(500) null, nf int, sa sysname null)

EXEC sp_MSforeachdb @command1=@cmd1,@command2=@cmd2, @command3=@cmd3

set @cmd1 = 'update #filesinfo set sa = (select sl.[name] from master..syslogins sl inner join [?]..sysusers su on sl.sid=su.sid where su.name=''dbo'')

where #filesinfo.db=''?'''

EXEC sp_MSforeachdb @command1=@cmd1

--EXEC sp_MSforeachdb 'insert into #filesinfo (db) select ''?'''

DELETE FROM #filesinfo

WHERE db in ('model','master','tempdb','pubs','Northwind','msdb')

--SELECT 'backup database ' + db + ' to disk=''' + db + '.bak''' FROM #filesinfo

declare @restorepath nvarchar(1024)

declare @datapath nvarchar(1024)

declare @logpath nvarchar(1024)

set @datapath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

set @logpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

set @restorepath = N'C:\EdgeWeb\migrate\'

select '

RESTORE DATABASE [' + db + ']

FROM DISK = ''' + @restorepath + db + '.bak''

WITH FILE = 1

, MOVE N''' + dn + ''' TO N''' + @datapath + db + '.mdf''

, MOVE N''' + ln + ''' TO N''' + @logpath + db + '_log.ldf''

, NOUNLOAD, REPLACE, STATS = 10

GO

'

from #filesinfo

drop table #filesinfo

Filed under: Code or Hosting | Comment (0)

SQL Server FAQ

Posted by Rose Bush on April 18th, 2013

I wanted a place to throw a lot of the high level SQL FAQ and answers for issues I come to.

v=sql.70 7
v=sql.80 2000
v=sql.90 2005
v=sql.100 2008
v=sql.105 2008R2
v=sql.110 2012
v=sql.120 2014
v=sql.130 2016

This site lists build versions, this is really useful for matching version to version by running the updates needed manually:

Microsoft SQL Server 2012, 2008R2, 2008, 2005, 2000 and 7.0 Builds

SQL Server 2012 msdn: Editions and Components of SQL Server 2012

SQL Server 2008 R2 msdn: Editions and Components of SQL Server 2008 R2

Server Editions:
*Datacenter
*Enterprise
*Standard

Specialized Editions:
*SQL Server Developer
*SQL Server Workgroup
*SQL Server Web
*SQL Server Express (SQL Server Express with Tools, SQL Server Express with Advanced Services)
*Compact 3.5 SP1 (x86) Compact 3.1 (x86)

SQL Server 2008 msdn: Editions and Components of SQL Server 2008

SQL Server 2005 msdn: Editions and Components of SQL Server 2005

Anything else was too old for my purposes.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MSSQL Data links:

http://en.wikipedia.org/wiki/Microsoft_SQL_Server

Deprecated Database Engine Features in SQL Server 2012
http://msdn.microsoft.com/en-us/library/ms143729.aspx

Build version list, you have a version number and want to know what updates are in there:
http://sqlserverbuilds.blogspot.com/

Features Supported by the Editionhere here here here hereNOPENOPENOPE

	SQL Server 2016	SQL Server 2014	SQL Server 2012	SQL Server 2008 R2	SQL Server 2008		SQL Server 2000	SQL Server 7
Hardware and Software Requirements	here	here	here	here	here		NOPE	NOPE
Editions and Components	here	here	here	here	here		NOPE	NOPE

Hardware and Software Requirements for Installing SQL Server 2012
http://msdn.microsoft.com/en-us/library/ms143506.aspx

Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx

Features Supported by the Editions of SQL Server 2012 (FYI SQL web does support SQL profiler some how?)
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx

Features Supported by the Editions of SQL Server 2014
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

Features Supported by the Editions of SQL Server 2016
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.130).aspx

Features Supported by the Editions of SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms143761(v=sql.90).aspx

Prevent Truncation for Dynamically Generated T-SQL Results in SSMS
https://www.edwinbush.com/prevent-truncation-for-dynamically-generated-t-sql-results-in-ssms/

Migrating SQL Server Users
https://www.edwinbush.com/migrating-sql-server-users/

Filed under: Code or Hosting | Comment (0)

Easily test connectivity to MSSQL Server

Posted by Rose Bush on April 9th, 2013

I have run into a number of cases where I want to test a connection to a SQL (MSSQL) Server without having to resort to code. To do so is rather straight forward:

Create a new file, a simple txt file is fine, and rename the document to “TestDBConnection.udl” sans quotes and spaces.

Be sure to change its file extension to .udl as many windows systems will not show you the extension. If you are in Explorer in Windows 7 I can tell you the commands to get to the setting are Alt, T, O, CTRL + Tab. Here you can see the ‘Hide extensions for known file types’.
Open the file:

Under Connection, enter the SQL server hostname or IP

If testing a SQL alias:
Enter information to log on to the server”
*Choose Windows Authenticated security
Click the Refresh button.
If a successful connection was made, a list of databases will be accessible under section 3.

Otherwise, test as you would guess, it is fairly clear from here.

Filed under: Code or Hosting | Comment (0)

Adding the Administrators group within SQL Server

Posted by Rose Bush on April 1st, 2013

If you want to add the group Administrators to SQL so that any user of the Administrators Group can use SQL server, the below code, once executed, will perform this for you:

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
GO

1

2

3

4

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'

GO

Filed under: Code or Hosting | Comment (0)

Determining Microsoft SQL Server Version

Posted by Rose Bush on March 26th, 2013

Open up SQL Server Management Studio and connect to the Database Engine (connection not pictured)

Right click on the instance and choose Properties from the menu.

Here you can see the Product and the Version, as both are important details. If you are noting this for future reference I recommend noting the entire line of each. Keep in mind the Version does change as upgrades to SQL server are installed through KB’s and Windows Updates.

Filed under: Code or Hosting | Comment (0)

Calculating Disk Space in Use by All Databases within a Given MSSQL Instance

Posted by Rose Bush on March 4th, 2013

If you are trying to calculate total disk space in use by all databases within a given MSSQL instance, you can use the following script to determine this space.

SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace FROM master.sys.master_files

1	SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace FROM master.sys.master_files

It has been said that “Sys.Master_files is a server-wide view that lists every file in every DB. It’s available from SQL Server 2005 onward.”

Filed under: Code or Hosting | Comment (0)

Update the Schema on a Stored Procedure

Posted by Rose Bush on February 26th, 2013

To update the schema of a single stored procedure, once on the database in question, you would need to run the following line:

ALTER SCHEMA destinationSchema TRANSFER sourceSchema.storedProcedure;

1	ALTER SCHEMA destinationSchema TRANSFER sourceSchema.storedProcedure;

Filed under: Code or Hosting | Comment (0)

Edventures in Normalcy

MSSQL Backup all Non System Databases Scripted

Find the Default File Paths for a Given SQL Instance

Create the alias with cliconfg.exe

Exporting/Importing (Migrating) All Non System MSSQL Databases

SQL Server FAQ

Easily test connectivity to MSSQL Server

Adding the Administrators group within SQL Server

Determining Microsoft SQL Server Version

Calculating Disk Space in Use by All Databases within a Given MSSQL Instance

Update the Schema on a Stored Procedure

Archives

Search:

Archives